github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/memristed/memex/builtin_time_test.go (about) 1 // Copyright 2020 WHTCORPS INC, 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 // See the License for the specific language governing permissions and 12 // limitations under the License. 13 14 package memex 15 16 import ( 17 "math" 18 "strings" 19 "time" 20 21 . "github.com/whtcorpsinc/check" 22 "github.com/whtcorpsinc/errors" 23 "github.com/whtcorpsinc/BerolinaSQL/ast" 24 "github.com/whtcorpsinc/BerolinaSQL/charset" 25 "github.com/whtcorpsinc/BerolinaSQL/allegrosql" 26 "github.com/whtcorpsinc/BerolinaSQL/terror" 27 "github.com/whtcorpsinc/milevadb/stochastikctx" 28 "github.com/whtcorpsinc/milevadb/stochastikctx/stmtctx" 29 "github.com/whtcorpsinc/milevadb/stochastikctx/variable" 30 "github.com/whtcorpsinc/milevadb/types" 31 "github.com/whtcorpsinc/milevadb/soliton/chunk" 32 "github.com/whtcorpsinc/milevadb/soliton/mock" 33 "github.com/whtcorpsinc/milevadb/soliton/solitonutil" 34 "github.com/whtcorpsinc/milevadb/soliton/timeutil" 35 ) 36 37 func init() { 38 // Some test depends on the values of timeutil.SystemLocation() 39 // If we don't SetSystemTZ() here, the value would change unpredicblock. 40 // Affectd by the order whether a testsuite runs before or after integration test. 41 // Note, SetSystemTZ() is a sync.Once operation. 42 timeutil.SetSystemTZ("system") 43 } 44 45 func (s *testEvaluatorSuite) TestDate(c *C) { 46 tblDate := []struct { 47 Input interface{} 48 Expect interface{} 49 }{ 50 {nil, nil}, 51 // standard format 52 {"2011-12-13", "2011-12-13"}, 53 {"2011-12-13 10:10:10", "2011-12-13"}, 54 // alternative delimiters, any ASCII punctuation character is a valid delimiter, 55 // punctuation character is defined by C++ std::ispunct: any graphical character 56 // that is not alphanumeric. 57 {"2011\"12\"13", "2011-12-13"}, 58 {"2011#12#13", "2011-12-13"}, 59 {"2011$12$13", "2011-12-13"}, 60 {"2011%12%13", "2011-12-13"}, 61 {"2011&12&13", "2011-12-13"}, 62 {"2011'12'13", "2011-12-13"}, 63 {"2011(12(13", "2011-12-13"}, 64 {"2011)12)13", "2011-12-13"}, 65 {"2011*12*13", "2011-12-13"}, 66 {"2011+12+13", "2011-12-13"}, 67 {"2011,12,13", "2011-12-13"}, 68 {"2011.12.13", "2011-12-13"}, 69 {"2011/12/13", "2011-12-13"}, 70 {"2011:12:13", "2011-12-13"}, 71 {"2011;12;13", "2011-12-13"}, 72 {"2011<12<13", "2011-12-13"}, 73 {"2011=12=13", "2011-12-13"}, 74 {"2011>12>13", "2011-12-13"}, 75 {"2011?12?13", "2011-12-13"}, 76 {"2011@12@13", "2011-12-13"}, 77 {"2011[12[13", "2011-12-13"}, 78 {"2011\\12\\13", "2011-12-13"}, 79 {"2011]12]13", "2011-12-13"}, 80 {"2011^12^13", "2011-12-13"}, 81 {"2011_12_13", "2011-12-13"}, 82 {"2011`12`13", "2011-12-13"}, 83 {"2011{12{13", "2011-12-13"}, 84 {"2011|12|13", "2011-12-13"}, 85 {"2011}12}13", "2011-12-13"}, 86 {"2011~12~13", "2011-12-13"}, 87 // internal format (YYYYMMDD, YYYYYMMDDHHMMSS) 88 {"20111213", "2011-12-13"}, 89 {"111213", "2011-12-13"}, 90 // leading and trailing space 91 {" 2011-12-13", "2011-12-13"}, 92 {"2011-12-13 ", "2011-12-13"}, 93 {" 2011-12-13 ", "2011-12-13"}, 94 // extra dashes 95 {"2011-12--13", "2011-12-13"}, 96 {"2011--12-13", "2011-12-13"}, 97 {"2011----12----13", "2011-12-13"}, 98 // combinations 99 {" 2011----12----13 ", "2011-12-13"}, 100 // errors 101 {"2011 12 13", nil}, 102 {"2011A12A13", nil}, 103 {"2011T12T13", nil}, 104 } 105 dtblDate := tblToDtbl(tblDate) 106 for _, t := range dtblDate { 107 fc := funcs[ast.Date] 108 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 109 c.Assert(err, IsNil) 110 v, err := evalBuiltinFunc(f, chunk.Event{}) 111 c.Assert(err, IsNil) 112 c.Assert(v, solitonutil.CausetEquals, t["Expect"][0]) 113 } 114 115 // test year, month and day 116 tbl := []struct { 117 Input string 118 Year int64 119 Month int64 120 MonthName string 121 DayOfMonth int64 122 DayOfWeek int64 123 DayOfYear int64 124 WeekDay int64 125 DayName string 126 Week int64 127 WeekOfYear int64 128 YearWeek int64 129 }{ 130 {"2000-01-01", 2000, 1, "January", 1, 7, 1, 5, "Saturday", 0, 52, 199952}, 131 {"2011-11-11", 2011, 11, "November", 11, 6, 315, 4, "Friday", 45, 45, 201145}, 132 {"0000-01-01", int64(0), 1, "January", 1, 7, 1, 5, "Saturday", 1, 52, 1}, 133 } 134 135 dtbl := tblToDtbl(tbl) 136 for ith, t := range dtbl { 137 fc := funcs[ast.Year] 138 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 139 c.Assert(err, IsNil) 140 v, err := evalBuiltinFunc(f, chunk.Event{}) 141 c.Assert(err, IsNil) 142 c.Assert(v, solitonutil.CausetEquals, t["Year"][0]) 143 144 fc = funcs[ast.Month] 145 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 146 c.Assert(err, IsNil) 147 v, err = evalBuiltinFunc(f, chunk.Event{}) 148 c.Assert(err, IsNil) 149 c.Assert(v, solitonutil.CausetEquals, t["Month"][0]) 150 151 fc = funcs[ast.MonthName] 152 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 153 c.Assert(err, IsNil) 154 v, err = evalBuiltinFunc(f, chunk.Event{}) 155 c.Assert(err, IsNil) 156 c.Assert(v, solitonutil.CausetEquals, t["MonthName"][0]) 157 158 fc = funcs[ast.DayOfMonth] 159 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 160 c.Assert(err, IsNil) 161 v, err = evalBuiltinFunc(f, chunk.Event{}) 162 c.Assert(err, IsNil) 163 c.Assert(v, solitonutil.CausetEquals, t["DayOfMonth"][0]) 164 165 fc = funcs[ast.DayOfWeek] 166 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 167 c.Assert(err, IsNil) 168 v, err = evalBuiltinFunc(f, chunk.Event{}) 169 c.Assert(err, IsNil) 170 c.Assert(v, solitonutil.CausetEquals, t["DayOfWeek"][0]) 171 172 fc = funcs[ast.DayOfYear] 173 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 174 c.Assert(err, IsNil) 175 v, err = evalBuiltinFunc(f, chunk.Event{}) 176 c.Assert(err, IsNil) 177 c.Assert(v, solitonutil.CausetEquals, t["DayOfYear"][0]) 178 179 fc = funcs[ast.Weekday] 180 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 181 c.Assert(err, IsNil) 182 c.Assert(f, NotNil) 183 v, err = evalBuiltinFunc(f, chunk.Event{}) 184 c.Assert(err, IsNil) 185 c.Assert(v, solitonutil.CausetEquals, t["WeekDay"][0]) 186 187 fc = funcs[ast.DayName] 188 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 189 c.Assert(err, IsNil) 190 v, err = evalBuiltinFunc(f, chunk.Event{}) 191 c.Assert(err, IsNil) 192 c.Assert(v, solitonutil.CausetEquals, t["DayName"][0]) 193 194 fc = funcs[ast.Week] 195 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 196 c.Assert(err, IsNil) 197 v, err = evalBuiltinFunc(f, chunk.Event{}) 198 c.Assert(err, IsNil) 199 c.Assert(v, solitonutil.CausetEquals, t["Week"][0], Commentf("no.%d", ith)) 200 201 fc = funcs[ast.WeekOfYear] 202 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 203 c.Assert(err, IsNil) 204 v, err = evalBuiltinFunc(f, chunk.Event{}) 205 c.Assert(err, IsNil) 206 c.Assert(v, solitonutil.CausetEquals, t["WeekOfYear"][0]) 207 208 fc = funcs[ast.YearWeek] 209 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 210 c.Assert(err, IsNil) 211 v, err = evalBuiltinFunc(f, chunk.Event{}) 212 c.Assert(err, IsNil) 213 c.Assert(v, solitonutil.CausetEquals, t["YearWeek"][0], Commentf("no.%d", ith)) 214 } 215 216 // test nil 217 tblNil := []struct { 218 Input interface{} 219 Year interface{} 220 Month interface{} 221 MonthName interface{} 222 DayOfMonth interface{} 223 DayOfWeek interface{} 224 DayOfYear interface{} 225 WeekDay interface{} 226 DayName interface{} 227 Week interface{} 228 WeekOfYear interface{} 229 YearWeek interface{} 230 }{ 231 {nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}, 232 {"0000-00-00 00:00:00", 0, 0, nil, 0, nil, nil, nil, nil, nil, nil, nil}, 233 {"0000-00-00", 0, 0, nil, 0, nil, nil, nil, nil, nil, nil, nil}, 234 } 235 236 dtblNil := tblToDtbl(tblNil) 237 for _, t := range dtblNil { 238 fc := funcs[ast.Year] 239 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 240 c.Assert(err, IsNil) 241 v, err := evalBuiltinFunc(f, chunk.Event{}) 242 c.Assert(err, IsNil) 243 c.Assert(v, solitonutil.CausetEquals, t["Year"][0]) 244 245 fc = funcs[ast.Month] 246 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 247 c.Assert(err, IsNil) 248 v, err = evalBuiltinFunc(f, chunk.Event{}) 249 c.Assert(err, IsNil) 250 c.Assert(v, solitonutil.CausetEquals, t["Month"][0]) 251 252 fc = funcs[ast.MonthName] 253 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 254 c.Assert(err, IsNil) 255 v, err = evalBuiltinFunc(f, chunk.Event{}) 256 c.Assert(err, IsNil) 257 c.Assert(v, solitonutil.CausetEquals, t["MonthName"][0]) 258 259 fc = funcs[ast.DayOfMonth] 260 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 261 c.Assert(err, IsNil) 262 v, err = evalBuiltinFunc(f, chunk.Event{}) 263 c.Assert(err, IsNil) 264 c.Assert(v, solitonutil.CausetEquals, t["DayOfMonth"][0]) 265 266 fc = funcs[ast.DayOfWeek] 267 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 268 c.Assert(err, IsNil) 269 v, err = evalBuiltinFunc(f, chunk.Event{}) 270 c.Assert(err, IsNil) 271 c.Assert(v, solitonutil.CausetEquals, t["DayOfWeek"][0]) 272 273 fc = funcs[ast.DayOfYear] 274 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 275 c.Assert(err, IsNil) 276 v, err = evalBuiltinFunc(f, chunk.Event{}) 277 c.Assert(err, IsNil) 278 c.Assert(v, solitonutil.CausetEquals, t["DayOfYear"][0]) 279 280 fc = funcs[ast.Weekday] 281 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 282 c.Assert(err, IsNil) 283 c.Assert(f, NotNil) 284 v, err = evalBuiltinFunc(f, chunk.Event{}) 285 c.Assert(err, IsNil) 286 c.Assert(v, solitonutil.CausetEquals, t["WeekDay"][0]) 287 288 fc = funcs[ast.DayName] 289 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 290 c.Assert(err, IsNil) 291 v, err = evalBuiltinFunc(f, chunk.Event{}) 292 c.Assert(err, IsNil) 293 c.Assert(v, solitonutil.CausetEquals, t["DayName"][0]) 294 295 fc = funcs[ast.Week] 296 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 297 c.Assert(err, IsNil) 298 v, err = evalBuiltinFunc(f, chunk.Event{}) 299 c.Assert(err, IsNil) 300 c.Assert(v, solitonutil.CausetEquals, t["Week"][0]) 301 302 fc = funcs[ast.WeekOfYear] 303 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 304 c.Assert(err, IsNil) 305 v, err = evalBuiltinFunc(f, chunk.Event{}) 306 c.Assert(err, IsNil) 307 c.Assert(v, solitonutil.CausetEquals, t["WeekOfYear"][0]) 308 309 fc = funcs[ast.YearWeek] 310 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 311 c.Assert(err, IsNil) 312 v, err = evalBuiltinFunc(f, chunk.Event{}) 313 c.Assert(err, IsNil) 314 c.Assert(v, solitonutil.CausetEquals, t["YearWeek"][0]) 315 } 316 317 // test nil with 'NO_ZERO_DATE' set in sql_mode 318 tblNil = []struct { 319 Input interface{} 320 Year interface{} 321 Month interface{} 322 MonthName interface{} 323 DayOfMonth interface{} 324 DayOfWeek interface{} 325 DayOfYear interface{} 326 WeekDay interface{} 327 DayName interface{} 328 Week interface{} 329 WeekOfYear interface{} 330 YearWeek interface{} 331 }{ 332 {nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}, 333 {"0000-00-00 00:00:00", nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}, 334 {"0000-00-00", nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}, 335 } 336 337 dtblNil = tblToDtbl(tblNil) 338 s.ctx.GetStochastikVars().SetSystemVar("sql_mode", "NO_ZERO_DATE") 339 for _, t := range dtblNil { 340 fc := funcs[ast.Year] 341 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 342 c.Assert(err, IsNil) 343 v, err := evalBuiltinFunc(f, chunk.Event{}) 344 c.Assert(err, IsNil) 345 c.Assert(v, solitonutil.CausetEquals, t["Year"][0]) 346 347 fc = funcs[ast.Month] 348 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 349 c.Assert(err, IsNil) 350 v, err = evalBuiltinFunc(f, chunk.Event{}) 351 c.Assert(err, IsNil) 352 c.Assert(v, solitonutil.CausetEquals, t["Month"][0]) 353 354 fc = funcs[ast.MonthName] 355 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 356 c.Assert(err, IsNil) 357 v, err = evalBuiltinFunc(f, chunk.Event{}) 358 c.Assert(err, IsNil) 359 c.Assert(v, solitonutil.CausetEquals, t["MonthName"][0]) 360 361 fc = funcs[ast.DayOfMonth] 362 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 363 c.Assert(err, IsNil) 364 v, err = evalBuiltinFunc(f, chunk.Event{}) 365 c.Assert(err, IsNil) 366 c.Assert(v, solitonutil.CausetEquals, t["DayOfMonth"][0]) 367 368 fc = funcs[ast.DayOfWeek] 369 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 370 c.Assert(err, IsNil) 371 v, err = evalBuiltinFunc(f, chunk.Event{}) 372 c.Assert(err, IsNil) 373 c.Assert(v, solitonutil.CausetEquals, t["DayOfWeek"][0]) 374 375 fc = funcs[ast.DayOfYear] 376 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 377 c.Assert(err, IsNil) 378 v, err = evalBuiltinFunc(f, chunk.Event{}) 379 c.Assert(err, IsNil) 380 c.Assert(v, solitonutil.CausetEquals, t["DayOfYear"][0]) 381 382 fc = funcs[ast.Weekday] 383 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 384 c.Assert(err, IsNil) 385 c.Assert(f, NotNil) 386 v, err = evalBuiltinFunc(f, chunk.Event{}) 387 c.Assert(err, IsNil) 388 c.Assert(v, solitonutil.CausetEquals, t["WeekDay"][0]) 389 390 fc = funcs[ast.DayName] 391 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 392 c.Assert(err, IsNil) 393 v, err = evalBuiltinFunc(f, chunk.Event{}) 394 c.Assert(err, IsNil) 395 c.Assert(v, solitonutil.CausetEquals, t["DayName"][0]) 396 397 fc = funcs[ast.Week] 398 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 399 c.Assert(err, IsNil) 400 v, err = evalBuiltinFunc(f, chunk.Event{}) 401 c.Assert(err, IsNil) 402 c.Assert(v, solitonutil.CausetEquals, t["Week"][0]) 403 404 fc = funcs[ast.WeekOfYear] 405 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 406 c.Assert(err, IsNil) 407 v, err = evalBuiltinFunc(f, chunk.Event{}) 408 c.Assert(err, IsNil) 409 c.Assert(v, solitonutil.CausetEquals, t["WeekOfYear"][0]) 410 411 fc = funcs[ast.YearWeek] 412 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 413 c.Assert(err, IsNil) 414 v, err = evalBuiltinFunc(f, chunk.Event{}) 415 c.Assert(err, IsNil) 416 c.Assert(v, solitonutil.CausetEquals, t["YearWeek"][0]) 417 } 418 } 419 420 func (s *testEvaluatorSuite) TestMonthName(c *C) { 421 sc := s.ctx.GetStochastikVars().StmtCtx 422 sc.IgnoreZeroInDate = true 423 cases := []struct { 424 args interface{} 425 expected string 426 isNil bool 427 getErr bool 428 }{ 429 {"2020-12-01", "December", false, false}, 430 {"2020-00-01", "", true, false}, 431 {"0000-00-00", "", true, false}, 432 {"0000-00-00 00:00:00.000000", "", true, false}, 433 {"0000-00-00 00:00:11.000000", "", true, false}, 434 } 435 for _, t := range cases { 436 f, err := newFunctionForTest(s.ctx, ast.MonthName, s.primitiveValsToConstants([]interface{}{t.args})...) 437 c.Assert(err, IsNil) 438 d, err := f.Eval(chunk.Event{}) 439 if t.getErr { 440 c.Assert(err, NotNil) 441 } else { 442 c.Assert(err, IsNil) 443 if t.isNil { 444 c.Assert(d.HoTT(), Equals, types.HoTTNull) 445 } else { 446 c.Assert(d.GetString(), Equals, t.expected) 447 } 448 } 449 } 450 451 _, err := funcs[ast.MonthName].getFunction(s.ctx, []Expression{NewZero()}) 452 c.Assert(err, IsNil) 453 } 454 455 func (s *testEvaluatorSuite) TestDayName(c *C) { 456 sc := s.ctx.GetStochastikVars().StmtCtx 457 sc.IgnoreZeroInDate = true 458 cases := []struct { 459 args interface{} 460 expected string 461 isNil bool 462 getErr bool 463 }{ 464 {"2020-12-01", "Friday", false, false}, 465 {"0000-12-01", "Friday", false, false}, 466 {"2020-00-01", "", true, false}, 467 {"2020-01-00", "", true, false}, 468 {"0000-00-00", "", true, false}, 469 {"0000-00-00 00:00:00.000000", "", true, false}, 470 {"0000-00-00 00:00:11.000000", "", true, false}, 471 } 472 for _, t := range cases { 473 f, err := newFunctionForTest(s.ctx, ast.DayName, s.primitiveValsToConstants([]interface{}{t.args})...) 474 c.Assert(err, IsNil) 475 d, err := f.Eval(chunk.Event{}) 476 if t.getErr { 477 c.Assert(err, NotNil) 478 } else { 479 c.Assert(err, IsNil) 480 if t.isNil { 481 c.Assert(d.HoTT(), Equals, types.HoTTNull) 482 } else { 483 c.Assert(d.GetString(), Equals, t.expected) 484 } 485 } 486 } 487 488 _, err := funcs[ast.DayName].getFunction(s.ctx, []Expression{NewZero()}) 489 c.Assert(err, IsNil) 490 } 491 492 func (s *testEvaluatorSuite) TestDayOfWeek(c *C) { 493 sc := s.ctx.GetStochastikVars().StmtCtx 494 sc.IgnoreZeroInDate = true 495 cases := []struct { 496 args interface{} 497 expected int64 498 isNil bool 499 getErr bool 500 }{ 501 {"2020-12-01", 6, false, false}, 502 {"0000-00-00", 1, true, false}, 503 {"2020-00-00", 1, true, false}, 504 {"2020-00-00 12:12:12", 1, true, false}, 505 {"0000-00-00 12:12:12", 1, true, false}, 506 } 507 for _, t := range cases { 508 f, err := newFunctionForTest(s.ctx, ast.DayOfWeek, s.primitiveValsToConstants([]interface{}{t.args})...) 509 c.Assert(err, IsNil) 510 d, err := f.Eval(chunk.Event{}) 511 if t.getErr { 512 c.Assert(err, NotNil) 513 } else { 514 c.Assert(err, IsNil) 515 if t.isNil { 516 c.Assert(d.HoTT(), Equals, types.HoTTNull) 517 } else { 518 c.Assert(d.GetInt64(), Equals, t.expected) 519 } 520 } 521 } 522 523 _, err := funcs[ast.DayOfWeek].getFunction(s.ctx, []Expression{NewZero()}) 524 c.Assert(err, IsNil) 525 } 526 527 func (s *testEvaluatorSuite) TestDayOfMonth(c *C) { 528 sc := s.ctx.GetStochastikVars().StmtCtx 529 sc.IgnoreZeroInDate = true 530 cases := []struct { 531 args interface{} 532 expected int64 533 isNil bool 534 getErr bool 535 }{ 536 {"2020-12-01", 1, false, false}, 537 {"0000-00-00", 0, false, false}, 538 {"2020-00-00", 0, false, false}, 539 {"2020-00-00 12:12:12", 0, false, false}, 540 {"0000-00-00 12:12:12", 0, false, false}, 541 } 542 for _, t := range cases { 543 f, err := newFunctionForTest(s.ctx, ast.DayOfMonth, s.primitiveValsToConstants([]interface{}{t.args})...) 544 c.Assert(err, IsNil) 545 d, err := f.Eval(chunk.Event{}) 546 if t.getErr { 547 c.Assert(err, NotNil) 548 } else { 549 c.Assert(err, IsNil) 550 if t.isNil { 551 c.Assert(d.HoTT(), Equals, types.HoTTNull) 552 } else { 553 c.Assert(d.GetInt64(), Equals, t.expected) 554 } 555 } 556 } 557 558 _, err := funcs[ast.DayOfMonth].getFunction(s.ctx, []Expression{NewZero()}) 559 c.Assert(err, IsNil) 560 } 561 562 func (s *testEvaluatorSuite) TestDayOfYear(c *C) { 563 sc := s.ctx.GetStochastikVars().StmtCtx 564 sc.IgnoreZeroInDate = true 565 cases := []struct { 566 args interface{} 567 expected int64 568 isNil bool 569 getErr bool 570 }{ 571 {"2020-12-01", 335, false, false}, 572 {"0000-00-00", 1, true, false}, 573 {"2020-00-00", 0, true, false}, 574 {"2020-00-00 12:12:12", 0, true, false}, 575 {"0000-00-00 12:12:12", 0, true, false}, 576 } 577 for _, t := range cases { 578 f, err := newFunctionForTest(s.ctx, ast.DayOfYear, s.primitiveValsToConstants([]interface{}{t.args})...) 579 c.Assert(err, IsNil) 580 d, err := f.Eval(chunk.Event{}) 581 if t.getErr { 582 c.Assert(err, NotNil) 583 } else { 584 c.Assert(err, IsNil) 585 if t.isNil { 586 c.Assert(d.HoTT(), Equals, types.HoTTNull) 587 } else { 588 c.Assert(d.GetInt64(), Equals, t.expected) 589 } 590 } 591 } 592 593 _, err := funcs[ast.DayOfYear].getFunction(s.ctx, []Expression{NewZero()}) 594 c.Assert(err, IsNil) 595 } 596 597 func (s *testEvaluatorSuite) TestDateFormat(c *C) { 598 // Test case for https://github.com/whtcorpsinc/milevadb/issues/2908 599 // SELECT DATE_FORMAT(null,'%Y-%M-%D') 600 args := []types.Causet{types.NewCauset(nil), types.NewStringCauset("%Y-%M-%D")} 601 fc := funcs[ast.DateFormat] 602 f, err := fc.getFunction(s.ctx, s.datumsToConstants(args)) 603 c.Assert(err, IsNil) 604 v, err := evalBuiltinFunc(f, chunk.Event{}) 605 c.Assert(err, IsNil) 606 c.Assert(v.IsNull(), Equals, true) 607 608 tblDate := []struct { 609 Input []string 610 Expect interface{} 611 }{ 612 {[]string{"2010-01-07 23:12:34.12345", 613 `%b %M %m %c %D %d %e %j %k %h %i %p %r %T %s %f %U %u %V %v %a %W %w %X %x %Y %y %%`}, 614 `Jan January 01 1 7th 07 7 007 23 11 12 PM 11:12:34 PM 23:12:34 34 123450 01 01 01 01 Thu Thursday 4 2010 2010 2010 10 %`}, 615 {[]string{"2012-12-21 23:12:34.123456", 616 `%b %M %m %c %D %d %e %j %k %h %i %p %r %T %s %f %U %u %V %v %a %W %w %X %x %Y %y %%`}, 617 "Dec December 12 12 21st 21 21 356 23 11 12 PM 11:12:34 PM 23:12:34 34 123456 51 51 51 51 Fri Friday 5 2012 2012 2012 12 %"}, 618 {[]string{"0000-01-01 00:00:00.123456", 619 // Functions week() and yearweek() don't support multi mode, 620 // so the result of "%U %u %V %Y" is different from MyALLEGROSQL. 621 `%b %M %m %c %D %d %e %j %k %h %i %p %r %T %s %f %v %x %Y %y %%`}, 622 `Jan January 01 1 1st 01 1 001 0 12 00 AM 12:00:00 AM 00:00:00 00 123456 52 4294967295 0000 00 %`}, 623 {[]string{"2020-09-3 00:59:59.123456", 624 `abc%b %M %m %c %D %d %e %j %k %h %i %p %r %T %s %f %U %u %V %v %a %W %w %X %x %Y %y!123 %%xyz %z`}, 625 `abcSep September 09 9 3rd 03 3 247 0 12 59 AM 12:59:59 AM 00:59:59 59 123456 35 35 35 35 Sat Saturday 6 2020 2020 2020 16!123 %xyz z`}, 626 {[]string{"2012-10-01 00:00:00", 627 `%b %M %m %c %D %d %e %j %k %H %i %p %r %T %s %f %v %x %Y %y %%`}, 628 `Oct October 10 10 1st 01 1 275 0 00 00 AM 12:00:00 AM 00:00:00 00 000000 40 2012 2012 12 %`}, 629 } 630 dtblDate := tblToDtbl(tblDate) 631 for i, t := range dtblDate { 632 fc := funcs[ast.DateFormat] 633 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 634 c.Assert(err, IsNil) 635 v, err := evalBuiltinFunc(f, chunk.Event{}) 636 c.Assert(err, IsNil) 637 c.Assert(v, solitonutil.CausetEquals, t["Expect"][0], Commentf(`no.%d \nobtain:%v \nexpect:%v\n`, i, 638 v.GetValue(), t["Expect"][0].GetValue())) 639 } 640 } 641 642 func (s *testEvaluatorSuite) TestClock(c *C) { 643 // test hour, minute, second, micro second 644 645 tbl := []struct { 646 Input string 647 Hour int64 648 Minute int64 649 Second int64 650 MicroSecond int64 651 Time string 652 }{ 653 {"10:10:10.123456", 10, 10, 10, 123456, "10:10:10.123456"}, 654 {"11:11:11.11", 11, 11, 11, 110000, "11:11:11.11"}, 655 {"2010-10-10 11:11:11.11", 11, 11, 11, 110000, "11:11:11.11"}, 656 } 657 658 dtbl := tblToDtbl(tbl) 659 for _, t := range dtbl { 660 fc := funcs[ast.Hour] 661 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 662 c.Assert(err, IsNil) 663 v, err := evalBuiltinFunc(f, chunk.Event{}) 664 c.Assert(err, IsNil) 665 c.Assert(v, solitonutil.CausetEquals, t["Hour"][0]) 666 667 fc = funcs[ast.Minute] 668 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 669 c.Assert(err, IsNil) 670 v, err = evalBuiltinFunc(f, chunk.Event{}) 671 c.Assert(err, IsNil) 672 c.Assert(v, solitonutil.CausetEquals, t["Minute"][0]) 673 674 fc = funcs[ast.Second] 675 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 676 c.Assert(err, IsNil) 677 v, err = evalBuiltinFunc(f, chunk.Event{}) 678 c.Assert(err, IsNil) 679 c.Assert(v, solitonutil.CausetEquals, t["Second"][0]) 680 681 fc = funcs[ast.MicroSecond] 682 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 683 c.Assert(err, IsNil) 684 v, err = evalBuiltinFunc(f, chunk.Event{}) 685 c.Assert(err, IsNil) 686 c.Assert(v, solitonutil.CausetEquals, t["MicroSecond"][0]) 687 688 fc = funcs[ast.Time] 689 f, err = fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 690 c.Assert(err, IsNil) 691 v, err = evalBuiltinFunc(f, chunk.Event{}) 692 c.Assert(err, IsNil) 693 c.Assert(v, solitonutil.CausetEquals, t["Time"][0]) 694 } 695 696 // nil 697 fc := funcs[ast.Hour] 698 f, err := fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(nil))) 699 c.Assert(err, IsNil) 700 v, err := evalBuiltinFunc(f, chunk.Event{}) 701 c.Assert(err, IsNil) 702 c.Assert(v.HoTT(), Equals, types.HoTTNull) 703 704 fc = funcs[ast.Minute] 705 f, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(nil))) 706 c.Assert(err, IsNil) 707 v, err = evalBuiltinFunc(f, chunk.Event{}) 708 c.Assert(err, IsNil) 709 c.Assert(v.HoTT(), Equals, types.HoTTNull) 710 711 fc = funcs[ast.Second] 712 f, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(nil))) 713 c.Assert(err, IsNil) 714 v, err = evalBuiltinFunc(f, chunk.Event{}) 715 c.Assert(err, IsNil) 716 c.Assert(v.HoTT(), Equals, types.HoTTNull) 717 718 fc = funcs[ast.MicroSecond] 719 f, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(nil))) 720 c.Assert(err, IsNil) 721 v, err = evalBuiltinFunc(f, chunk.Event{}) 722 c.Assert(err, IsNil) 723 c.Assert(v.HoTT(), Equals, types.HoTTNull) 724 725 fc = funcs[ast.Time] 726 f, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(nil))) 727 c.Assert(err, IsNil) 728 v, err = evalBuiltinFunc(f, chunk.Event{}) 729 c.Assert(err, IsNil) 730 c.Assert(v.HoTT(), Equals, types.HoTTNull) 731 732 // test error 733 errTbl := []string{ 734 "2011-11-11 10:10:10.11.12", 735 } 736 737 for _, t := range errTbl { 738 td := types.MakeCausets(t) 739 fc := funcs[ast.Hour] 740 f, err := fc.getFunction(s.ctx, s.datumsToConstants(td)) 741 c.Assert(err, IsNil) 742 _, err = evalBuiltinFunc(f, chunk.Event{}) 743 c.Assert(err, IsNil) 744 745 fc = funcs[ast.Minute] 746 f, err = fc.getFunction(s.ctx, s.datumsToConstants(td)) 747 c.Assert(err, IsNil) 748 _, err = evalBuiltinFunc(f, chunk.Event{}) 749 c.Assert(err, IsNil) 750 751 fc = funcs[ast.Second] 752 f, err = fc.getFunction(s.ctx, s.datumsToConstants(td)) 753 c.Assert(err, IsNil) 754 _, err = evalBuiltinFunc(f, chunk.Event{}) 755 c.Assert(err, IsNil) 756 757 fc = funcs[ast.MicroSecond] 758 f, err = fc.getFunction(s.ctx, s.datumsToConstants(td)) 759 c.Assert(err, IsNil) 760 _, err = evalBuiltinFunc(f, chunk.Event{}) 761 c.Assert(err, IsNil) 762 763 fc = funcs[ast.Time] 764 preWarningCnt := s.ctx.GetStochastikVars().StmtCtx.WarningCount() 765 f, err = fc.getFunction(s.ctx, s.datumsToConstants(td)) 766 c.Assert(err, IsNil) 767 _, err = evalBuiltinFunc(f, chunk.Event{}) 768 c.Assert(err, IsNil) 769 c.Assert(s.ctx.GetStochastikVars().StmtCtx.WarningCount(), Equals, preWarningCnt+1) 770 } 771 } 772 773 func (s *testEvaluatorSuite) TestTime(c *C) { 774 cases := []struct { 775 args interface{} 776 expected string 777 isNil bool 778 getErr bool 779 }{ 780 {"2003-12-31 01:02:03", "01:02:03", false, false}, 781 {"2003-12-31 01:02:03.000123", "01:02:03.000123", false, false}, 782 {"01:02:03.000123", "01:02:03.000123", false, false}, 783 {"01:02:03", "01:02:03", false, false}, 784 {"-838:59:59.000000", "-838:59:59.000000", false, false}, 785 } 786 787 for _, t := range cases { 788 f, err := newFunctionForTest(s.ctx, ast.Time, s.primitiveValsToConstants([]interface{}{t.args})...) 789 c.Assert(err, IsNil) 790 tp := f.GetType() 791 c.Assert(tp.Tp, Equals, allegrosql.TypeDuration) 792 c.Assert(tp.Charset, Equals, charset.CharsetBin) 793 c.Assert(tp.DefCauslate, Equals, charset.DefCauslationBin) 794 c.Assert(tp.Flag&allegrosql.BinaryFlag, Equals, allegrosql.BinaryFlag) 795 c.Assert(tp.Flen, Equals, allegrosql.MaxDurationWidthWithFsp) 796 d, err := f.Eval(chunk.Event{}) 797 if t.getErr { 798 c.Assert(err, NotNil) 799 } else { 800 c.Assert(err, IsNil) 801 if t.isNil { 802 c.Assert(d.HoTT(), Equals, types.HoTTNull) 803 } else { 804 c.Assert(d.GetMysqlDuration().String(), Equals, t.expected) 805 } 806 } 807 } 808 809 _, err := funcs[ast.Time].getFunction(s.ctx, []Expression{NewZero()}) 810 c.Assert(err, IsNil) 811 } 812 813 func resetStmtContext(ctx stochastikctx.Context) { 814 ctx.GetStochastikVars().StmtCtx.ResetNowTs() 815 } 816 817 func (s *testEvaluatorSuite) TestNowAndUTCTimestamp(c *C) { 818 gotime := func(t types.Time, l *time.Location) time.Time { 819 tt, err := t.GoTime(l) 820 c.Assert(err, IsNil) 821 return tt 822 } 823 824 for _, x := range []struct { 825 fc functionClass 826 now func() time.Time 827 }{ 828 {funcs[ast.Now], func() time.Time { return time.Now() }}, 829 {funcs[ast.UTCTimestamp], func() time.Time { return time.Now().UTC() }}, 830 } { 831 f, err := x.fc.getFunction(s.ctx, s.datumsToConstants(nil)) 832 c.Assert(err, IsNil) 833 resetStmtContext(s.ctx) 834 v, err := evalBuiltinFunc(f, chunk.Event{}) 835 ts := x.now() 836 c.Assert(err, IsNil) 837 t := v.GetMysqlTime() 838 // we canot use a constant value to check timestamp funcs, so here 839 // just to check the fractional seconds part and the time delta. 840 c.Assert(strings.Contains(t.String(), "."), IsFalse) 841 c.Assert(ts.Sub(gotime(t, ts.Location())), LessEqual, time.Second) 842 843 f, err = x.fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(6))) 844 c.Assert(err, IsNil) 845 resetStmtContext(s.ctx) 846 v, err = evalBuiltinFunc(f, chunk.Event{}) 847 ts = x.now() 848 c.Assert(err, IsNil) 849 t = v.GetMysqlTime() 850 c.Assert(strings.Contains(t.String(), "."), IsTrue) 851 c.Assert(ts.Sub(gotime(t, ts.Location())), LessEqual, time.Second) 852 853 resetStmtContext(s.ctx) 854 f, err = x.fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(8))) 855 c.Assert(err, IsNil) 856 _, err = evalBuiltinFunc(f, chunk.Event{}) 857 c.Assert(err, NotNil) 858 859 resetStmtContext(s.ctx) 860 f, err = x.fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(-2))) 861 c.Assert(err, IsNil) 862 _, err = evalBuiltinFunc(f, chunk.Event{}) 863 c.Assert(err, NotNil) 864 } 865 866 // Test that "timestamp" and "time_zone" variable may affect the result of Now() builtin function. 867 variable.SetStochastikSystemVar(s.ctx.GetStochastikVars(), "time_zone", types.NewCauset("+00:00")) 868 variable.SetStochastikSystemVar(s.ctx.GetStochastikVars(), "timestamp", types.NewCauset(1234)) 869 fc := funcs[ast.Now] 870 resetStmtContext(s.ctx) 871 f, err := fc.getFunction(s.ctx, s.datumsToConstants(nil)) 872 c.Assert(err, IsNil) 873 v, err := evalBuiltinFunc(f, chunk.Event{}) 874 c.Assert(err, IsNil) 875 result, err := v.ToString() 876 c.Assert(err, IsNil) 877 c.Assert(result, Equals, "1970-01-01 00:20:34") 878 variable.SetStochastikSystemVar(s.ctx.GetStochastikVars(), "timestamp", types.NewCauset(0)) 879 variable.SetStochastikSystemVar(s.ctx.GetStochastikVars(), "time_zone", types.NewCauset("system")) 880 } 881 882 func (s *testEvaluatorSuite) TestIsDuration(c *C) { 883 tbl := []struct { 884 Input string 885 expect bool 886 }{ 887 {"110:00:00", true}, 888 {"aa:bb:cc", false}, 889 {"1 01:00:00", true}, 890 {"01:00:00.999999", true}, 891 {"071231235959.999999", false}, 892 {"20171231235959.999999", false}, 893 {"2020-01-01 01:01:01.11", false}, 894 {"07-12-31 23:59:59.999999", false}, 895 {"2007-12-31 23:59:59.999999", false}, 896 } 897 for _, t := range tbl { 898 result := isDuration(t.Input) 899 c.Assert(result, Equals, t.expect) 900 } 901 } 902 903 func (s *testEvaluatorSuite) TestAddTimeSig(c *C) { 904 tbl := []struct { 905 Input string 906 InputDuration string 907 expect string 908 }{ 909 {"01:00:00.999999", "02:00:00.999998", "03:00:01.999997"}, 910 {"110:00:00", "1 02:00:00", "136:00:00"}, 911 {"2020-01-01 01:01:01.11", "01:01:01.11111", "2020-01-01 02:02:02.221110"}, 912 {"2007-12-31 23:59:59.999999", "1 1:1:1.000002", "2008-01-02 01:01:01.000001"}, 913 {"2020-12-01 01:01:01.000001", "1 1:1:1.000002", "2020-12-02 02:02:02.000003"}, 914 {"2020-12-31 23:59:59", "00:00:01", "2020-01-01 00:00:00"}, 915 {"2020-12-31 23:59:59", "1", "2020-01-01 00:00:00"}, 916 {"2007-12-31 23:59:59.999999", "2 1:1:1.000002", "2008-01-03 01:01:01.000001"}, 917 {"2020-08-16 20:21:01", "00:00:00.000001", "2020-08-16 20:21:01.000001"}, 918 } 919 fc := funcs[ast.AddTime] 920 for _, t := range tbl { 921 tmpInput := types.NewStringCauset(t.Input) 922 tmpInputDuration := types.NewStringCauset(t.InputDuration) 923 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{tmpInput, tmpInputDuration})) 924 c.Assert(err, IsNil) 925 d, err := evalBuiltinFunc(f, chunk.Event{}) 926 c.Assert(err, IsNil) 927 result, _ := d.ToString() 928 c.Assert(result, Equals, t.expect) 929 } 930 931 // This is a test for issue 7334 932 du := newDateArighmeticalUtil() 933 resetStmtContext(s.ctx) 934 now, _, err := evalNowWithFsp(s.ctx, 0) 935 c.Assert(err, IsNil) 936 res, _, err := du.add(s.ctx, now, "1", "MICROSECOND") 937 c.Assert(err, IsNil) 938 c.Assert(res.Fsp(), Equals, int8(6)) 939 940 tbl = []struct { 941 Input string 942 InputDuration string 943 expect string 944 }{ 945 {"01:00:00.999999", "02:00:00.999998", "03:00:01.999997"}, 946 {"23:59:59", "00:00:01", "24:00:00"}, 947 {"235959", "00:00:01", "24:00:00"}, 948 {"110:00:00", "1 02:00:00", "136:00:00"}, 949 {"-110:00:00", "1 02:00:00", "-84:00:00"}, 950 } 951 for _, t := range tbl { 952 dur, err := types.ParseDuration(s.ctx.GetStochastikVars().StmtCtx, t.Input, types.GetFsp(t.Input)) 953 c.Assert(err, IsNil) 954 tmpInput := types.NewDurationCauset(dur) 955 tmpInputDuration := types.NewStringCauset(t.InputDuration) 956 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{tmpInput, tmpInputDuration})) 957 c.Assert(err, IsNil) 958 d, err := evalBuiltinFunc(f, chunk.Event{}) 959 c.Assert(err, IsNil) 960 result, _ := d.ToString() 961 c.Assert(result, Equals, t.expect) 962 } 963 964 tbll := []struct { 965 Input int64 966 InputDuration int64 967 expect string 968 }{ 969 {20171010123456, 1, "2020-10-10 12:34:57"}, 970 {123456, 1, "12:34:57"}, 971 } 972 for _, t := range tbll { 973 tmpInput := types.NewIntCauset(t.Input) 974 tmpInputDuration := types.NewIntCauset(t.InputDuration) 975 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{tmpInput, tmpInputDuration})) 976 c.Assert(err, IsNil) 977 d, err := evalBuiltinFunc(f, chunk.Event{}) 978 c.Assert(err, IsNil) 979 result, _ := d.ToString() 980 c.Assert(result, Equals, t.expect) 981 } 982 983 tblWarning := []struct { 984 Input interface{} 985 InputDuration interface{} 986 warning *terror.Error 987 }{ 988 {"0", "-32073", types.ErrTruncatedWrongVal}, 989 {"-32073", "0", types.ErrTruncatedWrongVal}, 990 {types.ZeroDuration, "-32073", types.ErrTruncatedWrongVal}, 991 {"-32073", types.ZeroDuration, types.ErrTruncatedWrongVal}, 992 {types.CurrentTime(allegrosql.TypeTimestamp), "-32073", types.ErrTruncatedWrongVal}, 993 {types.CurrentTime(allegrosql.TypeDate), "-32073", types.ErrTruncatedWrongVal}, 994 {types.CurrentTime(allegrosql.TypeDatetime), "-32073", types.ErrTruncatedWrongVal}, 995 } 996 for i, t := range tblWarning { 997 tmpInput := types.NewCauset(t.Input) 998 tmpInputDuration := types.NewCauset(t.InputDuration) 999 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{tmpInput, tmpInputDuration})) 1000 c.Assert(err, IsNil) 1001 d, err := evalBuiltinFunc(f, chunk.Event{}) 1002 c.Assert(err, IsNil) 1003 result, _ := d.ToString() 1004 c.Assert(result, Equals, "") 1005 c.Assert(d.IsNull(), Equals, true) 1006 warnings := s.ctx.GetStochastikVars().StmtCtx.GetWarnings() 1007 c.Assert(len(warnings), Equals, i+1) 1008 c.Assert(terror.ErrorEqual(t.warning, warnings[i].Err), IsTrue, Commentf("err %v", warnings[i].Err)) 1009 } 1010 } 1011 1012 func (s *testEvaluatorSuite) TestSubTimeSig(c *C) { 1013 tbl := []struct { 1014 Input string 1015 InputDuration string 1016 expect string 1017 }{ 1018 {"01:00:00.999999", "02:00:00.999998", "-00:59:59.999999"}, 1019 {"110:00:00", "1 02:00:00", "84:00:00"}, 1020 {"2020-01-01 01:01:01.11", "01:01:01.11111", "2020-12-31 23:59:59.998890"}, 1021 {"2007-12-31 23:59:59.999999", "1 1:1:1.000002", "2007-12-30 22:58:58.999997"}, 1022 } 1023 fc := funcs[ast.SubTime] 1024 for _, t := range tbl { 1025 tmpInput := types.NewStringCauset(t.Input) 1026 tmpInputDuration := types.NewStringCauset(t.InputDuration) 1027 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{tmpInput, tmpInputDuration})) 1028 c.Assert(err, IsNil) 1029 d, err := evalBuiltinFunc(f, chunk.Event{}) 1030 c.Assert(err, IsNil) 1031 result, _ := d.ToString() 1032 c.Assert(result, Equals, t.expect) 1033 } 1034 1035 tbl = []struct { 1036 Input string 1037 InputDuration string 1038 expect string 1039 }{ 1040 {"03:00:00.999999", "02:00:00.999998", "01:00:00.000001"}, 1041 {"23:59:59", "00:00:01", "23:59:58"}, 1042 {"235959", "00:00:01", "23:59:58"}, 1043 } 1044 for _, t := range tbl { 1045 dur, err := types.ParseDuration(s.ctx.GetStochastikVars().StmtCtx, t.Input, types.GetFsp(t.Input)) 1046 c.Assert(err, IsNil) 1047 tmpInput := types.NewDurationCauset(dur) 1048 tmpInputDuration := types.NewStringCauset(t.InputDuration) 1049 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{tmpInput, tmpInputDuration})) 1050 c.Assert(err, IsNil) 1051 d, err := evalBuiltinFunc(f, chunk.Event{}) 1052 c.Assert(err, IsNil) 1053 result, _ := d.ToString() 1054 c.Assert(result, Equals, t.expect) 1055 } 1056 tbll := []struct { 1057 Input int64 1058 InputDuration int64 1059 expect string 1060 }{ 1061 {20171010123456, 1, "2020-10-10 12:34:55"}, 1062 {123456, 1, "12:34:55"}, 1063 } 1064 for _, t := range tbll { 1065 tmpInput := types.NewIntCauset(t.Input) 1066 tmpInputDuration := types.NewIntCauset(t.InputDuration) 1067 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{tmpInput, tmpInputDuration})) 1068 c.Assert(err, IsNil) 1069 d, err := evalBuiltinFunc(f, chunk.Event{}) 1070 c.Assert(err, IsNil) 1071 result, _ := d.ToString() 1072 c.Assert(result, Equals, t.expect) 1073 } 1074 1075 tblWarning := []struct { 1076 Input interface{} 1077 InputDuration interface{} 1078 warning *terror.Error 1079 }{ 1080 {"0", "-32073", types.ErrTruncatedWrongVal}, 1081 {"-32073", "0", types.ErrTruncatedWrongVal}, 1082 {types.ZeroDuration, "-32073", types.ErrTruncatedWrongVal}, 1083 {"-32073", types.ZeroDuration, types.ErrTruncatedWrongVal}, 1084 {types.CurrentTime(allegrosql.TypeTimestamp), "-32073", types.ErrTruncatedWrongVal}, 1085 {types.CurrentTime(allegrosql.TypeDate), "-32073", types.ErrTruncatedWrongVal}, 1086 {types.CurrentTime(allegrosql.TypeDatetime), "-32073", types.ErrTruncatedWrongVal}, 1087 } 1088 for i, t := range tblWarning { 1089 tmpInput := types.NewCauset(t.Input) 1090 tmpInputDuration := types.NewCauset(t.InputDuration) 1091 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{tmpInput, tmpInputDuration})) 1092 c.Assert(err, IsNil) 1093 d, err := evalBuiltinFunc(f, chunk.Event{}) 1094 c.Assert(err, IsNil) 1095 result, _ := d.ToString() 1096 c.Assert(result, Equals, "") 1097 c.Assert(d.IsNull(), Equals, true) 1098 warnings := s.ctx.GetStochastikVars().StmtCtx.GetWarnings() 1099 c.Assert(len(warnings), Equals, i+1) 1100 c.Assert(terror.ErrorEqual(t.warning, warnings[i].Err), IsTrue, Commentf("err %v", warnings[i].Err)) 1101 } 1102 } 1103 1104 func (s *testEvaluatorSuite) TestSysDate(c *C) { 1105 fc := funcs[ast.Sysdate] 1106 1107 ctx := mock.NewContext() 1108 ctx.GetStochastikVars().StmtCtx.TimeZone = timeutil.SystemLocation() 1109 timezones := []types.Causet{types.NewCauset(1234), types.NewCauset(0)} 1110 for _, timezone := range timezones { 1111 // sysdate() result is not affected by "timestamp" stochastik variable. 1112 variable.SetStochastikSystemVar(ctx.GetStochastikVars(), "timestamp", timezone) 1113 f, err := fc.getFunction(ctx, s.datumsToConstants(nil)) 1114 c.Assert(err, IsNil) 1115 resetStmtContext(s.ctx) 1116 v, err := evalBuiltinFunc(f, chunk.Event{}) 1117 last := time.Now() 1118 c.Assert(err, IsNil) 1119 n := v.GetMysqlTime() 1120 c.Assert(n.String(), GreaterEqual, last.Format(types.TimeFormat)) 1121 1122 baseFunc, _, input, output := genVecBuiltinFuncBenchCase(ctx, ast.Sysdate, vecExprBenchCase{retEvalType: types.ETDatetime}) 1123 resetStmtContext(s.ctx) 1124 err = baseFunc.vecEvalTime(input, output) 1125 c.Assert(err, IsNil) 1126 last = time.Now() 1127 times := output.Times() 1128 for i := 0; i < 1024; i++ { 1129 c.Assert(times[i].String(), GreaterEqual, last.Format(types.TimeFormat)) 1130 } 1131 1132 baseFunc, _, input, output = genVecBuiltinFuncBenchCase(ctx, ast.Sysdate, 1133 vecExprBenchCase{ 1134 retEvalType: types.ETDatetime, 1135 childrenTypes: []types.EvalType{types.ETInt}, 1136 geners: []dataGenerator{newRangeInt64Gener(0, 7)}, 1137 }) 1138 resetStmtContext(s.ctx) 1139 loc := ctx.GetStochastikVars().Location() 1140 startTm := time.Now().In(loc) 1141 err = baseFunc.vecEvalTime(input, output) 1142 c.Assert(err, IsNil) 1143 for i := 0; i < 1024; i++ { 1144 c.Assert(times[i].String(), GreaterEqual, startTm.Format(types.TimeFormat)) 1145 } 1146 } 1147 1148 last := time.Now() 1149 f, err := fc.getFunction(ctx, s.datumsToConstants(types.MakeCausets(6))) 1150 c.Assert(err, IsNil) 1151 resetStmtContext(s.ctx) 1152 v, err := evalBuiltinFunc(f, chunk.Event{}) 1153 c.Assert(err, IsNil) 1154 n := v.GetMysqlTime() 1155 c.Assert(n.String(), GreaterEqual, last.Format(types.TimeFormat)) 1156 1157 f, err = fc.getFunction(ctx, s.datumsToConstants(types.MakeCausets(-2))) 1158 c.Assert(err, IsNil) 1159 _, err = evalBuiltinFunc(f, chunk.Event{}) 1160 c.Assert(err, NotNil) 1161 } 1162 1163 func convertToTimeWithFsp(sc *stmtctx.StatementContext, arg types.Causet, tp byte, fsp int8) (d types.Causet, err error) { 1164 if fsp > types.MaxFsp { 1165 fsp = types.MaxFsp 1166 } 1167 1168 f := types.NewFieldType(tp) 1169 f.Decimal = int(fsp) 1170 1171 d, err = arg.ConvertTo(sc, f) 1172 if err != nil { 1173 d.SetNull() 1174 return d, err 1175 } 1176 1177 if d.IsNull() { 1178 return 1179 } 1180 1181 if d.HoTT() != types.HoTTMysqlTime { 1182 d.SetNull() 1183 return d, errors.Errorf("need time type, but got %T", d.GetValue()) 1184 } 1185 return 1186 } 1187 1188 func convertToTime(sc *stmtctx.StatementContext, arg types.Causet, tp byte) (d types.Causet, err error) { 1189 return convertToTimeWithFsp(sc, arg, tp, types.MaxFsp) 1190 } 1191 1192 func builtinDateFormat(ctx stochastikctx.Context, args []types.Causet) (d types.Causet, err error) { 1193 date, err := convertToTime(ctx.GetStochastikVars().StmtCtx, args[0], allegrosql.TypeDatetime) 1194 if err != nil { 1195 return d, err 1196 } 1197 1198 if date.IsNull() { 1199 return 1200 } 1201 t := date.GetMysqlTime() 1202 str, err := t.DateFormat(args[1].GetString()) 1203 if err != nil { 1204 return d, err 1205 } 1206 d.SetString(str, allegrosql.DefaultDefCauslationName) 1207 return 1208 } 1209 1210 func (s *testEvaluatorSuite) TestFromUnixTime(c *C) { 1211 tbl := []struct { 1212 isDecimal bool 1213 integralPart int64 1214 fractionalPart int64 1215 decimal float64 1216 format string 1217 expect string 1218 }{ 1219 {false, 1451606400, 0, 0, "", "2020-01-01 00:00:00"}, 1220 {true, 1451606400, 123456000, 1451606400.123456, "", "2020-01-01 00:00:00.123456"}, 1221 {true, 1451606400, 999999000, 1451606400.999999, "", "2020-01-01 00:00:00.999999"}, 1222 {true, 1451606400, 999999900, 1451606400.9999999, "", "2020-01-01 00:00:01.000000"}, 1223 {false, 1451606400, 0, 0, `%Y %D %M %h:%i:%s %x`, "2020-01-01 00:00:00"}, 1224 {true, 1451606400, 123456000, 1451606400.123456, `%Y %D %M %h:%i:%s %x`, "2020-01-01 00:00:00.123456"}, 1225 {true, 1451606400, 999999000, 1451606400.999999, `%Y %D %M %h:%i:%s %x`, "2020-01-01 00:00:00.999999"}, 1226 {true, 1451606400, 999999900, 1451606400.9999999, `%Y %D %M %h:%i:%s %x`, "2020-01-01 00:00:01.000000"}, 1227 } 1228 sc := s.ctx.GetStochastikVars().StmtCtx 1229 originTZ := sc.TimeZone 1230 sc.TimeZone = time.UTC 1231 defer func() { 1232 sc.TimeZone = originTZ 1233 }() 1234 fc := funcs[ast.FromUnixTime] 1235 for _, t := range tbl { 1236 var timestamp types.Causet 1237 if !t.isDecimal { 1238 timestamp.SetInt64(t.integralPart) 1239 } else { 1240 timestamp.SetFloat64(t.decimal) 1241 } 1242 // result of from_unixtime() is dependent on specific time zone. 1243 if len(t.format) == 0 { 1244 constants := s.datumsToConstants([]types.Causet{timestamp}) 1245 if !t.isDecimal { 1246 constants[0].GetType().Decimal = 0 1247 } 1248 1249 f, err := fc.getFunction(s.ctx, constants) 1250 c.Assert(err, IsNil) 1251 1252 v, err := evalBuiltinFunc(f, chunk.Event{}) 1253 c.Assert(err, IsNil) 1254 ans := v.GetMysqlTime() 1255 c.Assert(ans.String(), Equals, t.expect, Commentf("%+v", t)) 1256 } else { 1257 format := types.NewStringCauset(t.format) 1258 constants := s.datumsToConstants([]types.Causet{timestamp, format}) 1259 if !t.isDecimal { 1260 constants[0].GetType().Decimal = 0 1261 } 1262 f, err := fc.getFunction(s.ctx, constants) 1263 c.Assert(err, IsNil) 1264 v, err := evalBuiltinFunc(f, chunk.Event{}) 1265 c.Assert(err, IsNil) 1266 result, err := builtinDateFormat(s.ctx, []types.Causet{types.NewStringCauset(t.expect), format}) 1267 c.Assert(err, IsNil) 1268 c.Assert(v.GetString(), Equals, result.GetString(), Commentf("%+v", t)) 1269 } 1270 } 1271 1272 f, err := fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(-12345))) 1273 c.Assert(err, IsNil) 1274 v, err := evalBuiltinFunc(f, chunk.Event{}) 1275 c.Assert(err, IsNil) 1276 c.Assert(v.HoTT(), Equals, types.HoTTNull) 1277 1278 f, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(math.MaxInt32+1))) 1279 c.Assert(err, IsNil) 1280 _, err = evalBuiltinFunc(f, chunk.Event{}) 1281 c.Assert(err, IsNil) 1282 c.Assert(v.HoTT(), Equals, types.HoTTNull) 1283 } 1284 1285 func (s *testEvaluatorSuite) TestCurrentDate(c *C) { 1286 last := time.Now() 1287 fc := funcs[ast.CurrentDate] 1288 f, err := fc.getFunction(mock.NewContext(), s.datumsToConstants(nil)) 1289 c.Assert(err, IsNil) 1290 resetStmtContext(s.ctx) 1291 v, err := evalBuiltinFunc(f, chunk.Event{}) 1292 c.Assert(err, IsNil) 1293 n := v.GetMysqlTime() 1294 c.Assert(n.String(), GreaterEqual, last.Format(types.DateFormat)) 1295 } 1296 1297 func (s *testEvaluatorSuite) TestCurrentTime(c *C) { 1298 tfStr := "15:04:05" 1299 1300 last := time.Now() 1301 fc := funcs[ast.CurrentTime] 1302 f, err := fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(nil))) 1303 c.Assert(err, IsNil) 1304 resetStmtContext(s.ctx) 1305 v, err := evalBuiltinFunc(f, chunk.Event{}) 1306 c.Assert(err, IsNil) 1307 n := v.GetMysqlDuration() 1308 c.Assert(n.String(), HasLen, 8) 1309 c.Assert(n.String(), GreaterEqual, last.Format(tfStr)) 1310 1311 f, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(3))) 1312 c.Assert(err, IsNil) 1313 resetStmtContext(s.ctx) 1314 v, err = evalBuiltinFunc(f, chunk.Event{}) 1315 c.Assert(err, IsNil) 1316 n = v.GetMysqlDuration() 1317 c.Assert(n.String(), HasLen, 12) 1318 c.Assert(n.String(), GreaterEqual, last.Format(tfStr)) 1319 1320 f, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(6))) 1321 c.Assert(err, IsNil) 1322 resetStmtContext(s.ctx) 1323 v, err = evalBuiltinFunc(f, chunk.Event{}) 1324 c.Assert(err, IsNil) 1325 n = v.GetMysqlDuration() 1326 c.Assert(n.String(), HasLen, 15) 1327 c.Assert(n.String(), GreaterEqual, last.Format(tfStr)) 1328 1329 _, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(-1))) 1330 c.Assert(err, NotNil) 1331 1332 _, err = fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(7))) 1333 c.Assert(err, NotNil) 1334 } 1335 1336 func (s *testEvaluatorSuite) TestUTCTime(c *C) { 1337 last := time.Now().UTC() 1338 tfStr := "00:00:00" 1339 fc := funcs[ast.UTCTime] 1340 1341 tests := []struct { 1342 param interface{} 1343 expect int 1344 }{{0, 8}, {3, 12}, {6, 15}, {-1, 0}, {7, 0}} 1345 1346 for _, test := range tests { 1347 f, err := fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets(test.param))) 1348 c.Assert(err, IsNil) 1349 resetStmtContext(s.ctx) 1350 v, err := evalBuiltinFunc(f, chunk.Event{}) 1351 if test.expect > 0 { 1352 c.Assert(err, IsNil) 1353 n := v.GetMysqlDuration() 1354 c.Assert(n.String(), HasLen, test.expect) 1355 c.Assert(n.String(), GreaterEqual, last.Format(tfStr)) 1356 } else { 1357 c.Assert(err, NotNil) 1358 } 1359 } 1360 1361 f, err := fc.getFunction(s.ctx, make([]Expression, 0)) 1362 c.Assert(err, IsNil) 1363 resetStmtContext(s.ctx) 1364 v, err := evalBuiltinFunc(f, chunk.Event{}) 1365 c.Assert(err, IsNil) 1366 n := v.GetMysqlDuration() 1367 c.Assert(n.String(), HasLen, 8) 1368 c.Assert(n.String(), GreaterEqual, last.Format(tfStr)) 1369 } 1370 1371 func (s *testEvaluatorSuite) TestUTCDate(c *C) { 1372 last := time.Now().UTC() 1373 fc := funcs[ast.UTCDate] 1374 f, err := fc.getFunction(mock.NewContext(), s.datumsToConstants(nil)) 1375 c.Assert(err, IsNil) 1376 resetStmtContext(mock.NewContext()) 1377 v, err := evalBuiltinFunc(f, chunk.Event{}) 1378 c.Assert(err, IsNil) 1379 n := v.GetMysqlTime() 1380 c.Assert(n.String(), GreaterEqual, last.Format(types.DateFormat)) 1381 } 1382 1383 func (s *testEvaluatorSuite) TestStrToDate(c *C) { 1384 tests := []struct { 1385 Date string 1386 Format string 1387 Success bool 1388 Expect time.Time 1389 }{ 1390 {"10/28/2011 9:46:29 pm", "%m/%d/%Y %l:%i:%s %p", true, time.Date(2011, 10, 28, 21, 46, 29, 0, time.Local)}, 1391 {"10/28/2011 9:46:29 Pm", "%m/%d/%Y %l:%i:%s %p", true, time.Date(2011, 10, 28, 21, 46, 29, 0, time.Local)}, 1392 {"2011/10/28 9:46:29 am", "%Y/%m/%d %l:%i:%s %p", true, time.Date(2011, 10, 28, 9, 46, 29, 0, time.Local)}, 1393 {"20161122165022", `%Y%m%d%H%i%s`, true, time.Date(2020, 11, 22, 16, 50, 22, 0, time.Local)}, 1394 {"2020 11 22 16 50 22", `%Y%m%d%H%i%s`, true, time.Date(2020, 11, 22, 16, 50, 22, 0, time.Local)}, 1395 {"16-50-22 2020 11 22", `%H-%i-%s%Y%m%d`, true, time.Date(2020, 11, 22, 16, 50, 22, 0, time.Local)}, 1396 {"16-50 2020 11 22", `%H-%i-%s%Y%m%d`, false, time.Time{}}, 1397 {"15-01-2001 1:59:58.999", "%d-%m-%Y %I:%i:%s.%f", true, time.Date(2001, 1, 15, 1, 59, 58, 999000000, time.Local)}, 1398 {"15-01-2001 1:59:58.1", "%d-%m-%Y %H:%i:%s.%f", true, time.Date(2001, 1, 15, 1, 59, 58, 100000000, time.Local)}, 1399 {"15-01-2001 1:59:58.", "%d-%m-%Y %H:%i:%s.%f", true, time.Date(2001, 1, 15, 1, 59, 58, 000000000, time.Local)}, 1400 {"15-01-2001 1:9:8.999", "%d-%m-%Y %H:%i:%s.%f", true, time.Date(2001, 1, 15, 1, 9, 8, 999000000, time.Local)}, 1401 {"15-01-2001 1:9:8.999", "%d-%m-%Y %H:%i:%S.%f", true, time.Date(2001, 1, 15, 1, 9, 8, 999000000, time.Local)}, 1402 {"2003-01-02 10:11:12 PM", "%Y-%m-%d %H:%i:%S %p", false, time.Time{}}, 1403 {"10:20:10AM", "%H:%i:%S%p", false, time.Time{}}, 1404 } 1405 1406 fc := funcs[ast.StrToDate] 1407 for _, test := range tests { 1408 date := types.NewStringCauset(test.Date) 1409 format := types.NewStringCauset(test.Format) 1410 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{date, format})) 1411 c.Assert(err, IsNil) 1412 result, err := evalBuiltinFunc(f, chunk.Event{}) 1413 c.Assert(err, IsNil) 1414 if !test.Success { 1415 c.Assert(err, IsNil) 1416 c.Assert(result.IsNull(), IsTrue) 1417 continue 1418 } 1419 c.Assert(result.HoTT(), Equals, types.HoTTMysqlTime) 1420 value := result.GetMysqlTime() 1421 t1, _ := value.GoTime(time.Local) 1422 c.Assert(t1, Equals, test.Expect) 1423 } 1424 } 1425 1426 func (s *testEvaluatorSuite) TestFromDays(c *C) { 1427 stmtCtx := s.ctx.GetStochastikVars().StmtCtx 1428 origin := stmtCtx.IgnoreTruncate 1429 stmtCtx.IgnoreTruncate = true 1430 defer func() { 1431 stmtCtx.IgnoreTruncate = origin 1432 }() 1433 tests := []struct { 1434 day int64 1435 expect string 1436 }{ 1437 {-140, "0000-00-00"}, // allegrosql FROM_DAYS returns 0000-00-00 for any day <= 365. 1438 {140, "0000-00-00"}, // allegrosql FROM_DAYS returns 0000-00-00 for any day <= 365. 1439 {735000, "2012-05-12"}, // Leap year. 1440 {735030, "2012-06-11"}, 1441 {735130, "2012-09-19"}, 1442 {734909, "2012-02-11"}, 1443 {734878, "2012-01-11"}, 1444 {734927, "2012-02-29"}, 1445 {734634, "2011-05-12"}, // Non Leap year. 1446 {734664, "2011-06-11"}, 1447 {734764, "2011-09-19"}, 1448 {734544, "2011-02-11"}, 1449 {734513, "2011-01-11"}, 1450 } 1451 1452 fc := funcs[ast.FromDays] 1453 for _, test := range tests { 1454 t1 := types.NewIntCauset(test.day) 1455 1456 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{t1})) 1457 c.Assert(err, IsNil) 1458 c.Assert(f, NotNil) 1459 result, err := evalBuiltinFunc(f, chunk.Event{}) 1460 1461 c.Assert(err, IsNil) 1462 c.Assert(result.GetMysqlTime().String(), Equals, test.expect) 1463 } 1464 1465 stringTests := []struct { 1466 day string 1467 expect string 1468 }{ 1469 {"z550z", "0000-00-00"}, 1470 {"6500z", "0017-10-18"}, 1471 {"440", "0001-03-16"}, 1472 } 1473 1474 for _, test := range stringTests { 1475 t1 := types.NewStringCauset(test.day) 1476 1477 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{t1})) 1478 c.Assert(err, IsNil) 1479 result, err := evalBuiltinFunc(f, chunk.Event{}) 1480 1481 c.Assert(err, IsNil) 1482 c.Assert(result.GetMysqlTime().String(), Equals, test.expect) 1483 } 1484 } 1485 1486 func (s *testEvaluatorSuite) TestDateDiff(c *C) { 1487 // Test cases from https://dev.allegrosql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff 1488 tests := []struct { 1489 t1 string 1490 t2 string 1491 expect int64 1492 }{ 1493 {"2004-05-21", "2004:01:02", 140}, 1494 {"2004-04-21", "2000:01:02", 1571}, 1495 {"2008-12-31 23:59:59.000001", "2008-12-30 01:01:01.000002", 1}, 1496 {"1010-11-30 23:59:59", "2010-12-31", -365274}, 1497 {"1010-11-30", "2210-11-01", -438262}, 1498 } 1499 1500 fc := funcs[ast.DateDiff] 1501 for _, test := range tests { 1502 t1 := types.NewStringCauset(test.t1) 1503 t2 := types.NewStringCauset(test.t2) 1504 1505 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{t1, t2})) 1506 c.Assert(err, IsNil) 1507 result, err := evalBuiltinFunc(f, chunk.Event{}) 1508 1509 c.Assert(err, IsNil) 1510 c.Assert(result.GetInt64(), Equals, test.expect) 1511 } 1512 1513 // Test invalid time format. 1514 tests2 := []struct { 1515 t1 string 1516 t2 string 1517 }{ 1518 {"2004-05-21", "abcdefg"}, 1519 {"2007-12-31 23:59:59", "23:59:59"}, 1520 {"2007-00-31 23:59:59", "2020-01-13"}, 1521 {"2007-10-31 23:59:59", "2020-01-00"}, 1522 {"2007-10-31 23:59:59", "99999999-01-00"}, 1523 } 1524 1525 fc = funcs[ast.DateDiff] 1526 for _, test := range tests2 { 1527 t1 := types.NewStringCauset(test.t1) 1528 t2 := types.NewStringCauset(test.t2) 1529 1530 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{t1, t2})) 1531 c.Assert(err, IsNil) 1532 d, err := evalBuiltinFunc(f, chunk.Event{}) 1533 c.Assert(err, IsNil) 1534 c.Assert(d.IsNull(), IsTrue) 1535 } 1536 } 1537 1538 func (s *testEvaluatorSuite) TestTimeDiff(c *C) { 1539 sc := s.ctx.GetStochastikVars().StmtCtx 1540 sc.IgnoreZeroInDate = true 1541 // Test cases from https://dev.allegrosql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timediff 1542 tests := []struct { 1543 args []interface{} 1544 expectStr string 1545 isNil bool 1546 fsp int8 1547 getWarning bool 1548 }{ 1549 {[]interface{}{"2000:01:01 00:00:00", "2000:01:01 00:00:00.000001"}, "-00:00:00.000001", false, 6, false}, 1550 {[]interface{}{"2008-12-31 23:59:59.000001", "2008-12-30 01:01:01.000002"}, "46:58:57.999999", false, 6, false}, 1551 {[]interface{}{"2020-12-00 12:00:00", "2020-12-01 12:00:00"}, "-24:00:00", false, 0, false}, 1552 {[]interface{}{"10:10:10", "10:9:0"}, "00:01:10", false, 0, false}, 1553 {[]interface{}{"2020-12-00 12:00:00", "10:9:0"}, "", true, 0, false}, 1554 {[]interface{}{"2020-12-00 12:00:00", ""}, "", true, 0, true}, 1555 } 1556 1557 for _, t := range tests { 1558 preWarningCnt := s.ctx.GetStochastikVars().StmtCtx.WarningCount() 1559 f, err := newFunctionForTest(s.ctx, ast.TimeDiff, s.primitiveValsToConstants(t.args)...) 1560 c.Assert(err, IsNil) 1561 tp := f.GetType() 1562 c.Assert(tp.Tp, Equals, allegrosql.TypeDuration) 1563 c.Assert(tp.Charset, Equals, charset.CharsetBin) 1564 c.Assert(tp.DefCauslate, Equals, charset.DefCauslationBin) 1565 c.Assert(tp.Flag, Equals, allegrosql.BinaryFlag) 1566 c.Assert(tp.Flen, Equals, allegrosql.MaxDurationWidthWithFsp) 1567 d, err := f.Eval(chunk.Event{}) 1568 if t.getWarning { 1569 c.Assert(err, IsNil) 1570 c.Assert(s.ctx.GetStochastikVars().StmtCtx.WarningCount(), Equals, preWarningCnt+1) 1571 } else { 1572 c.Assert(err, IsNil) 1573 if t.isNil { 1574 c.Assert(d.HoTT(), Equals, types.HoTTNull) 1575 } else { 1576 c.Assert(d.GetMysqlDuration().String(), Equals, t.expectStr) 1577 c.Assert(d.GetMysqlDuration().Fsp, Equals, t.fsp) 1578 } 1579 } 1580 } 1581 _, err := funcs[ast.TimeDiff].getFunction(s.ctx, []Expression{NewZero(), NewZero()}) 1582 c.Assert(err, IsNil) 1583 } 1584 1585 func (s *testEvaluatorSuite) TestWeek(c *C) { 1586 // Test cases from https://dev.allegrosql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week 1587 tests := []struct { 1588 t string 1589 mode int64 1590 expect int64 1591 }{ 1592 {"2008-02-20", 0, 7}, 1593 {"2008-02-20", 1, 8}, 1594 {"2008-12-31", 1, 53}, 1595 } 1596 fc := funcs[ast.Week] 1597 for _, test := range tests { 1598 arg1 := types.NewStringCauset(test.t) 1599 arg2 := types.NewIntCauset(test.mode) 1600 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{arg1, arg2})) 1601 c.Assert(err, IsNil) 1602 result, err := evalBuiltinFunc(f, chunk.Event{}) 1603 c.Assert(err, IsNil) 1604 c.Assert(result.GetInt64(), Equals, test.expect) 1605 } 1606 } 1607 1608 func (s *testEvaluatorSuite) TestWeekWithoutModeSig(c *C) { 1609 tests := []struct { 1610 t string 1611 expect int64 1612 }{ 1613 {"2008-02-20", 7}, 1614 {"2000-12-31", 53}, 1615 {"2000-12-31", 1}, //set default week mode 1616 {"2005-12-3", 48}, //set default week mode 1617 {"2008-02-20", 7}, 1618 } 1619 1620 fc := funcs[ast.Week] 1621 for i, test := range tests { 1622 arg1 := types.NewStringCauset(test.t) 1623 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{arg1})) 1624 c.Assert(err, IsNil) 1625 result, err := evalBuiltinFunc(f, chunk.Event{}) 1626 c.Assert(err, IsNil) 1627 c.Assert(result.GetInt64(), Equals, test.expect) 1628 if i == 1 { 1629 s.ctx.GetStochastikVars().SetSystemVar("default_week_format", "6") 1630 } else if i == 3 { 1631 s.ctx.GetStochastikVars().SetSystemVar("default_week_format", "") 1632 } 1633 } 1634 } 1635 func (s *testEvaluatorSuite) TestYearWeek(c *C) { 1636 sc := s.ctx.GetStochastikVars().StmtCtx 1637 sc.IgnoreZeroInDate = true 1638 // Test cases from https://dev.allegrosql.com/doc/refman/5.7/en/date-and-time-functions.html#function_yearweek 1639 tests := []struct { 1640 t string 1641 mode int64 1642 expect int64 1643 }{ 1644 {"1987-01-01", 0, 198652}, 1645 {"2000-01-01", 0, 199952}, 1646 } 1647 fc := funcs[ast.YearWeek] 1648 for _, test := range tests { 1649 arg1 := types.NewStringCauset(test.t) 1650 arg2 := types.NewIntCauset(test.mode) 1651 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{arg1, arg2})) 1652 c.Assert(err, IsNil) 1653 result, err := evalBuiltinFunc(f, chunk.Event{}) 1654 c.Assert(err, IsNil) 1655 c.Assert(result.GetInt64(), Equals, test.expect) 1656 } 1657 1658 f, err := fc.getFunction(s.ctx, s.datumsToConstants(types.MakeCausets("2020-00-05"))) 1659 c.Assert(err, IsNil) 1660 result, err := evalBuiltinFunc(f, chunk.Event{}) 1661 c.Assert(err, IsNil) 1662 c.Assert(result.IsNull(), IsTrue) 1663 } 1664 1665 func (s *testEvaluatorSuite) TestTimestamFIDeliff(c *C) { 1666 tests := []struct { 1667 unit string 1668 t1 string 1669 t2 string 1670 expect int64 1671 }{ 1672 {"MONTH", "2003-02-01", "2003-05-01", 3}, 1673 {"YEAR", "2002-05-01", "2001-01-01", -1}, 1674 {"MINUTE", "2003-02-01", "2003-05-01 12:05:55", 128885}, 1675 } 1676 1677 fc := funcs[ast.TimestamFIDeliff] 1678 for _, test := range tests { 1679 args := []types.Causet{ 1680 types.NewStringCauset(test.unit), 1681 types.NewStringCauset(test.t1), 1682 types.NewStringCauset(test.t2), 1683 } 1684 resetStmtContext(s.ctx) 1685 f, err := fc.getFunction(s.ctx, s.datumsToConstants(args)) 1686 c.Assert(err, IsNil) 1687 d, err := evalBuiltinFunc(f, chunk.Event{}) 1688 c.Assert(err, IsNil) 1689 c.Assert(d.GetInt64(), Equals, test.expect) 1690 } 1691 sc := s.ctx.GetStochastikVars().StmtCtx 1692 sc.IgnoreTruncate = true 1693 sc.IgnoreZeroInDate = true 1694 resetStmtContext(s.ctx) 1695 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{types.NewStringCauset("DAY"), 1696 types.NewStringCauset("2020-01-00"), 1697 types.NewStringCauset("2020-01-01")})) 1698 c.Assert(err, IsNil) 1699 d, err := evalBuiltinFunc(f, chunk.Event{}) 1700 c.Assert(err, IsNil) 1701 c.Assert(d.HoTT(), Equals, types.HoTTNull) 1702 1703 resetStmtContext(s.ctx) 1704 f, err = fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{types.NewStringCauset("DAY"), 1705 {}, types.NewStringCauset("2020-01-01")})) 1706 c.Assert(err, IsNil) 1707 d, err = evalBuiltinFunc(f, chunk.Event{}) 1708 c.Assert(err, IsNil) 1709 c.Assert(d.IsNull(), IsTrue) 1710 } 1711 1712 func (s *testEvaluatorSuite) TestUnixTimestamp(c *C) { 1713 // Test UNIX_TIMESTAMP(). 1714 fc := funcs[ast.UnixTimestamp] 1715 f, err := fc.getFunction(s.ctx, nil) 1716 c.Assert(err, IsNil) 1717 resetStmtContext(s.ctx) 1718 d, err := evalBuiltinFunc(f, chunk.Event{}) 1719 c.Assert(err, IsNil) 1720 c.Assert(d.GetInt64()-time.Now().Unix(), GreaterEqual, int64(-1)) 1721 c.Assert(d.GetInt64()-time.Now().Unix(), LessEqual, int64(1)) 1722 1723 // https://github.com/whtcorpsinc/milevadb/issues/2496 1724 // Test UNIX_TIMESTAMP(NOW()). 1725 resetStmtContext(s.ctx) 1726 now, isNull, err := evalNowWithFsp(s.ctx, 0) 1727 c.Assert(err, IsNil) 1728 c.Assert(isNull, IsFalse) 1729 n := types.Causet{} 1730 n.SetMysqlTime(now) 1731 args := []types.Causet{n} 1732 f, err = fc.getFunction(s.ctx, s.datumsToConstants(args)) 1733 c.Assert(err, IsNil) 1734 resetStmtContext(s.ctx) 1735 d, err = evalBuiltinFunc(f, chunk.Event{}) 1736 c.Assert(err, IsNil) 1737 val, _ := d.GetMysqlDecimal().ToInt() 1738 c.Assert(val-time.Now().Unix(), GreaterEqual, int64(-1)) 1739 c.Assert(val-time.Now().Unix(), LessEqual, int64(1)) 1740 1741 // https://github.com/whtcorpsinc/milevadb/issues/2852 1742 // Test UNIX_TIMESTAMP(NULL). 1743 args = []types.Causet{types.NewCauset(nil)} 1744 resetStmtContext(s.ctx) 1745 f, err = fc.getFunction(s.ctx, s.datumsToConstants(args)) 1746 c.Assert(err, IsNil) 1747 d, err = evalBuiltinFunc(f, chunk.Event{}) 1748 c.Assert(err, IsNil) 1749 c.Assert(d.IsNull(), Equals, true) 1750 1751 // Set the time_zone variable, because UnixTimestamp() result depends on it. 1752 s.ctx.GetStochastikVars().TimeZone = time.UTC 1753 s.ctx.GetStochastikVars().StmtCtx.IgnoreZeroInDate = true 1754 tests := []struct { 1755 inputDecimal int 1756 input types.Causet 1757 expectHoTT byte 1758 expect string 1759 }{ 1760 {0, types.NewIntCauset(151113), types.HoTTInt64, "1447372800"}, // YYMMDD 1761 // TODO: Uncomment the line below after fixing #4232 1762 // {5, types.NewFloat64Causet(151113.12345), types.HoTTMysqlDecimal, "1447372800.00000"}, // YYMMDD 1763 {0, types.NewIntCauset(20151113), types.HoTTInt64, "1447372800"}, // YYYYMMDD 1764 // TODO: Uncomment the line below after fixing #4232 1765 // {5, types.NewFloat64Causet(20151113.12345), types.HoTTMysqlDecimal, "1447372800.00000"}, // YYYYMMDD 1766 {0, types.NewIntCauset(151113102019), types.HoTTInt64, "1447410019"}, // YYMMDDHHMMSS 1767 {0, types.NewFloat64Causet(151113102019), types.HoTTInt64, "1447410019"}, // YYMMDDHHMMSS 1768 {2, types.NewFloat64Causet(151113102019.12), types.HoTTMysqlDecimal, "1447410019.12"}, // YYMMDDHHMMSS 1769 {0, types.NewDecimalCauset(types.NewDecFromStringForTest("151113102019")), types.HoTTInt64, "1447410019"}, // YYMMDDHHMMSS 1770 {2, types.NewDecimalCauset(types.NewDecFromStringForTest("151113102019.12")), types.HoTTMysqlDecimal, "1447410019.12"}, // YYMMDDHHMMSS 1771 {7, types.NewDecimalCauset(types.NewDecFromStringForTest("151113102019.1234567")), types.HoTTMysqlDecimal, "1447410019.123457"}, // YYMMDDHHMMSS 1772 {0, types.NewIntCauset(20151113102019), types.HoTTInt64, "1447410019"}, // YYYYMMDDHHMMSS 1773 {0, types.NewStringCauset("2020-11-13 10:20:19"), types.HoTTInt64, "1447410019"}, 1774 {0, types.NewStringCauset("2020-11-13 10:20:19.012"), types.HoTTMysqlDecimal, "1447410019.012"}, 1775 {0, types.NewStringCauset("1970-01-01 00:00:00"), types.HoTTInt64, "0"}, // Min timestamp 1776 {0, types.NewStringCauset("2038-01-19 03:14:07.999999"), types.HoTTMysqlDecimal, "2147483647.999999"}, // Max timestamp 1777 {0, types.NewStringCauset("2020-00-02"), types.HoTTInt64, "0"}, // Invalid date 1778 {0, types.NewStringCauset("1969-12-31 23:59:59.999999"), types.HoTTMysqlDecimal, "0"}, // Invalid timestamp 1779 {0, types.NewStringCauset("2038-01-19 03:14:08"), types.HoTTInt64, "0"}, // Invalid timestamp 1780 // Below tests irregular inputs. 1781 //{0, types.NewIntCauset(0), types.HoTTInt64, "0"}, 1782 //{0, types.NewIntCauset(-1), types.HoTTInt64, "0"}, 1783 //{0, types.NewIntCauset(12345), types.HoTTInt64, "0"}, 1784 } 1785 1786 for _, test := range tests { 1787 expr := s.datumsToConstants([]types.Causet{test.input}) 1788 expr[0].GetType().Decimal = test.inputDecimal 1789 resetStmtContext(s.ctx) 1790 f, err := fc.getFunction(s.ctx, expr) 1791 c.Assert(err, IsNil, Commentf("%+v", test)) 1792 d, err := evalBuiltinFunc(f, chunk.Event{}) 1793 c.Assert(err, IsNil, Commentf("%+v", test)) 1794 c.Assert(d.HoTT(), Equals, test.expectHoTT, Commentf("%+v", test)) 1795 str, err := d.ToString() 1796 c.Assert(err, IsNil, Commentf("%+v", test)) 1797 c.Assert(str, Equals, test.expect, Commentf("%+v", test)) 1798 } 1799 } 1800 1801 func (s *testEvaluatorSuite) TestDateArithFuncs(c *C) { 1802 date := []string{"2020-12-31", "2020-01-01"} 1803 fcAdd := funcs[ast.DateAdd] 1804 fcSub := funcs[ast.DateSub] 1805 1806 tests := []struct { 1807 inputDate string 1808 fc functionClass 1809 inputDecimal float64 1810 expect string 1811 }{ 1812 {date[0], fcAdd, 1, date[1]}, 1813 {date[1], fcAdd, -1, date[0]}, 1814 {date[1], fcAdd, -0.5, date[0]}, 1815 {date[1], fcAdd, -1.4, date[0]}, 1816 1817 {date[1], fcSub, 1, date[0]}, 1818 {date[0], fcSub, -1, date[1]}, 1819 {date[0], fcSub, -0.5, date[1]}, 1820 {date[0], fcSub, -1.4, date[1]}, 1821 } 1822 for _, test := range tests { 1823 args := types.MakeCausets(test.inputDate, test.inputDecimal, "DAY") 1824 f, err := test.fc.getFunction(s.ctx, s.datumsToConstants(args)) 1825 c.Assert(err, IsNil) 1826 c.Assert(f, NotNil) 1827 v, err := evalBuiltinFunc(f, chunk.Event{}) 1828 c.Assert(err, IsNil) 1829 c.Assert(v.GetMysqlTime().String(), Equals, test.expect) 1830 } 1831 1832 args := types.MakeCausets(date[0], nil, "DAY") 1833 f, err := fcAdd.getFunction(s.ctx, s.datumsToConstants(args)) 1834 c.Assert(err, IsNil) 1835 c.Assert(f, NotNil) 1836 v, err := evalBuiltinFunc(f, chunk.Event{}) 1837 c.Assert(err, IsNil) 1838 c.Assert(v.IsNull(), IsTrue) 1839 1840 args = types.MakeCausets(date[1], nil, "DAY") 1841 f, err = fcSub.getFunction(s.ctx, s.datumsToConstants(args)) 1842 c.Assert(err, IsNil) 1843 c.Assert(f, NotNil) 1844 v, err = evalBuiltinFunc(f, chunk.Event{}) 1845 c.Assert(err, IsNil) 1846 c.Assert(v.IsNull(), IsTrue) 1847 1848 testMonths := []struct { 1849 input string 1850 months int 1851 expected string 1852 }{ 1853 {"1900-01-31", 1, "1900-02-28"}, 1854 {"2000-01-31", 1, "2000-02-29"}, 1855 {"2020-01-31", 1, "2020-02-29"}, 1856 {"2020-07-31", 1, "2020-08-31"}, 1857 {"2020-08-31", 1, "2020-09-30"}, 1858 {"2020-07-31", 2, "2020-09-30"}, 1859 {"2020-01-31", 27, "2020-04-30"}, 1860 {"2000-02-29", 12, "2001-02-28"}, 1861 {"2000-11-30", 1, "2000-12-30"}, 1862 } 1863 1864 for _, test := range testMonths { 1865 args = types.MakeCausets(test.input, test.months, "MONTH") 1866 f, err = fcAdd.getFunction(s.ctx, s.datumsToConstants(args)) 1867 c.Assert(err, IsNil) 1868 c.Assert(f, NotNil) 1869 v, err = evalBuiltinFunc(f, chunk.Event{}) 1870 c.Assert(err, IsNil) 1871 c.Assert(v.GetMysqlTime().String(), Equals, test.expected) 1872 } 1873 1874 testYears := []struct { 1875 input string 1876 year int 1877 expected string 1878 }{ 1879 {"1899-02-28", 1, "1900-02-28"}, 1880 {"1901-02-28", -1, "1900-02-28"}, 1881 {"2000-02-29", 1, "2001-02-28"}, 1882 {"2001-02-28", -1, "2000-02-28"}, 1883 {"2004-02-29", 1, "2005-02-28"}, 1884 {"2005-02-28", -1, "2004-02-28"}, 1885 } 1886 1887 for _, test := range testYears { 1888 args = types.MakeCausets(test.input, test.year, "YEAR") 1889 f, err = fcAdd.getFunction(s.ctx, s.datumsToConstants(args)) 1890 c.Assert(err, IsNil) 1891 c.Assert(f, NotNil) 1892 v, err = evalBuiltinFunc(f, chunk.Event{}) 1893 c.Assert(err, IsNil) 1894 c.Assert(v.GetMysqlTime().String(), Equals, test.expected) 1895 } 1896 1897 testOverflowYears := []struct { 1898 input string 1899 year int 1900 }{ 1901 {"2008-11-23", -1465647104}, 1902 {"2008-11-23", 1465647104}, 1903 } 1904 1905 for _, test := range testOverflowYears { 1906 args = types.MakeCausets(test.input, test.year, "YEAR") 1907 f, err = fcAdd.getFunction(s.ctx, s.datumsToConstants(args)) 1908 c.Assert(err, IsNil) 1909 c.Assert(f, NotNil) 1910 v, err = evalBuiltinFunc(f, chunk.Event{}) 1911 c.Assert(err, IsNil) 1912 c.Assert(v.IsNull(), IsTrue) 1913 } 1914 1915 for _, test := range testOverflowYears { 1916 args = types.MakeCausets(test.input, test.year, "YEAR") 1917 f, err = fcSub.getFunction(s.ctx, s.datumsToConstants(args)) 1918 c.Assert(err, IsNil) 1919 c.Assert(f, NotNil) 1920 v, err = evalBuiltinFunc(f, chunk.Event{}) 1921 c.Assert(err, IsNil) 1922 c.Assert(v.IsNull(), IsTrue) 1923 } 1924 1925 testDurations := []struct { 1926 fc functionClass 1927 dur string 1928 fsp int8 1929 unit string 1930 format interface{} 1931 expected string 1932 }{ 1933 { 1934 fc: fcAdd, 1935 dur: "00:00:00", 1936 fsp: 0, 1937 unit: "MICROSECOND", 1938 format: "100", 1939 expected: "00:00:00.000100", 1940 }, 1941 { 1942 fc: fcAdd, 1943 dur: "00:00:00", 1944 fsp: 0, 1945 unit: "MICROSECOND", 1946 format: 100.0, 1947 expected: "00:00:00.000100", 1948 }, 1949 { 1950 fc: fcSub, 1951 dur: "00:00:01", 1952 fsp: 0, 1953 unit: "MICROSECOND", 1954 format: "100", 1955 expected: "00:00:00.999900", 1956 }, 1957 { 1958 fc: fcAdd, 1959 dur: "00:00:00", 1960 fsp: 0, 1961 unit: "DAY", 1962 format: "1", 1963 expected: "24:00:00", 1964 }, 1965 { 1966 fc: fcAdd, 1967 dur: "00:00:00", 1968 fsp: 0, 1969 unit: "SECOND", 1970 format: 1, 1971 expected: "00:00:01", 1972 }, 1973 { 1974 fc: fcAdd, 1975 dur: "00:00:00", 1976 fsp: 0, 1977 unit: "DAY", 1978 format: types.NewDecFromInt(1), 1979 expected: "24:00:00", 1980 }, 1981 { 1982 fc: fcAdd, 1983 dur: "00:00:00", 1984 fsp: 0, 1985 unit: "DAY", 1986 format: 1.0, 1987 expected: "24:00:00", 1988 }, 1989 { 1990 fc: fcSub, 1991 dur: "26:00:00", 1992 fsp: 0, 1993 unit: "DAY", 1994 format: "1", 1995 expected: "02:00:00", 1996 }, 1997 { 1998 fc: fcSub, 1999 dur: "26:00:00", 2000 fsp: 0, 2001 unit: "DAY", 2002 format: 1, 2003 expected: "02:00:00", 2004 }, 2005 { 2006 fc: fcSub, 2007 dur: "26:00:00", 2008 fsp: 0, 2009 unit: "SECOND", 2010 format: types.NewDecFromInt(1), 2011 expected: "25:59:59", 2012 }, 2013 { 2014 fc: fcSub, 2015 dur: "27:00:00", 2016 fsp: 0, 2017 unit: "DAY", 2018 format: 1.0, 2019 expected: "03:00:00", 2020 }, 2021 } 2022 for _, tt := range testDurations { 2023 dur, _, ok, err := types.StrToDuration(nil, tt.dur, tt.fsp) 2024 c.Assert(err, IsNil) 2025 c.Assert(ok, IsTrue) 2026 args = types.MakeCausets(dur, tt.format, tt.unit) 2027 f, err = tt.fc.getFunction(s.ctx, s.datumsToConstants(args)) 2028 c.Assert(err, IsNil) 2029 c.Assert(f, NotNil) 2030 v, err = evalBuiltinFunc(f, chunk.Event{}) 2031 c.Assert(err, IsNil) 2032 c.Assert(v.GetMysqlDuration().String(), Equals, tt.expected) 2033 } 2034 } 2035 2036 func (s *testEvaluatorSuite) TestTimestamp(c *C) { 2037 tests := []struct { 2038 t []types.Causet 2039 expect string 2040 }{ 2041 // one argument 2042 {[]types.Causet{types.NewStringCauset("2020-01-18")}, "2020-01-18 00:00:00"}, 2043 {[]types.Causet{types.NewStringCauset("20170118")}, "2020-01-18 00:00:00"}, 2044 {[]types.Causet{types.NewStringCauset("170118")}, "2020-01-18 00:00:00"}, 2045 {[]types.Causet{types.NewStringCauset("20170118123056")}, "2020-01-18 12:30:56"}, 2046 {[]types.Causet{types.NewStringCauset("2020-01-18 12:30:56")}, "2020-01-18 12:30:56"}, 2047 {[]types.Causet{types.NewIntCauset(170118)}, "2020-01-18 00:00:00"}, 2048 {[]types.Causet{types.NewFloat64Causet(20170118)}, "2020-01-18 00:00:00"}, 2049 {[]types.Causet{types.NewStringCauset("20170118123050.999")}, "2020-01-18 12:30:50.999"}, 2050 {[]types.Causet{types.NewStringCauset("20170118123050.1234567")}, "2020-01-18 12:30:50.123457"}, 2051 // TODO: Parse int should use ParseTimeFromNum, rather than convert int to string for parsing. 2052 // {[]types.Causet{types.NewIntCauset(11111111111)}, "2001-11-11 11:11:11"}, 2053 {[]types.Causet{types.NewStringCauset("11111111111")}, "2011-11-11 11:11:01"}, 2054 {[]types.Causet{types.NewFloat64Causet(20170118.999)}, "2020-01-18 00:00:00.000"}, 2055 2056 // two arguments 2057 {[]types.Causet{types.NewStringCauset("2020-01-18"), types.NewStringCauset("12:30:59")}, "2020-01-18 12:30:59"}, 2058 {[]types.Causet{types.NewStringCauset("2020-01-18"), types.NewStringCauset("12:30:59")}, "2020-01-18 12:30:59"}, 2059 {[]types.Causet{types.NewStringCauset("2020-01-18 01:01:01"), types.NewStringCauset("12:30:50")}, "2020-01-18 13:31:51"}, 2060 {[]types.Causet{types.NewStringCauset("2020-01-18 01:01:01"), types.NewStringCauset("838:59:59")}, "2020-02-22 00:01:00"}, 2061 2062 {[]types.Causet{types.NewDecimalCauset(types.NewDecFromStringForTest("20170118123950.123"))}, "2020-01-18 12:39:50.123"}, 2063 {[]types.Causet{types.NewDecimalCauset(types.NewDecFromStringForTest("20170118123950.999"))}, "2020-01-18 12:39:50.999"}, 2064 {[]types.Causet{types.NewDecimalCauset(types.NewDecFromStringForTest("20170118123950.999"))}, "2020-01-18 12:39:50.999"}, 2065 } 2066 fc := funcs[ast.Timestamp] 2067 for _, test := range tests { 2068 resetStmtContext(s.ctx) 2069 f, err := fc.getFunction(s.ctx, s.datumsToConstants(test.t)) 2070 c.Assert(err, IsNil) 2071 d, err := evalBuiltinFunc(f, chunk.Event{}) 2072 c.Assert(err, IsNil) 2073 result, _ := d.ToString() 2074 c.Assert(result, Equals, test.expect) 2075 } 2076 2077 nilCauset := types.NewCauset(nil) 2078 resetStmtContext(s.ctx) 2079 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{nilCauset})) 2080 c.Assert(err, IsNil) 2081 d, err := evalBuiltinFunc(f, chunk.Event{}) 2082 c.Assert(err, IsNil) 2083 c.Assert(d.HoTT(), Equals, types.HoTTNull) 2084 } 2085 2086 func (s *testEvaluatorSuite) TestMakeDate(c *C) { 2087 cases := []struct { 2088 args []interface{} 2089 expected string 2090 isNil bool 2091 getErr bool 2092 }{ 2093 {[]interface{}{71, 1}, "1971-01-01", false, false}, 2094 {[]interface{}{71.1, 1.89}, "1971-01-02", false, false}, 2095 {[]interface{}{99, 1}, "1999-01-01", false, false}, 2096 {[]interface{}{100, 1}, "0100-01-01", false, false}, 2097 {[]interface{}{69, 1}, "2069-01-01", false, false}, 2098 {[]interface{}{70, 1}, "1970-01-01", false, false}, 2099 {[]interface{}{1000, 1}, "1000-01-01", false, false}, 2100 {[]interface{}{-1, 3660}, "", true, false}, 2101 {[]interface{}{10000, 3660}, "", true, false}, 2102 {[]interface{}{2060, 2900025}, "9999-12-31", false, false}, 2103 {[]interface{}{2060, 2900026}, "", true, false}, 2104 {[]interface{}{"71", 1}, "1971-01-01", false, false}, 2105 {[]interface{}{71, "1"}, "1971-01-01", false, false}, 2106 {[]interface{}{"71", "1"}, "1971-01-01", false, false}, 2107 {[]interface{}{nil, 2900025}, "", true, false}, 2108 {[]interface{}{2060, nil}, "", true, false}, 2109 {[]interface{}{nil, nil}, "", true, false}, 2110 {[]interface{}{errors.New("must error"), errors.New("must error")}, "", false, true}, 2111 } 2112 2113 for _, t := range cases { 2114 f, err := newFunctionForTest(s.ctx, ast.MakeDate, s.primitiveValsToConstants(t.args)...) 2115 c.Assert(err, IsNil) 2116 tp := f.GetType() 2117 c.Assert(tp.Tp, Equals, allegrosql.TypeDate) 2118 c.Assert(tp.Charset, Equals, charset.CharsetBin) 2119 c.Assert(tp.DefCauslate, Equals, charset.DefCauslationBin) 2120 c.Assert(tp.Flag, Equals, allegrosql.BinaryFlag) 2121 c.Assert(tp.Flen, Equals, allegrosql.MaxDateWidth) 2122 d, err := f.Eval(chunk.Event{}) 2123 if t.getErr { 2124 c.Assert(err, NotNil) 2125 } else { 2126 c.Assert(err, IsNil) 2127 if t.isNil { 2128 c.Assert(d.HoTT(), Equals, types.HoTTNull) 2129 } else { 2130 c.Assert(d.GetMysqlTime().String(), Equals, t.expected) 2131 } 2132 } 2133 } 2134 2135 _, err := funcs[ast.MakeDate].getFunction(s.ctx, []Expression{NewZero(), NewZero()}) 2136 c.Assert(err, IsNil) 2137 } 2138 2139 func (s *testEvaluatorSuite) TestMakeTime(c *C) { 2140 tbl := []struct { 2141 Args []interface{} 2142 Want interface{} 2143 }{ 2144 {[]interface{}{12, 15, 30}, "12:15:30"}, 2145 {[]interface{}{25, 15, 30}, "25:15:30"}, 2146 {[]interface{}{-25, 15, 30}, "-25:15:30"}, 2147 {[]interface{}{12, -15, 30}, nil}, 2148 {[]interface{}{12, 15, -30}, nil}, 2149 2150 {[]interface{}{12, 15, "30.10"}, "12:15:30.100000"}, 2151 {[]interface{}{12, 15, "30.20"}, "12:15:30.200000"}, 2152 {[]interface{}{12, 15, 30.3000001}, "12:15:30.300000"}, 2153 {[]interface{}{12, 15, 30.0000005}, "12:15:30.000001"}, 2154 {[]interface{}{"12", "15", 30.1}, "12:15:30.100000"}, 2155 2156 {[]interface{}{0, 58.4, 0}, "00:58:00"}, 2157 {[]interface{}{0, "58.4", 0}, "00:58:00"}, 2158 {[]interface{}{0, 58.5, 1}, "00:59:01"}, 2159 {[]interface{}{0, "58.5", 1}, "00:58:01"}, 2160 {[]interface{}{0, 59.5, 1}, nil}, 2161 {[]interface{}{0, "59.5", 1}, "00:59:01"}, 2162 {[]interface{}{0, 1, 59.1}, "00:01:59.100000"}, 2163 {[]interface{}{0, 1, "59.1"}, "00:01:59.100000"}, 2164 {[]interface{}{0, 1, 59.5}, "00:01:59.500000"}, 2165 {[]interface{}{0, 1, "59.5"}, "00:01:59.500000"}, 2166 {[]interface{}{23.5, 1, 10}, "24:01:10"}, 2167 {[]interface{}{"23.5", 1, 10}, "23:01:10"}, 2168 2169 {[]interface{}{0, 0, 0}, "00:00:00"}, 2170 2171 {[]interface{}{837, 59, 59.1}, "837:59:59.100000"}, 2172 {[]interface{}{838, 59, 59.1}, "838:59:59.000000"}, 2173 {[]interface{}{-838, 59, 59.1}, "-838:59:59.000000"}, 2174 {[]interface{}{1000, 1, 1}, "838:59:59"}, 2175 {[]interface{}{-1000, 1, 1.23}, "-838:59:59.000000"}, 2176 {[]interface{}{1000, 59.1, 1}, "838:59:59"}, 2177 {[]interface{}{1000, 59.5, 1}, nil}, 2178 {[]interface{}{1000, 1, 59.1}, "838:59:59.000000"}, 2179 {[]interface{}{1000, 1, 59.5}, "838:59:59.000000"}, 2180 2181 {[]interface{}{12, 15, 60}, nil}, 2182 {[]interface{}{12, 15, "60"}, nil}, 2183 {[]interface{}{12, 60, 0}, nil}, 2184 {[]interface{}{12, "60", 0}, nil}, 2185 2186 {[]interface{}{12, 15, nil}, nil}, 2187 {[]interface{}{12, nil, 0}, nil}, 2188 {[]interface{}{nil, 15, 0}, nil}, 2189 {[]interface{}{nil, nil, nil}, nil}, 2190 } 2191 2192 Dtbl := tblToDtbl(tbl) 2193 maketime := funcs[ast.MakeTime] 2194 for idx, t := range Dtbl { 2195 f, err := maketime.getFunction(s.ctx, s.datumsToConstants(t["Args"])) 2196 c.Assert(err, IsNil) 2197 got, err := evalBuiltinFunc(f, chunk.Event{}) 2198 c.Assert(err, IsNil) 2199 if t["Want"][0].HoTT() == types.HoTTNull { 2200 c.Assert(got.HoTT(), Equals, types.HoTTNull, Commentf("[%v] - args:%v", idx, t["Args"])) 2201 } else { 2202 want, err := t["Want"][0].ToString() 2203 c.Assert(err, IsNil) 2204 c.Assert(got.GetMysqlDuration().String(), Equals, want, Commentf("[%v] - args:%v", idx, t["Args"])) 2205 } 2206 } 2207 2208 // MAKETIME(CAST(-1 AS UNSIGNED),0,0); 2209 tp1 := &types.FieldType{ 2210 Tp: allegrosql.TypeLonglong, 2211 Flag: allegrosql.UnsignedFlag, 2212 Charset: charset.CharsetBin, 2213 DefCauslate: charset.DefCauslationBin, 2214 Flen: allegrosql.MaxIntWidth, 2215 } 2216 f := BuildCastFunction(s.ctx, &Constant{Value: types.NewCauset("-1"), RetType: types.NewFieldType(allegrosql.TypeString)}, tp1) 2217 res, err := f.Eval(chunk.Event{}) 2218 c.Assert(err, IsNil) 2219 f1, err := maketime.getFunction(s.ctx, s.datumsToConstants([]types.Causet{res, makeCausets(0)[0], makeCausets(0)[0]})) 2220 c.Assert(err, IsNil) 2221 got, err := evalBuiltinFunc(f1, chunk.Event{}) 2222 c.Assert(err, IsNil) 2223 c.Assert(got.GetMysqlDuration().String(), Equals, "838:59:59") 2224 2225 tbl = []struct { 2226 Args []interface{} 2227 Want interface{} 2228 }{ 2229 {[]interface{}{"", "", ""}, "00:00:00.000000"}, 2230 {[]interface{}{"h", "m", "s"}, "00:00:00.000000"}, 2231 } 2232 Dtbl = tblToDtbl(tbl) 2233 maketime = funcs[ast.MakeTime] 2234 for idx, t := range Dtbl { 2235 f, err := maketime.getFunction(s.ctx, s.datumsToConstants(t["Args"])) 2236 c.Assert(err, IsNil) 2237 got, err := evalBuiltinFunc(f, chunk.Event{}) 2238 c.Assert(err, IsNil) 2239 want, err := t["Want"][0].ToString() 2240 c.Assert(err, IsNil) 2241 c.Assert(got.GetMysqlDuration().String(), Equals, want, Commentf("[%v] - args:%v", idx, t["Args"])) 2242 } 2243 } 2244 2245 func (s *testEvaluatorSuite) TestQuarter(c *C) { 2246 sc := s.ctx.GetStochastikVars().StmtCtx 2247 sc.IgnoreZeroInDate = true 2248 tests := []struct { 2249 t string 2250 expect int64 2251 }{ 2252 // Test case from https://dev.allegrosql.com/doc/refman/5.7/en/date-and-time-functions.html#function_quarter 2253 {"2008-04-01", 2}, 2254 // Test case for boundary values 2255 {"2008-01-01", 1}, 2256 {"2008-03-31", 1}, 2257 {"2008-06-30", 2}, 2258 {"2008-07-01", 3}, 2259 {"2008-09-30", 3}, 2260 {"2008-10-01", 4}, 2261 {"2008-12-31", 4}, 2262 // Test case for month 0 2263 {"2008-00-01", 0}, 2264 } 2265 fc := funcs["quarter"] 2266 for _, test := range tests { 2267 arg := types.NewStringCauset(test.t) 2268 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{arg})) 2269 c.Assert(err, IsNil) 2270 c.Assert(f, NotNil) 2271 result, err := evalBuiltinFunc(f, chunk.Event{}) 2272 c.Assert(err, IsNil) 2273 c.Assert(result.GetInt64(), Equals, test.expect) 2274 } 2275 2276 // test invalid input 2277 argInvalid := types.NewStringCauset("2008-13-01") 2278 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{argInvalid})) 2279 c.Assert(err, IsNil) 2280 result, err := evalBuiltinFunc(f, chunk.Event{}) 2281 c.Assert(err, IsNil) 2282 c.Assert(result.IsNull(), IsTrue) 2283 } 2284 2285 func (s *testEvaluatorSuite) TestGetFormat(c *C) { 2286 tests := []struct { 2287 unit string 2288 location string 2289 expect string 2290 }{ 2291 {"DATE", "USA", `%m.%d.%Y`}, 2292 {"DATE", "JIS", `%Y-%m-%d`}, 2293 {"DATE", "ISO", `%Y-%m-%d`}, 2294 {"DATE", "EUR", `%d.%m.%Y`}, 2295 {"DATE", "INTERNAL", `%Y%m%d`}, 2296 2297 {"DATETIME", "USA", `%Y-%m-%d %H.%i.%s`}, 2298 {"DATETIME", "JIS", `%Y-%m-%d %H:%i:%s`}, 2299 {"DATETIME", "ISO", `%Y-%m-%d %H:%i:%s`}, 2300 {"DATETIME", "EUR", `%Y-%m-%d %H.%i.%s`}, 2301 {"DATETIME", "INTERNAL", `%Y%m%d%H%i%s`}, 2302 2303 {"TIME", "USA", `%h:%i:%s %p`}, 2304 {"TIME", "JIS", `%H:%i:%s`}, 2305 {"TIME", "ISO", `%H:%i:%s`}, 2306 {"TIME", "EUR", `%H.%i.%s`}, 2307 {"TIME", "INTERNAL", `%H%i%s`}, 2308 } 2309 2310 fc := funcs[ast.GetFormat] 2311 for _, test := range tests { 2312 t := []types.Causet{types.NewStringCauset(test.unit), types.NewStringCauset(test.location)} 2313 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2314 c.Assert(err, IsNil) 2315 d, err := evalBuiltinFunc(f, chunk.Event{}) 2316 c.Assert(err, IsNil) 2317 result, _ := d.ToString() 2318 c.Assert(result, Equals, test.expect) 2319 } 2320 } 2321 2322 func (s *testEvaluatorSuite) TestToSeconds(c *C) { 2323 sc := s.ctx.GetStochastikVars().StmtCtx 2324 sc.IgnoreZeroInDate = true 2325 tests := []struct { 2326 param interface{} 2327 expect int64 2328 }{ 2329 {950501, 62966505600}, 2330 {"2009-11-29", 63426672000}, 2331 {"2009-11-29 13:43:32", 63426721412}, 2332 {"09-11-29 13:43:32", 63426721412}, 2333 {"99-11-29 13:43:32", 63111102212}, 2334 } 2335 2336 fc := funcs[ast.ToSeconds] 2337 for _, test := range tests { 2338 t := []types.Causet{types.NewCauset(test.param)} 2339 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2340 c.Assert(err, IsNil) 2341 d, err := evalBuiltinFunc(f, chunk.Event{}) 2342 c.Assert(err, IsNil) 2343 c.Assert(d.GetInt64(), Equals, test.expect) 2344 } 2345 2346 testsNull := []interface{}{ 2347 "0000-00-00", 2348 "1992-13-00", 2349 "2007-10-07 23:59:61", 2350 123456789} 2351 2352 for _, i := range testsNull { 2353 t := []types.Causet{types.NewCauset(i)} 2354 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2355 c.Assert(err, IsNil) 2356 d, err := evalBuiltinFunc(f, chunk.Event{}) 2357 c.Assert(err, IsNil) 2358 c.Assert(d.IsNull(), IsTrue) 2359 } 2360 } 2361 2362 func (s *testEvaluatorSuite) TestToDays(c *C) { 2363 sc := s.ctx.GetStochastikVars().StmtCtx 2364 sc.IgnoreZeroInDate = true 2365 tests := []struct { 2366 param interface{} 2367 expect int64 2368 }{ 2369 {950501, 728779}, 2370 {"2007-10-07", 733321}, 2371 {"2008-10-07", 733687}, 2372 {"08-10-07", 733687}, 2373 {"0000-01-01", 1}, 2374 {"2007-10-07 00:00:59", 733321}, 2375 } 2376 2377 fc := funcs[ast.ToDays] 2378 for _, test := range tests { 2379 t := []types.Causet{types.NewCauset(test.param)} 2380 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2381 c.Assert(err, IsNil) 2382 d, err := evalBuiltinFunc(f, chunk.Event{}) 2383 c.Assert(err, IsNil) 2384 c.Assert(d.GetInt64(), Equals, test.expect) 2385 } 2386 2387 testsNull := []interface{}{ 2388 "0000-00-00", 2389 "1992-13-00", 2390 "2007-10-07 23:59:61", 2391 123456789} 2392 2393 for _, i := range testsNull { 2394 t := []types.Causet{types.NewCauset(i)} 2395 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2396 c.Assert(err, IsNil) 2397 d, err := evalBuiltinFunc(f, chunk.Event{}) 2398 c.Assert(err, IsNil) 2399 c.Assert(d.IsNull(), IsTrue) 2400 } 2401 } 2402 2403 func (s *testEvaluatorSuite) TestTimestampAdd(c *C) { 2404 tests := []struct { 2405 unit string 2406 interval int64 2407 date interface{} 2408 expect string 2409 }{ 2410 {"MINUTE", 1, "2003-01-02", "2003-01-02 00:01:00"}, 2411 {"WEEK", 1, "2003-01-02 23:59:59", "2003-01-09 23:59:59"}, 2412 {"MICROSECOND", 1, 950501, "1995-05-01 00:00:00.000001"}, 2413 } 2414 2415 fc := funcs[ast.TimestampAdd] 2416 for _, test := range tests { 2417 t := []types.Causet{types.NewStringCauset(test.unit), types.NewIntCauset(test.interval), types.NewCauset(test.date)} 2418 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2419 c.Assert(err, IsNil) 2420 d, err := evalBuiltinFunc(f, chunk.Event{}) 2421 c.Assert(err, IsNil) 2422 result, _ := d.ToString() 2423 c.Assert(result, Equals, test.expect) 2424 } 2425 } 2426 2427 func (s *testEvaluatorSuite) TestPeriodAdd(c *C) { 2428 tests := []struct { 2429 Period int64 2430 Months int64 2431 Success bool 2432 Expect int64 2433 }{ 2434 {201611, 2, true, 201701}, 2435 {201611, 3, true, 201702}, 2436 {201611, -13, true, 201510}, 2437 {1611, 3, true, 201702}, 2438 {7011, 3, true, 197102}, 2439 {12323, 10, false, 0}, 2440 {0, 3, false, 0}, 2441 } 2442 2443 fc := funcs[ast.PeriodAdd] 2444 for _, test := range tests { 2445 period := types.NewIntCauset(test.Period) 2446 months := types.NewIntCauset(test.Months) 2447 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{period, months})) 2448 c.Assert(err, IsNil) 2449 c.Assert(f, NotNil) 2450 result, err := evalBuiltinFunc(f, chunk.Event{}) 2451 if !test.Success { 2452 c.Assert(result.IsNull(), IsTrue) 2453 continue 2454 } 2455 c.Assert(err, IsNil) 2456 c.Assert(result.HoTT(), Equals, types.HoTTInt64) 2457 value := result.GetInt64() 2458 c.Assert(value, Equals, test.Expect) 2459 } 2460 } 2461 2462 func (s *testEvaluatorSuite) TestTimeFormat(c *C) { 2463 // SELECT TIME_FORMAT(null,'%H %k %h %I %l') 2464 args := []types.Causet{types.NewCauset(nil), types.NewStringCauset(`%H %k %h %I %l`)} 2465 fc := funcs[ast.TimeFormat] 2466 f, err := fc.getFunction(s.ctx, s.datumsToConstants(args)) 2467 c.Assert(err, IsNil) 2468 v, err := evalBuiltinFunc(f, chunk.Event{}) 2469 c.Assert(err, IsNil) 2470 c.Assert(v.IsNull(), Equals, true) 2471 2472 tblDate := []struct { 2473 Input []string 2474 Expect interface{} 2475 }{ 2476 {[]string{"23:00:00", `%H %k %h %I %l`}, 2477 "23 23 11 11 11"}, 2478 {[]string{"11:00:00", `%H %k %h %I %l`}, 2479 "11 11 11 11 11"}, 2480 {[]string{"17:42:03.000001", `%r %T %h:%i%p %h:%i:%s %p %H %i %s`}, 2481 "05:42:03 PM 17:42:03 05:42PM 05:42:03 PM 17 42 03"}, 2482 {[]string{"07:42:03.000001", `%f`}, 2483 "000001"}, 2484 {[]string{"1990-05-07 19:30:10", `%H %i %s`}, 2485 "19 30 10"}, 2486 } 2487 dtblDate := tblToDtbl(tblDate) 2488 for i, t := range dtblDate { 2489 fc := funcs[ast.TimeFormat] 2490 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t["Input"])) 2491 c.Assert(err, IsNil) 2492 v, err := evalBuiltinFunc(f, chunk.Event{}) 2493 c.Assert(err, IsNil) 2494 c.Assert(v, solitonutil.CausetEquals, t["Expect"][0], Commentf(`no.%d \nobtain:%v \nexpect:%v\n`, i, 2495 v.GetValue(), t["Expect"][0].GetValue())) 2496 } 2497 } 2498 2499 func (s *testEvaluatorSuite) TestTimeToSec(c *C) { 2500 fc := funcs[ast.TimeToSec] 2501 2502 // test nil 2503 nilCauset := types.NewCauset(nil) 2504 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{nilCauset})) 2505 c.Assert(err, IsNil) 2506 d, err := evalBuiltinFunc(f, chunk.Event{}) 2507 c.Assert(err, IsNil) 2508 c.Assert(d.HoTT(), Equals, types.HoTTNull) 2509 2510 // TODO: Some test cases are commented out due to #4340, #4341. 2511 tests := []struct { 2512 input types.Causet 2513 expect int64 2514 }{ 2515 {types.NewStringCauset("22:23:00"), 80580}, 2516 {types.NewStringCauset("00:39:38"), 2378}, 2517 {types.NewStringCauset("23:00"), 82800}, 2518 {types.NewStringCauset("00:00"), 0}, 2519 {types.NewStringCauset("00:00:00"), 0}, 2520 {types.NewStringCauset("23:59:59"), 86399}, 2521 {types.NewStringCauset("1:0"), 3600}, 2522 {types.NewStringCauset("1:00"), 3600}, 2523 {types.NewStringCauset("1:0:0"), 3600}, 2524 {types.NewStringCauset("-02:00"), -7200}, 2525 {types.NewStringCauset("-02:00:05"), -7205}, 2526 {types.NewStringCauset("020005"), 7205}, 2527 // {types.NewStringCauset("20171222020005"), 7205}, 2528 // {types.NewIntCauset(020005), 7205}, 2529 // {types.NewIntCauset(20171222020005), 7205}, 2530 // {types.NewIntCauset(171222020005), 7205}, 2531 } 2532 for _, test := range tests { 2533 expr := s.datumsToConstants([]types.Causet{test.input}) 2534 f, err := fc.getFunction(s.ctx, expr) 2535 c.Assert(err, IsNil, Commentf("%+v", test)) 2536 result, err := evalBuiltinFunc(f, chunk.Event{}) 2537 c.Assert(err, IsNil, Commentf("%+v", test)) 2538 c.Assert(result.GetInt64(), Equals, test.expect, Commentf("%+v", test)) 2539 } 2540 } 2541 2542 func (s *testEvaluatorSuite) TestSecToTime(c *C) { 2543 stmtCtx := s.ctx.GetStochastikVars().StmtCtx 2544 origin := stmtCtx.IgnoreTruncate 2545 stmtCtx.IgnoreTruncate = true 2546 defer func() { 2547 stmtCtx.IgnoreTruncate = origin 2548 }() 2549 2550 fc := funcs[ast.SecToTime] 2551 2552 // test nil 2553 nilCauset := types.NewCauset(nil) 2554 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{nilCauset})) 2555 c.Assert(err, IsNil) 2556 d, err := evalBuiltinFunc(f, chunk.Event{}) 2557 c.Assert(err, IsNil) 2558 c.Assert(d.HoTT(), Equals, types.HoTTNull) 2559 2560 tests := []struct { 2561 inputDecimal int 2562 input types.Causet 2563 expect string 2564 }{ 2565 {0, types.NewIntCauset(2378), "00:39:38"}, 2566 {0, types.NewIntCauset(3864000), "838:59:59"}, 2567 {0, types.NewIntCauset(-3864000), "-838:59:59"}, 2568 {1, types.NewFloat64Causet(86401.4), "24:00:01.4"}, 2569 {1, types.NewFloat64Causet(-86401.4), "-24:00:01.4"}, 2570 {5, types.NewFloat64Causet(86401.54321), "24:00:01.54321"}, 2571 {-1, types.NewFloat64Causet(86401.54321), "24:00:01.543210"}, 2572 {0, types.NewStringCauset("123.4"), "00:02:03.400000"}, 2573 {0, types.NewStringCauset("123.4567891"), "00:02:03.456789"}, 2574 {0, types.NewStringCauset("123"), "00:02:03.000000"}, 2575 {0, types.NewStringCauset("abc"), "00:00:00.000000"}, 2576 } 2577 for _, test := range tests { 2578 expr := s.datumsToConstants([]types.Causet{test.input}) 2579 expr[0].GetType().Decimal = test.inputDecimal 2580 f, err := fc.getFunction(s.ctx, expr) 2581 c.Assert(err, IsNil, Commentf("%+v", test)) 2582 d, err := evalBuiltinFunc(f, chunk.Event{}) 2583 c.Assert(err, IsNil, Commentf("%+v", test)) 2584 result, _ := d.ToString() 2585 c.Assert(result, Equals, test.expect, Commentf("%+v", test)) 2586 } 2587 } 2588 2589 func (s *testEvaluatorSuite) TestConvertTz(c *C) { 2590 tests := []struct { 2591 t interface{} 2592 fromTz interface{} 2593 toTz interface{} 2594 Success bool 2595 expect string 2596 }{ 2597 {"2004-01-01 12:00:00.111", "-00:00", "+12:34", true, "2004-01-02 00:34:00.111"}, 2598 {"2004-01-01 12:00:00.11", "+00:00", "+12:34", true, "2004-01-02 00:34:00.11"}, 2599 {"2004-01-01 12:00:00.11111111111", "-00:00", "+12:34", true, "2004-01-02 00:34:00.111111"}, 2600 {"2004-01-01 12:00:00", "GMT", "MET", true, "2004-01-01 13:00:00"}, 2601 {"2004-01-01 12:00:00", "-01:00", "-12:00", true, "2004-01-01 01:00:00"}, 2602 {"2004-01-01 12:00:00", "-00:00", "+13:00", true, "2004-01-02 01:00:00"}, 2603 {"2004-01-01 12:00:00", "-00:00", "-13:00", true, ""}, 2604 {"2004-01-01 12:00:00", "-00:00", "-12:88", true, ""}, 2605 {"2004-01-01 12:00:00", "+10:82", "GMT", true, ""}, 2606 {"2004-01-01 12:00:00", "+00:00", "GMT", true, ""}, 2607 {"2004-01-01 12:00:00", "GMT", "+00:00", true, ""}, 2608 {20040101, "+00:00", "+10:32", true, "2004-01-01 10:32:00"}, 2609 {3.14159, "+00:00", "+10:32", true, ""}, 2610 {"2004-01-01 12:00:00", "", "GMT", true, ""}, 2611 {"2004-01-01 12:00:00", "GMT", "", true, ""}, 2612 {"2004-01-01 12:00:00", "a", "GMT", true, ""}, 2613 {"2004-01-01 12:00:00", "0", "GMT", true, ""}, 2614 {"2004-01-01 12:00:00", "GMT", "a", true, ""}, 2615 {"2004-01-01 12:00:00", "GMT", "0", true, ""}, 2616 {nil, "GMT", "+00:00", true, ""}, 2617 {"2004-01-01 12:00:00", nil, "+00:00", true, ""}, 2618 {"2004-01-01 12:00:00", "GMT", nil, true, ""}, 2619 } 2620 fc := funcs[ast.ConvertTz] 2621 for _, test := range tests { 2622 f, err := fc.getFunction(s.ctx, 2623 s.datumsToConstants( 2624 []types.Causet{ 2625 types.NewCauset(test.t), 2626 types.NewCauset(test.fromTz), 2627 types.NewCauset(test.toTz)})) 2628 c.Assert(err, IsNil) 2629 d, err := evalBuiltinFunc(f, chunk.Event{}) 2630 if test.Success { 2631 c.Assert(err, IsNil) 2632 } else { 2633 c.Assert(err, NotNil) 2634 } 2635 result, _ := d.ToString() 2636 c.Assert(result, Equals, test.expect, Commentf("convert_tz(\"%v\", \"%s\", \"%s\")", test.t, test.fromTz, test.toTz)) 2637 } 2638 } 2639 2640 func (s *testEvaluatorSuite) TestPeriodDiff(c *C) { 2641 tests := []struct { 2642 Period1 int64 2643 Period2 int64 2644 Success bool 2645 Expect int64 2646 }{ 2647 {201611, 201611, true, 0}, 2648 {200802, 200703, true, 11}, 2649 {201701, 201611, true, 2}, 2650 {201702, 201611, true, 3}, 2651 {201510, 201611, true, -13}, 2652 {201702, 1611, true, 3}, 2653 {197102, 7011, true, 3}, 2654 } 2655 2656 tests2 := []struct { 2657 Period1 int64 2658 Period2 int64 2659 }{ 2660 {0, 999999999}, 2661 {9999999, 0}, 2662 {411, 200413}, 2663 {197000, 207700}, 2664 {12509, 12323}, 2665 {12509, 12323}, 2666 } 2667 fc := funcs[ast.PeriodDiff] 2668 for _, test := range tests { 2669 period1 := types.NewIntCauset(test.Period1) 2670 period2 := types.NewIntCauset(test.Period2) 2671 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{period1, period2})) 2672 c.Assert(err, IsNil) 2673 c.Assert(f, NotNil) 2674 result, err := evalBuiltinFunc(f, chunk.Event{}) 2675 if !test.Success { 2676 c.Assert(result.IsNull(), IsTrue) 2677 continue 2678 } 2679 c.Assert(err, IsNil) 2680 c.Assert(result.HoTT(), Equals, types.HoTTInt64) 2681 value := result.GetInt64() 2682 c.Assert(value, Equals, test.Expect) 2683 } 2684 2685 for _, test := range tests2 { 2686 period1 := types.NewIntCauset(test.Period1) 2687 period2 := types.NewIntCauset(test.Period2) 2688 f, err := fc.getFunction(s.ctx, s.datumsToConstants([]types.Causet{period1, period2})) 2689 c.Assert(err, IsNil) 2690 c.Assert(f, NotNil) 2691 _, err = evalBuiltinFunc(f, chunk.Event{}) 2692 c.Assert(err, NotNil) 2693 c.Assert(err.Error(), Equals, "[memex:1210]Incorrect arguments to period_diff") 2694 } 2695 2696 // nil 2697 args := []types.Causet{types.NewCauset(nil), types.NewIntCauset(0)} 2698 f, err := fc.getFunction(s.ctx, s.datumsToConstants(args)) 2699 c.Assert(err, IsNil) 2700 v, err := evalBuiltinFunc(f, chunk.Event{}) 2701 c.Assert(err, IsNil) 2702 c.Assert(v.HoTT(), Equals, types.HoTTNull) 2703 2704 args = []types.Causet{types.NewIntCauset(0), types.NewCauset(nil)} 2705 f, err = fc.getFunction(s.ctx, s.datumsToConstants(args)) 2706 c.Assert(err, IsNil) 2707 v, err = evalBuiltinFunc(f, chunk.Event{}) 2708 c.Assert(err, IsNil) 2709 c.Assert(v.HoTT(), Equals, types.HoTTNull) 2710 } 2711 2712 func (s *testEvaluatorSuite) TestLastDay(c *C) { 2713 tests := []struct { 2714 param interface{} 2715 expect string 2716 }{ 2717 {"2003-02-05", "2003-02-28"}, 2718 {"2004-02-05", "2004-02-29"}, 2719 {"2004-01-01 01:01:01", "2004-01-31"}, 2720 {950501, "1995-05-31"}, 2721 } 2722 2723 fc := funcs[ast.LastDay] 2724 for _, test := range tests { 2725 t := []types.Causet{types.NewCauset(test.param)} 2726 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2727 c.Assert(err, IsNil) 2728 d, err := evalBuiltinFunc(f, chunk.Event{}) 2729 c.Assert(err, IsNil) 2730 result, _ := d.ToString() 2731 c.Assert(result, Equals, test.expect) 2732 } 2733 2734 testsNull := []interface{}{ 2735 "0000-00-00", 2736 "1992-13-00", 2737 "2007-10-07 23:59:61", 2738 "2005-00-00", 2739 "2005-00-01", 2740 "2243-01 00:00:00", 2741 123456789} 2742 2743 for _, i := range testsNull { 2744 t := []types.Causet{types.NewCauset(i)} 2745 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2746 c.Assert(err, IsNil) 2747 d, err := evalBuiltinFunc(f, chunk.Event{}) 2748 c.Assert(err, IsNil) 2749 c.Assert(d.IsNull(), IsTrue) 2750 } 2751 } 2752 2753 func (s *testEvaluatorSuite) TestWithTimeZone(c *C) { 2754 sv := s.ctx.GetStochastikVars() 2755 originTZ := sv.Location() 2756 sv.TimeZone, _ = time.LoadLocation("Asia/Tokyo") 2757 defer func() { 2758 sv.TimeZone = originTZ 2759 }() 2760 2761 timeToGoTime := func(d types.Causet, loc *time.Location) time.Time { 2762 result, _ := d.GetMysqlTime().GoTime(loc) 2763 return result 2764 } 2765 durationToGoTime := func(d types.Causet, loc *time.Location) time.Time { 2766 t, _ := d.GetMysqlDuration().ConvertToTime(sv.StmtCtx, allegrosql.TypeDatetime) 2767 result, _ := t.GoTime(sv.TimeZone) 2768 return result 2769 } 2770 2771 tests := []struct { 2772 method string 2773 Input []types.Causet 2774 convertToTime func(types.Causet, *time.Location) time.Time 2775 }{ 2776 {ast.Sysdate, makeCausets(2), timeToGoTime}, 2777 {ast.Sysdate, nil, timeToGoTime}, 2778 {ast.Curdate, nil, timeToGoTime}, 2779 {ast.CurrentTime, makeCausets(2), durationToGoTime}, 2780 {ast.CurrentTime, nil, durationToGoTime}, 2781 {ast.Curtime, nil, durationToGoTime}, 2782 } 2783 2784 for _, t := range tests { 2785 now := time.Now().In(sv.TimeZone) 2786 f, err := funcs[t.method].getFunction(s.ctx, s.datumsToConstants(t.Input)) 2787 c.Assert(err, IsNil) 2788 resetStmtContext(s.ctx) 2789 d, err := evalBuiltinFunc(f, chunk.Event{}) 2790 c.Assert(err, IsNil) 2791 result := t.convertToTime(d, sv.TimeZone) 2792 c.Assert(result.Sub(now), LessEqual, 2*time.Second) 2793 } 2794 } 2795 2796 func (s *testEvaluatorSuite) TestMilevaDBParseTso(c *C) { 2797 s.ctx.GetStochastikVars().TimeZone = time.UTC 2798 tests := []struct { 2799 param interface{} 2800 expect string 2801 }{ 2802 {404411537129996288, "2020-11-20 09:53:04.877000"}, 2803 {"404411537129996288", "2020-11-20 09:53:04.877000"}, 2804 {1, "1970-01-01 00:00:00.000000"}, 2805 } 2806 2807 fc := funcs[ast.MilevaDBParseTso] 2808 for _, test := range tests { 2809 t := []types.Causet{types.NewCauset(test.param)} 2810 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2811 c.Assert(err, IsNil) 2812 d, err := evalBuiltinFunc(f, chunk.Event{}) 2813 c.Assert(err, IsNil) 2814 result, _ := d.ToString() 2815 c.Assert(result, Equals, test.expect) 2816 } 2817 2818 testsNull := []interface{}{ 2819 0, 2820 -1, 2821 "-1"} 2822 2823 for _, i := range testsNull { 2824 t := []types.Causet{types.NewCauset(i)} 2825 f, err := fc.getFunction(s.ctx, s.datumsToConstants(t)) 2826 c.Assert(err, IsNil) 2827 d, err := evalBuiltinFunc(f, chunk.Event{}) 2828 c.Assert(err, IsNil) 2829 c.Assert(d.IsNull(), IsTrue) 2830 } 2831 } 2832 2833 func (s *testEvaluatorSuite) TestGetIntervalFromDecimal(c *C) { 2834 du := baseDateArithmitical{} 2835 2836 tests := []struct { 2837 param string 2838 expect string 2839 unit string 2840 }{ 2841 {"1.100", "1:100", "MINUTE_SECOND"}, 2842 {"1.10000", "1-10000", "YEAR_MONTH"}, 2843 {"1.10000", "1 10000", "DAY_HOUR"}, 2844 {"11000", "0 00:00:11000", "DAY_MICROSECOND"}, 2845 {"11000", "00:00:11000", "HOUR_MICROSECOND"}, 2846 {"11.1000", "00:11:1000", "HOUR_SECOND"}, 2847 {"1000", "00:1000", "MINUTE_MICROSECOND"}, 2848 } 2849 2850 for _, test := range tests { 2851 interval, isNull, err := du.getIntervalFromDecimal(s.ctx, s.datumsToConstants([]types.Causet{types.NewCauset("CURRENT DATE"), types.NewDecimalCauset(newMyDecimal(c, test.param))}), chunk.Event{}, test.unit) 2852 c.Assert(isNull, IsFalse) 2853 c.Assert(err, IsNil) 2854 c.Assert(interval, Equals, test.expect) 2855 } 2856 }