github.com/dolthub/go-mysql-server@v0.18.0/sql/types/decimal_test.go (about) 1 // Copyright 2022 Dolthub, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package types 16 17 import ( 18 "fmt" 19 "math/big" 20 "reflect" 21 "strings" 22 "testing" 23 "time" 24 25 "github.com/shopspring/decimal" 26 "github.com/stretchr/testify/assert" 27 "github.com/stretchr/testify/require" 28 ) 29 30 func TestDecimalAccuracy(t *testing.T) { 31 t.Skip("This runs 821471 tests, which take quite a while. Re-run this if the max precision is ever updated.") 32 precision := 65 33 34 tests := []struct { 35 scale int 36 intervals []string 37 }{ 38 {1, []string{"1"}}, 39 {2, []string{"1"}}, 40 {3, []string{"1"}}, 41 {4, []string{"1", "2"}}, 42 {5, []string{"1", "7", "19"}}, 43 {6, []string{"1", "17", "173"}}, 44 {7, []string{"1", "13", "1381"}}, 45 {8, []string{"1", "127", "15139"}}, 46 {9, []string{"1", "167", "11311", "157733"}}, 47 {10, []string{"1", "193", "12119", "1299827"}}, 48 {11, []string{"1", "1409", "13597", "11566817"}}, 49 {12, []string{"1", "1462", "162736", "19973059", "153698913"}}, 50 {13, []string{"1", "17173", "164916", "12810490", "1106465924"}}, 51 {14, []string{"1", "14145", "1929683", "11237352", "12259001771"}}, 52 {15, []string{"1", "19702", "1826075", "197350780", "117062654737"}}, 53 {16, []string{"1", "154259", "1722308", "192079755", "1568355872155"}}, 54 {17, []string{"1", "199621", "17380400", "189789317", "18535814105416"}}, 55 {18, []string{"1", "164284", "19555364", "1343158899", "191285386028951"}}, 56 {19, []string{"1", "1370167", "14327353", "1613296706", "1786126145971438"}}, 57 {20, []string{"1", "1682382", "156896829", "1502199604", "15400467202762943"}}, 58 {21, []string{"1", "1908105", "132910114", "17668300548", "145914194398307528"}}, 59 {22, []string{"1", "11192652", "181987462", "13471431866", "1112655573846229769"}}, 60 {23, []string{"1", "19628451", "1498686974", "13119001111", "17583200755082903973"}}, 61 {24, []string{"1", "14855266", "1844358042", "140667369937", "138362583526008386641"}}, 62 {25, []string{"1", "132605238", "1459826257", "138157739511", "1456272936346618537992"}}, 63 {26, []string{"1", "178623779", "19310677332", "124692319379", "15924740334465525606269"}}, 64 {27, []string{"1", "136953077", "13506952725", "1383331590521", "137480986566749829385216"}}, 65 {28, []string{"1", "1838754847", "16879518108", "1840612305937", "1389868035366355336138689"}}, 66 {29, []string{"1", "1760427312", "169649694515", "1810557411178", "12907494895459213754558234"}}, 67 {30, []string{"1", "1823936104", "131352779146", "17050328377892", "146384189585475736836539491"}}, 68 } 69 70 for _, test := range tests { 71 decimalType := MustCreateDecimalType(uint8(precision), uint8(test.scale)) 72 decimalInt := big.NewInt(0) 73 bigIntervals := make([]*big.Int, len(test.intervals)) 74 for i, interval := range test.intervals { 75 bigInterval := new(big.Int) 76 _ = bigInterval.UnmarshalText([]byte(interval)) 77 bigIntervals[i] = bigInterval 78 } 79 intervalIndex := 0 80 baseStr := strings.Repeat("9", precision-test.scale) + "." 81 upperBound := new(big.Int) 82 _ = upperBound.UnmarshalText([]byte("1" + strings.Repeat("0", test.scale))) 83 84 for decimalInt.Cmp(upperBound) == -1 { 85 decimalStr := decimalInt.Text(10) 86 fullDecimalStr := strings.Repeat("0", test.scale-len(decimalStr)) + decimalStr 87 fullStr := baseStr + fullDecimalStr 88 89 t.Run(fmt.Sprintf("Scale:%v DecVal:%v", test.scale, fullDecimalStr), func(t *testing.T) { 90 res, _, err := decimalType.Convert(fullStr) 91 require.NoError(t, err) 92 require.Equal(t, fullStr, res.(decimal.Decimal).StringFixed(int32(decimalType.Scale()))) 93 }) 94 95 decimalInt.Add(decimalInt, bigIntervals[intervalIndex]) 96 intervalIndex = (intervalIndex + 1) % len(bigIntervals) 97 } 98 } 99 } 100 101 func TestDecimalCompare(t *testing.T) { 102 tests := []struct { 103 precision uint8 104 scale uint8 105 val1 interface{} 106 val2 interface{} 107 expectedCmp int 108 }{ 109 {1, 0, nil, 0, 1}, 110 {1, 0, 0, nil, -1}, 111 {1, 0, nil, nil, 0}, 112 {1, 0, "-3.2", 2, -1}, 113 {1, 1, ".738193", .6948274, 1}, 114 {5, 0, 0, 1, -1}, 115 {5, 0, 0, "1", -1}, 116 {5, 0, "0.23e1", 3, -1}, 117 {5, 0, "46572e-2", big.NewInt(466), -1}, 118 {20, 10, "48204.23457e4", 93828432, 1}, 119 {20, 10, "-.0000000001", 0, -1}, 120 {20, 10, "-.00000000001", 0, -1}, 121 {65, 0, "99999999999999999999999999999999999999999999999999999999999999999", 122 "99999999999999999999999999999999999999999999999999999999999999998", 1}, 123 {65, 30, "99999999999999999999999999999999999.999999999999999999999999999998", 124 "99999999999999999999999999999999999.999999999999999999999999999999", -1}, 125 } 126 127 for _, test := range tests { 128 t.Run(fmt.Sprintf("%v %v", test.val1, test.val2), func(t *testing.T) { 129 cmp, err := MustCreateDecimalType(test.precision, test.scale).Compare(test.val1, test.val2) 130 require.NoError(t, err) 131 assert.Equal(t, test.expectedCmp, cmp) 132 }) 133 } 134 } 135 136 func TestCreateNonColumnDecimal(t *testing.T) { 137 tests := []struct { 138 precision uint8 139 scale uint8 140 expectedType DecimalType_ 141 expectedErr bool 142 }{ 143 {0, 0, DecimalType_{decimal.New(1, 10), false, 10, 0}, false}, 144 {0, 1, DecimalType_{}, true}, 145 {0, 5, DecimalType_{}, true}, 146 {0, 10, DecimalType_{}, true}, 147 {0, 30, DecimalType_{}, true}, 148 {0, 65, DecimalType_{}, true}, 149 {0, 66, DecimalType_{}, true}, 150 {1, 0, DecimalType_{decimal.New(1, 1), false, 1, 0}, false}, 151 {1, 1, DecimalType_{decimal.New(1, 0), false, 1, 1}, false}, 152 {1, 5, DecimalType_{}, true}, 153 {1, 10, DecimalType_{}, true}, 154 {1, 30, DecimalType_{}, true}, 155 {1, 65, DecimalType_{}, true}, 156 {1, 66, DecimalType_{}, true}, 157 {5, 0, DecimalType_{decimal.New(1, 5), false, 5, 0}, false}, 158 {5, 1, DecimalType_{decimal.New(1, 4), false, 5, 1}, false}, 159 {5, 5, DecimalType_{decimal.New(1, 0), false, 5, 5}, false}, 160 {5, 10, DecimalType_{}, true}, 161 {5, 30, DecimalType_{}, true}, 162 {5, 65, DecimalType_{}, true}, 163 {5, 66, DecimalType_{}, true}, 164 {10, 0, DecimalType_{decimal.New(1, 10), false, 10, 0}, false}, 165 {10, 1, DecimalType_{decimal.New(1, 9), false, 10, 1}, false}, 166 {10, 5, DecimalType_{decimal.New(1, 5), false, 10, 5}, false}, 167 {10, 10, DecimalType_{decimal.New(1, 0), false, 10, 10}, false}, 168 {10, 30, DecimalType_{}, true}, 169 {10, 65, DecimalType_{}, true}, 170 {10, 66, DecimalType_{}, true}, 171 {30, 0, DecimalType_{decimal.New(1, 30), false, 30, 0}, false}, 172 {30, 1, DecimalType_{decimal.New(1, 29), false, 30, 1}, false}, 173 {30, 5, DecimalType_{decimal.New(1, 25), false, 30, 5}, false}, 174 {30, 10, DecimalType_{decimal.New(1, 20), false, 30, 10}, false}, 175 {30, 30, DecimalType_{decimal.New(1, 0), false, 30, 30}, false}, 176 {30, 65, DecimalType_{}, true}, 177 {30, 66, DecimalType_{}, true}, 178 {65, 0, DecimalType_{decimal.New(1, 65), false, 65, 0}, false}, 179 {65, 1, DecimalType_{decimal.New(1, 64), false, 65, 1}, false}, 180 {65, 5, DecimalType_{decimal.New(1, 60), false, 65, 5}, false}, 181 {65, 10, DecimalType_{decimal.New(1, 55), false, 65, 10}, false}, 182 {65, 30, DecimalType_{decimal.New(1, 35), false, 65, 30}, false}, 183 {65, 65, DecimalType_{}, true}, 184 {65, 66, DecimalType_{}, true}, 185 {66, 00, DecimalType_{}, true}, 186 {66, 01, DecimalType_{}, true}, 187 {66, 05, DecimalType_{}, true}, 188 {66, 10, DecimalType_{}, true}, 189 {66, 30, DecimalType_{}, true}, 190 {66, 65, DecimalType_{}, true}, 191 {66, 66, DecimalType_{}, true}, 192 } 193 194 for _, test := range tests { 195 t.Run(fmt.Sprintf("%v %v", test.precision, test.scale), func(t *testing.T) { 196 typ, err := CreateDecimalType(test.precision, test.scale) 197 if test.expectedErr { 198 assert.Error(t, err) 199 } else { 200 require.NoError(t, err) 201 assert.Equal(t, test.expectedType, typ) 202 } 203 }) 204 } 205 } 206 207 func TestCreateColumnDecimal(t *testing.T) { 208 tests := []struct { 209 precision uint8 210 scale uint8 211 expectedType DecimalType_ 212 expectedErr bool 213 }{ 214 {0, 0, DecimalType_{decimal.New(1, 10), true, 10, 0}, false}, 215 {0, 1, DecimalType_{}, true}, 216 {0, 5, DecimalType_{}, true}, 217 {0, 10, DecimalType_{}, true}, 218 {0, 30, DecimalType_{}, true}, 219 {0, 65, DecimalType_{}, true}, 220 {0, 66, DecimalType_{}, true}, 221 {1, 0, DecimalType_{decimal.New(1, 1), true, 1, 0}, false}, 222 {1, 1, DecimalType_{decimal.New(1, 0), true, 1, 1}, false}, 223 {1, 5, DecimalType_{}, true}, 224 {1, 10, DecimalType_{}, true}, 225 {1, 30, DecimalType_{}, true}, 226 {1, 65, DecimalType_{}, true}, 227 {1, 66, DecimalType_{}, true}, 228 {5, 0, DecimalType_{decimal.New(1, 5), true, 5, 0}, false}, 229 {5, 1, DecimalType_{decimal.New(1, 4), true, 5, 1}, false}, 230 {5, 5, DecimalType_{decimal.New(1, 0), true, 5, 5}, false}, 231 {5, 10, DecimalType_{}, true}, 232 {5, 30, DecimalType_{}, true}, 233 {5, 65, DecimalType_{}, true}, 234 {5, 66, DecimalType_{}, true}, 235 {10, 0, DecimalType_{decimal.New(1, 10), true, 10, 0}, false}, 236 {10, 1, DecimalType_{decimal.New(1, 9), true, 10, 1}, false}, 237 {10, 5, DecimalType_{decimal.New(1, 5), true, 10, 5}, false}, 238 {10, 10, DecimalType_{decimal.New(1, 0), true, 10, 10}, false}, 239 {10, 30, DecimalType_{}, true}, 240 {10, 65, DecimalType_{}, true}, 241 {10, 66, DecimalType_{}, true}, 242 {30, 0, DecimalType_{decimal.New(1, 30), true, 30, 0}, false}, 243 {30, 1, DecimalType_{decimal.New(1, 29), true, 30, 1}, false}, 244 {30, 5, DecimalType_{decimal.New(1, 25), true, 30, 5}, false}, 245 {30, 10, DecimalType_{decimal.New(1, 20), true, 30, 10}, false}, 246 {30, 30, DecimalType_{decimal.New(1, 0), true, 30, 30}, false}, 247 {30, 65, DecimalType_{}, true}, 248 {30, 66, DecimalType_{}, true}, 249 {65, 0, DecimalType_{decimal.New(1, 65), true, 65, 0}, false}, 250 {65, 1, DecimalType_{decimal.New(1, 64), true, 65, 1}, false}, 251 {65, 5, DecimalType_{decimal.New(1, 60), true, 65, 5}, false}, 252 {65, 10, DecimalType_{decimal.New(1, 55), true, 65, 10}, false}, 253 {65, 30, DecimalType_{decimal.New(1, 35), true, 65, 30}, false}, 254 {65, 65, DecimalType_{}, true}, 255 {65, 66, DecimalType_{}, true}, 256 {66, 00, DecimalType_{}, true}, 257 {66, 01, DecimalType_{}, true}, 258 {66, 05, DecimalType_{}, true}, 259 {66, 10, DecimalType_{}, true}, 260 {66, 30, DecimalType_{}, true}, 261 {66, 65, DecimalType_{}, true}, 262 {66, 66, DecimalType_{}, true}, 263 } 264 265 for _, test := range tests { 266 t.Run(fmt.Sprintf("%v %v", test.precision, test.scale), func(t *testing.T) { 267 typ, err := CreateColumnDecimalType(test.precision, test.scale) 268 if test.expectedErr { 269 assert.Error(t, err) 270 } else { 271 require.NoError(t, err) 272 assert.Equal(t, test.expectedType, typ) 273 } 274 }) 275 } 276 } 277 278 func TestDecimalConvert(t *testing.T) { 279 tests := []struct { 280 precision uint8 281 scale uint8 282 val interface{} 283 expectedVal interface{} 284 expectedErr bool 285 }{ 286 {1, 0, nil, nil, false}, 287 {1, 0, true, "1", false}, 288 {1, 0, false, "0", false}, 289 {1, 0, byte(0), "0", false}, 290 {1, 0, int8(3), "3", false}, 291 {1, 0, "-3.7e0", "-4", false}, 292 {1, 0, uint(4), "4", false}, 293 {1, 0, int16(9), "9", false}, 294 {1, 0, "0.00000000000000000003e20", "3", false}, 295 {1, 0, float64(-9.4), "-9", false}, 296 {1, 0, float32(9.5), "", true}, 297 {1, 0, int32(-10), "", true}, 298 299 {1, 1, 0, "0.0", false}, 300 {1, 1, .01, "0.0", false}, 301 {1, 1, .1, "0.1", false}, 302 {1, 1, ".22", "0.2", false}, 303 {1, 1, .55, "0.6", false}, 304 {1, 1, "-.7863294659345624", "-0.8", false}, 305 {1, 1, "2634193746329327479.32030573792e-19", "0.3", false}, 306 {1, 1, 1, "", true}, 307 {1, 1, new(big.Rat).SetInt64(2), "", true}, 308 309 {5, 0, 0, "0", false}, 310 {5, 0, 5000.2, "5000", false}, 311 {5, 0, "7742", "7742", false}, 312 {5, 0, new(big.Float).SetFloat64(-4723.875), "-4724", false}, 313 {5, 0, 99999, "99999", false}, 314 {5, 0, "0xf8e1", "63713", false}, 315 {5, 0, "0b1001110101100110", "40294", false}, 316 {5, 0, new(big.Rat).SetFrac64(999999, 10), "", true}, 317 {5, 0, 673927, "", true}, 318 319 {10, 5, 0, "0.00000", false}, 320 {10, 5, "99999.999994", "99999.99999", false}, 321 {10, 5, "5.5729136e3", "5572.91360", false}, 322 {10, 5, "600e-2", "6.00000", false}, 323 {10, 5, new(big.Rat).SetFrac64(-22, 7), "-3.14286", false}, 324 {10, 5, 100000, "", true}, 325 {10, 5, "-99999.999995", "", true}, 326 327 {65, 0, "99999999999999999999999999999999999999999999999999999999999999999", 328 "99999999999999999999999999999999999999999999999999999999999999999", false}, 329 {65, 0, "99999999999999999999999999999999999999999999999999999999999999999.1", 330 "99999999999999999999999999999999999999999999999999999999999999999", false}, 331 {65, 0, "99999999999999999999999999999999999999999999999999999999999999999.99", "", true}, 332 333 {65, 12, "16976349273982359874209023948672021737840592720387475.2719128737543572927374503832837350563300243035038234972093785", 334 "16976349273982359874209023948672021737840592720387475.271912873754", false}, 335 {65, 12, "99999999999999999999999999999999999999999999999999999.9999999999999", "", true}, 336 337 {20, 10, []byte{32}, nil, true}, 338 {20, 10, time.Date(2019, 12, 12, 12, 12, 12, 0, time.UTC), nil, true}, 339 } 340 341 for _, test := range tests { 342 t.Run(fmt.Sprintf("%v %v %v", test.precision, test.scale, test.val), func(t *testing.T) { 343 typ := MustCreateDecimalType(test.precision, test.scale) 344 val, _, err := typ.Convert(test.val) 345 if test.expectedErr { 346 assert.Error(t, err) 347 } else { 348 require.NoError(t, err) 349 if test.expectedVal == nil { 350 assert.Nil(t, val) 351 } else { 352 expectedVal, err := decimal.NewFromString(test.expectedVal.(string)) 353 require.NoError(t, err) 354 assert.True(t, expectedVal.Equal(val.(decimal.Decimal))) 355 assert.Equal(t, typ.ValueType(), reflect.TypeOf(val)) 356 } 357 } 358 }) 359 } 360 } 361 362 func TestDecimalString(t *testing.T) { 363 tests := []struct { 364 precision uint8 365 scale uint8 366 expectedStr string 367 }{ 368 {0, 0, "decimal(10,0)"}, 369 {1, 0, "decimal(1,0)"}, 370 {5, 0, "decimal(5,0)"}, 371 {10, 0, "decimal(10,0)"}, 372 {65, 0, "decimal(65,0)"}, 373 {1, 1, "decimal(1,1)"}, 374 {5, 1, "decimal(5,1)"}, 375 {10, 1, "decimal(10,1)"}, 376 {65, 1, "decimal(65,1)"}, 377 {5, 5, "decimal(5,5)"}, 378 {10, 5, "decimal(10,5)"}, 379 {65, 5, "decimal(65,5)"}, 380 {10, 10, "decimal(10,10)"}, 381 {65, 10, "decimal(65,10)"}, 382 {65, 30, "decimal(65,30)"}, 383 } 384 385 for _, test := range tests { 386 t.Run(fmt.Sprintf("%v %v %v", test.precision, test.scale, test.expectedStr), func(t *testing.T) { 387 str := MustCreateDecimalType(test.precision, test.scale).String() 388 assert.Equal(t, test.expectedStr, str) 389 }) 390 } 391 } 392 393 func TestDecimalZero(t *testing.T) { 394 tests := []struct { 395 precision uint8 396 scale uint8 397 }{ 398 {0, 0}, 399 {1, 0}, 400 {5, 0}, 401 {10, 0}, 402 {65, 0}, 403 {1, 1}, 404 {5, 1}, 405 {10, 1}, 406 {65, 1}, 407 {5, 5}, 408 {10, 5}, 409 {65, 5}, 410 {10, 10}, 411 {65, 10}, 412 {65, 30}, 413 } 414 415 for _, test := range tests { 416 t.Run(fmt.Sprintf("%v %v zero", test.precision, test.scale), func(t *testing.T) { 417 dt := MustCreateDecimalType(test.precision, test.scale) 418 _, ok := dt.Zero().(decimal.Decimal) 419 assert.True(t, ok) 420 }) 421 } 422 }