github.com/dolthub/go-mysql-server@v0.18.0/sql/expression/function/timediff_test.go (about) 1 // Copyright 2021 Dolthub, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package function 16 17 import ( 18 "testing" 19 "time" 20 21 "github.com/stretchr/testify/require" 22 "gopkg.in/src-d/go-errors.v1" 23 24 "github.com/dolthub/go-mysql-server/sql" 25 "github.com/dolthub/go-mysql-server/sql/expression" 26 "github.com/dolthub/go-mysql-server/sql/types" 27 ) 28 29 func TestTimeDiff(t *testing.T) { 30 toTimespan := func(str string) types.Timespan { 31 res, err := types.Time.ConvertToTimespan(str) 32 if err != nil { 33 t.Fatal(err) 34 } 35 return res 36 } 37 38 ctx := sql.NewEmptyContext() 39 testCases := []struct { 40 name string 41 from sql.Expression 42 to sql.Expression 43 expected interface{} 44 err bool 45 }{ 46 { 47 "invalid type text", 48 expression.NewLiteral("hello there", types.Text), 49 expression.NewConvert(expression.NewLiteral("01:00:00", types.Text), expression.ConvertToTime), 50 nil, 51 false, 52 }, 53 { 54 "invalid type date", 55 expression.NewConvert(expression.NewLiteral("2020-01-03", types.Text), expression.ConvertToDate), 56 expression.NewConvert(expression.NewLiteral("2020-01-04", types.Text), expression.ConvertToDate), 57 toTimespan("-24:00:00"), 58 false, 59 }, 60 { 61 "type mismatch 1", 62 expression.NewLiteral(time.Date(2008, time.December, 29, 1, 1, 1, 2, time.Local), types.Timestamp), 63 expression.NewConvert(expression.NewLiteral("01:00:00", types.Text), expression.ConvertToTime), 64 nil, 65 false, 66 }, 67 { 68 "type mismatch 2", 69 expression.NewLiteral("00:00:00.2", types.Text), 70 expression.NewLiteral("2020-10-10 10:10:10", types.Text), 71 nil, 72 false, 73 }, 74 { 75 "valid mismatch", 76 expression.NewLiteral(time.Date(2008, time.December, 29, 1, 1, 1, 2, time.Local), types.Timestamp), 77 expression.NewLiteral(time.Date(2008, time.December, 30, 1, 1, 1, 2, time.Local), types.DatetimeMaxPrecision), 78 toTimespan("-24:00:00"), 79 false, 80 }, 81 { 82 "timestamp types 1", 83 expression.NewLiteral(time.Date(2018, time.May, 2, 0, 0, 0, 0, time.Local), types.Timestamp), 84 expression.NewLiteral(time.Date(2018, time.May, 2, 0, 0, 1, 0, time.Local), types.Timestamp), 85 toTimespan("-00:00:01"), 86 false, 87 }, 88 { 89 "timestamp types 2", 90 expression.NewLiteral(time.Date(2008, time.December, 31, 23, 59, 59, 1, time.Local), types.Timestamp), 91 expression.NewLiteral(time.Date(2008, time.December, 30, 1, 1, 1, 2, time.Local), types.Timestamp), 92 toTimespan("46:58:57.999999"), 93 false, 94 }, 95 { 96 "time types 1", 97 expression.NewConvert(expression.NewLiteral("00:00:00.1", types.Text), expression.ConvertToTime), 98 expression.NewConvert(expression.NewLiteral("00:00:00.2", types.Text), expression.ConvertToTime), 99 toTimespan("-00:00:00.100000"), 100 false, 101 }, 102 { 103 "time types 2", 104 expression.NewLiteral("00:00:00.2", types.Text), 105 expression.NewLiteral("00:00:00.4", types.Text), 106 toTimespan("-00:00:00.200000"), 107 false, 108 }, 109 { 110 "datetime types", 111 expression.NewLiteral(time.Date(2008, time.December, 29, 0, 0, 0, 0, time.Local), types.DatetimeMaxPrecision), 112 expression.NewLiteral(time.Date(2008, time.December, 30, 0, 0, 0, 0, time.Local), types.DatetimeMaxPrecision), 113 toTimespan("-24:00:00"), 114 false, 115 }, 116 { 117 "datetime string types", 118 expression.NewLiteral("2008-12-29 00:00:00", types.Text), 119 expression.NewLiteral("2008-12-30 00:00:00", types.Text), 120 toTimespan("-24:00:00"), 121 false, 122 }, 123 { 124 "datetime string mix types", 125 expression.NewLiteral(time.Date(2008, time.December, 29, 0, 0, 0, 0, time.UTC), types.DatetimeMaxPrecision), 126 expression.NewLiteral("2008-12-30 00:00:00", types.Text), 127 toTimespan("-24:00:00"), 128 false, 129 }, 130 { 131 "first argument is null", 132 nil, 133 expression.NewLiteral("2008-12-30 00:00:00", types.Text), 134 nil, 135 false, 136 }, 137 { 138 "second argument is null", 139 expression.NewLiteral("2008-12-30 00:00:00", types.Text), 140 nil, 141 nil, 142 false, 143 }, 144 { 145 "both arguments are null", 146 nil, 147 nil, 148 nil, 149 false, 150 }, 151 } 152 153 for _, tt := range testCases { 154 t.Run(tt.name, func(t *testing.T) { 155 require := require.New(t) 156 diff := NewTimeDiff(tt.from, tt.to) 157 result, err := diff.Eval(ctx, nil) 158 if tt.err { 159 require.Error(err) 160 } else { 161 require.NoError(err) 162 require.Equal(tt.expected, result) 163 } 164 }) 165 } 166 } 167 168 func TestDateDiff(t *testing.T) { 169 dt, _ := time.Parse("2006-Jan-02", "2019-Dec-31") 170 testCases := []struct { 171 name string 172 e1Type sql.Type 173 e2Type sql.Type 174 row sql.Row 175 expected interface{} 176 err *errors.Kind 177 }{ 178 {"time and text types, ", types.DatetimeMaxPrecision, types.Text, sql.NewRow(dt, "2019-12-28"), int64(3), nil}, 179 {"text types, diff day, less than 24 hours time diff", types.Text, types.Text, sql.NewRow("2007-12-31 23:58:59", "2007-12-30 23:59:59"), int64(1), nil}, 180 {"text types, same day, 23:59:59 time diff", types.Text, types.Text, sql.NewRow("2007-12-30 23:59:59", "2007-12-30 00:00:00"), int64(0), nil}, 181 {"text types, diff day, 1 min time diff", types.Text, types.Text, sql.NewRow("2007-12-31 00:00:59", "2007-12-30 23:59:59"), int64(1), nil}, 182 {"text types, negative result", types.Text, types.Text, sql.NewRow("2010-11-30 22:59:59", "2010-12-31 23:59:59"), int64(-31), nil}, 183 {"text types, positive result", types.Text, types.Text, sql.NewRow("2007-12-31 23:59:59", "2007-12-30"), int64(1), nil}, 184 {"text types, negative result", types.Text, types.Text, sql.NewRow("2010-11-30 23:59:59", "2010-12-31"), int64(-31), nil}, 185 {"text types, day difference result", types.Text, types.Text, sql.NewRow("2017-06-25", "2017-06-15"), int64(10), nil}, 186 {"text types, year difference result", types.Text, types.Text, sql.NewRow("2017-06-25", "2016-06-15"), int64(375), nil}, 187 {"text types, format with /", types.Text, types.Text, sql.NewRow("2007/12/22", "2007/12/20"), int64(2), nil}, 188 {"text types, positive result", types.Text, types.Text, sql.NewRow("2007-12-31", "2007-12-29 23:59:59"), int64(2), nil}, 189 {"text types, negative result", types.Text, types.Text, sql.NewRow("2010-11-02", "2010-11-30 23:59:59"), int64(-28), nil}, 190 {"first argument is null", types.Text, types.Text, sql.NewRow(nil, "2010-11-02"), nil, nil}, 191 {"second argument is null", types.Text, types.Text, sql.NewRow("2010-11-02", nil), nil, nil}, 192 {"both arguments are null", types.Text, types.Text, sql.NewRow(nil, nil), nil, nil}, 193 } 194 195 for _, tt := range testCases { 196 args0 := expression.NewGetField(0, tt.e1Type, "", false) 197 args1 := expression.NewGetField(1, tt.e2Type, "", false) 198 f := NewDateDiff(args0, args1) 199 200 t.Run(tt.name, func(t *testing.T) { 201 require := require.New(t) 202 203 result, err := f.Eval(sql.NewEmptyContext(), tt.row) 204 if tt.err != nil { 205 require.Error(err) 206 require.True(tt.err.Is(err)) 207 } else { 208 require.NoError(err) 209 require.Equal(tt.expected, result) 210 } 211 }) 212 } 213 } 214 215 func TestTimestampDiff(t *testing.T) { 216 testCases := []struct { 217 name string 218 unit sql.Type 219 e1Type sql.Type 220 e2Type sql.Type 221 row sql.Row 222 expected interface{} 223 err bool 224 }{ 225 {"invalid unit", types.Text, types.Text, types.Text, sql.NewRow("MILLISECOND", "2007-12-30 23:59:59", "2007-12-31 00:00:00"), nil, true}, 226 {"microsecond", types.Text, types.Text, types.Text, sql.NewRow("MICROSECOND", "2007-12-30 23:59:59", "2007-12-31 00:00:00"), int64(1000000), false}, 227 {"microsecond - small number", types.Text, types.DatetimeMaxPrecision, types.DatetimeMaxPrecision, sql.NewRow("MICROSECOND", 228 time.Date(2017, 11, 12, 16, 16, 25, 2*int(time.Microsecond), time.Local), 229 time.Date(2017, 11, 12, 16, 16, 25, 333*int(time.Microsecond), time.Local)), int64(331), false}, 230 {"microsecond - negative", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_MICROSECOND", "2017-11-12 16:16:25.000022 +0000 UTC", "2017-11-12 16:16:25.000000 +0000 UTC"), int64(-22), false}, 231 {"second", types.Text, types.Text, types.Text, sql.NewRow("SECOND", "2007-12-30 23:59:58", "2007-12-31 00:00:00"), int64(2), false}, 232 {"second", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_SECOND", "2017-11-12 16:16:25.000022 +0000 UTC", "2017-11-12 16:16:25.000000 +0000 UTC"), int64(0), false}, 233 {"minute - less than minute", types.Text, types.Text, types.Text, sql.NewRow("MINUTE", "2007-12-30 23:59:59", "2007-12-31 00:00:00"), int64(0), false}, 234 {"minute - exactly one minute", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_MINUTE", "2007-12-30 23:59:00", "2007-12-31 00:00:00"), int64(1), false}, 235 {"hour - less", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_HOUR", "2007-12-30 22:29:00", "2007-12-31 00:00:00"), int64(1), false}, 236 {"hour", types.Text, types.Text, types.Text, sql.NewRow("HOUR", "2007-12-29 22:29:00", "2007-12-31 00:00:00"), int64(25), false}, 237 {"hour - negative", types.Text, types.Text, types.Text, sql.NewRow("HOUR", "2007-12-31 22:29:00", "2007-12-31 00:00:00"), int64(-22), false}, 238 {"day - less", types.Text, types.Text, types.Text, sql.NewRow("DAY", "2007-12-30 22:29:00", "2007-12-31 00:00:00"), int64(0), false}, 239 {"day", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_DAY", "2007-12-01 22:29:00", "2007-12-31 00:00:00"), int64(29), false}, 240 {"day - negative", types.Text, types.Text, types.Text, sql.NewRow("DAY", "2007-12-31 22:29:00", "2007-12-30 00:00:00"), int64(-1), false}, 241 {"week - less", types.Text, types.Text, types.Text, sql.NewRow("WEEK", "2007-12-31 00:00:00", "2007-12-24 00:00:01"), int64(0), false}, 242 {"week", types.Text, types.Text, types.Text, sql.NewRow("WEEK", "2007-10-30 00:00:00", "2007-12-24 00:00:01"), int64(7), false}, 243 {"week - negative", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_WEEK", "2007-12-31 00:00:00", "2007-12-24 00:00:00"), int64(-1), false}, 244 {"month - second less than a month", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_MONTH", "2007-11-30 00:00:00", "2007-12-29 23:59:59"), int64(0), false}, 245 {"month", types.Text, types.Text, types.Text, sql.NewRow("MONTH", "2007-01-31 00:00:00", "2007-12-30 00:00:00"), int64(10), false}, 246 {"month - negative", types.Text, types.Text, types.Text, sql.NewRow("MONTH", "2008-01-31 00:00:01", "2007-12-30 00:00:00"), int64(-1), false}, 247 {"quarter - exactly a quarter", types.Text, types.Text, types.Text, sql.NewRow("QUARTER", "2007-08-30 00:00:00", "2007-11-30 00:00:00"), int64(1), false}, 248 {"quarter - second less than a quarter", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_QUARTER", "2007-08-30 00:00:01", "2007-11-30 00:00:00"), int64(0), false}, 249 {"quarter", types.Text, types.Text, types.Text, sql.NewRow("QUARTER", "2006-08-30 00:00:00", "2007-11-30 00:00:00"), int64(5), false}, 250 {"quarter - negative", types.Text, types.Text, types.Text, sql.NewRow("QUARTER", "2006-08-30 00:00:00", "2002-11-30 00:00:00"), int64(-15), false}, 251 {"year - second less than a month", types.Text, types.Text, types.Text, sql.NewRow("YEAR", "2019-01-01 00:00:00", "2019-12-31 23:59:59"), int64(0), false}, 252 {"year", types.Text, types.Text, types.Text, sql.NewRow("YEAR", "2016-09-04 00:00:01", "2021-09-04 00:00:00"), int64(4), false}, 253 {"year - ", types.Text, types.Text, types.Text, sql.NewRow("YEAR", "2016-09-04 01:00:01", "2021-09-04 02:00:02"), int64(5), false}, 254 {"year - negative", types.Text, types.Text, types.Text, sql.NewRow("SQL_TSI_YEAR", "2016-09-05 00:00:00", "2006-09-04 23:59:59"), int64(-10), false}, 255 {"unit is null", types.Text, types.Text, types.Text, sql.NewRow(nil, "2016-09-05 00:00:00", "2006-09-04 23:59:59"), nil, true}, 256 {"first timestamp is null", types.Text, types.Text, types.Text, sql.NewRow("YEAR", nil, "2021-09-04 02:00:02"), nil, false}, 257 {"second timestamp is null", types.Text, types.Text, types.Text, sql.NewRow("YEAR", "2016-09-04 00:00:01", nil), nil, false}, 258 {"both timestamps are null", types.Text, types.Text, types.Text, sql.NewRow("YEAR", nil, nil), nil, false}, 259 } 260 261 for _, tt := range testCases { 262 args0 := expression.NewGetField(0, tt.unit, "", false) 263 args1 := expression.NewGetField(1, tt.e1Type, "", false) 264 args2 := expression.NewGetField(2, tt.e2Type, "", false) 265 f := NewTimestampDiff(args0, args1, args2) 266 267 t.Run(tt.name, func(t *testing.T) { 268 require := require.New(t) 269 270 result, err := f.Eval(sql.NewEmptyContext(), tt.row) 271 if tt.err { 272 require.Error(err) 273 } else { 274 require.NoError(err) 275 require.Equal(tt.expected, result) 276 } 277 }) 278 } 279 }