diff options
| author | Alexander Smirnov <alex@ydb.tech> | 2024-11-20 11:14:58 +0000 |
|---|---|---|
| committer | Alexander Smirnov <alex@ydb.tech> | 2024-11-20 11:14:58 +0000 |
| commit | 31773f157bf8164364649b5f470f52dece0a4317 (patch) | |
| tree | 33d0f7eef45303ab68cf08ab381ce5e5e36c5240 /yql/essentials/tests/sql/suites/bigdate | |
| parent | 2c7938962d8689e175574fc1e817c05049f27905 (diff) | |
| parent | eff600952d5dfe17942f38f510a8ac2b203bb3a5 (diff) | |
| download | ydb-31773f157bf8164364649b5f470f52dece0a4317.tar.gz | |
Merge branch 'rightlib' into mergelibs-241120-1113
Diffstat (limited to 'yql/essentials/tests/sql/suites/bigdate')
69 files changed, 1799 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/bigdate/BigDates.txt b/yql/essentials/tests/sql/suites/bigdate/BigDates.txt new file mode 100644 index 0000000000..dfdbaa09b8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/BigDates.txt @@ -0,0 +1,31 @@ +{"row"=-2147483649; "d32"=0; "dt64"=-2147483649; "ts64"=-2147483649; "i64"=-2147483649 }; +{"row"=-2147483648; "d32"=0; "dt64"=-2147483648; "ts64"=-2147483648; "i64"=-2147483648 }; +{"row"=-32769; "d32"=-32769; "dt64"=-32769; "ts64"=-32769; "i64"=-32769 }; +{"row"=-32768; "d32"=-32768; "dt64"=-32768; "ts64"=-32768; "i64"=-32768 }; +{"row"=-129; "d32"=-129; "dt64"=-129; "ts64"=-129; "i64"=-129 }; +{"row"=-128; "d32"=-128; "dt64"=-128; "ts64"=-128; "i64"=-128 }; + +{"row"=-7; "d32"=-53375809; "dt64"=-4611669897600; "ts64"=-4611669897600000000; "i64"=-9223339708799999999 }; +{"row"=-6; "d32"=-719163; "dt64"=-62135596801; "ts64"=-62135596800000001; "i64"=-4291747200000000 }; +{"row"=-5; "d32"=-719162; "dt64"=-62135596800; "ts64"=-62135596800000000; "i64"=-4291747199999999 }; +{"row"=-4; "d32"=-366; "dt64"=-86401; "ts64"=-86400000001; "i64"=-86400000001 }; +{"row"=-3; "d32"=-365; "dt64"=-86400; "ts64"=-86400000000; "i64"=-86400000000 }; +{"row"=-2; "d32"=-364; "dt64"=-86399; "ts64"=-86399999999; "i64"=-86399999999 }; + +{"row"=-1; "d32"=-1; "dt64"=-1; "ts64"=-1; "i64"=-1 }; +{"row"=0; "d32"=0; "dt64"=0; "ts64"=0; "i64"=0 }; +{"row"=1; "d32"=1; "dt64"=1; "ts64"=1; "i64"=1 }; + +{"row"=2; "d32"=364; "dt64"=86399; "ts64"=86399999999; "i64"=86399999999 }; +{"row"=3; "d32"=365; "dt64"=86400; "ts64"=86400000000; "i64"=86400000000 }; +{"row"=4; "d32"=366; "dt64"=86401; "ts64"=86400000001; "i64"=86400000001 }; +{"row"=5; "d32"=49672; "dt64"=4291747199; "ts64"=4291747199999999; "i64"=4291747199999999 }; +{"row"=6; "d32"=49673; "dt64"=4291747200; "ts64"=4291747200000000; "i64"=4291747200000000 }; +{"row"=7; "d32"=53375807; "dt64"=4611669811199; "ts64"=4611669811199999999; "i64"=9223339708799999999 }; + +{"row"=128; "d32"=128; "dt64"=128; "ts64"=128; "i64"=128 }; +{"row"=256; "d32"=256; "dt64"=256; "ts64"=256; "i64"=256 }; +{"row"=32768; "d32"=32768; "dt64"=32768; "ts64"=32768; "i64"=32768 }; +{"row"=65536; "d32"=65536; "dt64"=65536; "ts64"=65536; "i64"=65536 }; +{"row"=2147483648; "d32"=0; "dt64"=2147483648; "ts64"=2147483648; "i64"=2147483648 }; +{"row"=4294967296; "d32"=0; "dt64"=4294967296; "ts64"=4294967296; "i64"=4294967296 }; diff --git a/yql/essentials/tests/sql/suites/bigdate/BigDates.txt.attr b/yql/essentials/tests/sql/suites/bigdate/BigDates.txt.attr new file mode 100644 index 0000000000..aa7e0bc89c --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/BigDates.txt.attr @@ -0,0 +1,17 @@ +{ + "schema" = <"strict" = %true; "unique_keys" = %false> [ + { "type" = "int64"; "required" = %true; "name" = "row" }; + { "type" = "int32"; "required" = %true; "name" = "d32" }; + { "type" = "int64"; "required" = %true; "name" = "dt64" }; + { "type" = "int64"; "required" = %true; "name" = "ts64" }; + { "type" = "int64"; "required" = %true; "name" = "i64" }; + ]; + "_yql_row_spec" = { + "TypePatch" = ["StructType";[ + ["d32"; ["DataType"; "Date32"]]; + ["dt64"; ["DataType"; "Datetime64"]]; + ["ts64" ;["DataType"; "Timestamp64"]]; + ["i64"; ["DataType"; "Interval64"]]; + ]]; + }; +} diff --git a/yql/essentials/tests/sql/suites/bigdate/NarrowDates.txt b/yql/essentials/tests/sql/suites/bigdate/NarrowDates.txt new file mode 100644 index 0000000000..66d70ca8ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/NarrowDates.txt @@ -0,0 +1,14 @@ +{"row"=0u; "d"=0u; "dt"=0u; "ts"=0u }; +{"row"=1u; "d"=1u; "dt"=1u; "ts"=1u }; + +{"row"=2u; "d"=364u; "dt"=86399u; "ts"=86399999999u }; +{"row"=3u; "d"=365u; "dt"=86400u; "ts"=86400000000u }; +{"row"=4u; "d"=366u; "dt"=86401u; "ts"=86400000001u }; +{"row"=5u; "d"=49672u; "dt"=4291747199u; "ts"=4291747199999999u }; + +{"row"=128u; "d"=128u; "dt"=128u; "ts"=128u }; +{"row"=256u; "d"=256u; "dt"=256u; "ts"=256u }; +{"row"=32768u; "d"=32768u; "dt"=32768u; "ts"=32768u }; +{"row"=65536u; "d"=0u; "dt"=65536u; "ts"=65536u }; +{"row"=2147483648u; "d"=0u; "dt"=2147483648u; "ts"=2147483648u }; +{"row"=4294967296u; "d"=0u; "dt"=0u; "ts"=4294967296u }; diff --git a/yql/essentials/tests/sql/suites/bigdate/NarrowDates.txt.attr b/yql/essentials/tests/sql/suites/bigdate/NarrowDates.txt.attr new file mode 100644 index 0000000000..d5ea06fda8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/NarrowDates.txt.attr @@ -0,0 +1,15 @@ +{ + "schema" = <"strict" = %true; "unique_keys" = %false> [ + { "type" = "uint64"; "required" = %true; "name" = "row" }; + { "type" = "uint16"; "required" = %true; "name" = "d" }; + { "type" = "uint32"; "required" = %true; "name" = "dt" }; + { "type" = "uint64"; "required" = %true; "name" = "ts" }; + ]; + "_yql_row_spec" = { + "TypePatch" = ["StructType";[ + ["d"; ["DataType"; "Date"]]; + ["dt"; ["DataType"; "Datetime"]]; + ["ts" ;["DataType"; "Timestamp"]]; + ]]; + }; +} diff --git a/yql/essentials/tests/sql/suites/bigdate/NarrowInterval.txt b/yql/essentials/tests/sql/suites/bigdate/NarrowInterval.txt new file mode 100644 index 0000000000..49c519f404 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/NarrowInterval.txt @@ -0,0 +1,27 @@ +{"row"=-2147483649;"i"=-2147483649 }; +{"row"=-2147483648;"i"=-2147483648 }; +{"row"=-32769;"i"=-32769 }; +{"row"=-32768;"i"=-32768 }; +{"row"=-129;"i"=-129 }; +{"row"=-128;"i"=-128 }; + +{"row"=-5;"i"=-4291747199999999 }; +{"row"=-4;"i"=-86400000001 }; +{"row"=-3;"i"=-86400000000 }; +{"row"=-2;"i"=-86399999999 }; + +{"row"=-1;"i"=-1 }; +{"row"=0;"i"=0 }; +{"row"=1;"i"=1 }; + +{"row"=2;"i"=86399999999 }; +{"row"=3;"i"=86400000000 }; +{"row"=4;"i"=86400000001 }; +{"row"=5;"i"=4291747199999999 }; + +{"row"=128;"i"=128 }; +{"row"=256;"i"=256 }; +{"row"=32768;"i"=32768 }; +{"row"=65536;"i"=65536 }; +{"row"=2147483648;"i"=2147483648 }; +{"row"=4294967296;"i"=4294967296 }; diff --git a/yql/essentials/tests/sql/suites/bigdate/NarrowInterval.txt.attr b/yql/essentials/tests/sql/suites/bigdate/NarrowInterval.txt.attr new file mode 100644 index 0000000000..bb8f05160e --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/NarrowInterval.txt.attr @@ -0,0 +1,11 @@ +{ + "schema" = <"strict" = %true; "unique_keys" = %false> [ + { "type" = "int64"; "required" = %true; "name" = "row" }; + { "type" = "int64"; "required" = %true; "name" = "i" }; + ]; + "_yql_row_spec" = { + "TypePatch" = ["StructType";[ + ["i"; ["DataType"; "Interval"]]; + ]]; + }; +} diff --git a/yql/essentials/tests/sql/suites/bigdate/Signed.txt b/yql/essentials/tests/sql/suites/bigdate/Signed.txt new file mode 100644 index 0000000000..3a6634c986 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/Signed.txt @@ -0,0 +1,17 @@ +{ "row"=-8; "i8"=-16; "i16"=-256; "i32"=-65536; "i64"=-4294967296; }; +{ "row"=-7; "i8"=-128; "i16"=-32768; "i32"=-2147483648; "i64"=-9223372036854775808; }; +{ "row"=-6; "i8"=-128; "i16"=-32768; "i32"=-2147483648; "i64"=-9223339708799999999; }; +{ "row"=-5; "i8"=-128; "i16"=-32768; "i32"=-2147483648; "i64"=-4611669897600000001; }; +{ "row"=-4; "i8"=-128; "i16"=-32768; "i32"=-2147483648; "i64"=-4611669897600000000; }; +{ "row"=-3; "i8"=-128; "i16"=-32768; "i32"=-53375810; "i64"=-4611669897601; }; +{ "row"=-2; "i8"=-128; "i16"=-32768; "i32"=-53375809; "i64"=-4611669897600; }; +{ "row"=-1; "i8"=-1; "i16"=-1; "i32"=-1; "i64"=-1; }; +{ "row"=0; "i8"=0; "i16"=0; "i32"=0; "i64"=0; }; +{ "row"=1; "i8"=1; "i16"=1; "i32"=1; "i64"=1; }; +{ "row"=2; "i8"=127; "i16"=32767; "i32"=53375807; "i64"=4611669811199; }; +{ "row"=3; "i8"=127; "i16"=32767; "i32"=53375808; "i64"=4611669811200; }; +{ "row"=4; "i8"=127; "i16"=32767; "i32"=2147483647; "i64"=4611669811199999999; }; +{ "row"=5; "i8"=127; "i16"=32767; "i32"=2147483647; "i64"=4611669811200000000; }; +{ "row"=6; "i8"=127; "i16"=32767; "i32"=2147483647; "i64"=9223339708799999999; }; +{ "row"=7; "i8"=127; "i16"=32767; "i32"=2147483647; "i64"=9223372036854775807; }; +{ "row"=8; "i8"=16; "i16"=256; "i32"=65536; "i64"=4294967296; }; diff --git a/yql/essentials/tests/sql/suites/bigdate/Signed.txt.attr b/yql/essentials/tests/sql/suites/bigdate/Signed.txt.attr new file mode 100644 index 0000000000..56781be680 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/Signed.txt.attr @@ -0,0 +1,9 @@ +{ + "schema" = <"strict" = %true; "unique_keys" = %false> [ + {"type" = "int64"; "required" = %true; "name" = "row"}; + {"type" = "int8"; "required" = %true; "name" = "i8"}; + {"type" = "int16"; "required" = %true; "name" = "i16"}; + {"type" = "int32"; "required" = %true; "name" = "i32"}; + {"type" = "int64"; "required" = %true; "name" = "i64"}; + ]; +} diff --git a/yql/essentials/tests/sql/suites/bigdate/Unsigned.txt b/yql/essentials/tests/sql/suites/bigdate/Unsigned.txt new file mode 100644 index 0000000000..57d9f27b10 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/Unsigned.txt @@ -0,0 +1,8 @@ +{ "row"=0u; "ui8"=0u; "ui16"=0u; "ui32"=0u; "ui64"=0u; }; +{ "row"=1u; "ui8"=1u; "ui16"=1u; "ui32"=1u; "ui64"=1u; }; +{ "row"=2u; "ui8"=255u; "ui16"=65535u; "ui32"=53375807u; "ui64"=4611669811199u; }; +{ "row"=3u; "ui8"=255u; "ui16"=65535u; "ui32"=53375808u; "ui64"=4611669811200u; }; +{ "row"=4u; "ui8"=255u; "ui16"=65535u; "ui32"=4294967295u; "ui64"=4611669811199999999u; }; +{ "row"=5u; "ui8"=255u; "ui16"=65535u; "ui32"=4294967295u; "ui64"=4611669811200000000u; }; +{ "row"=6u; "ui8"=255u; "ui16"=65535u; "ui32"=4294967295u; "ui64"=9223339708799999999u; }; +{ "row"=7u; "ui8"=255u; "ui16"=65535u; "ui32"=4294967295u; "ui64"=18446744073709551615u; }; diff --git a/yql/essentials/tests/sql/suites/bigdate/Unsigned.txt.attr b/yql/essentials/tests/sql/suites/bigdate/Unsigned.txt.attr new file mode 100644 index 0000000000..9d72213c91 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/Unsigned.txt.attr @@ -0,0 +1,9 @@ +{ + "schema" = <"strict" = %true; "unique_keys" = %false> [ + {"type" = "uint64"; "required" = %true; "name" = "row"}; + {"type" = "uint8"; "required" = %true; "name" = "ui8"}; + {"type" = "uint16"; "required" = %true; "name" = "ui16"}; + {"type" = "uint32"; "required" = %true; "name" = "ui32"}; + {"type" = "uint64"; "required" = %true; "name" = "ui64"}; + ]; +} diff --git a/yql/essentials/tests/sql/suites/bigdate/arithmetic.sql b/yql/essentials/tests/sql/suites/bigdate/arithmetic.sql new file mode 100644 index 0000000000..5e72aa0c06 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/arithmetic.sql @@ -0,0 +1,103 @@ +$date32_min = unwrap(cast(-53375809 as date32)); +$date32_max = unwrap(cast(53375807 as date32)); +$datetime64_min = unwrap(cast(-4611669897600 as datetime64)); +$datetime64_max = unwrap(cast(4611669811199 as datetime64)); +$timestamp64_min = unwrap(cast(-4611669897600000000 as timestamp64)); +$timestamp64_max = unwrap(cast(4611669811199999999 as timestamp64)); +$interval64_min = unwrap(cast(-9223339708799999999 as interval64)); +$interval64_max = unwrap(cast(9223339708799999999 as interval64)); +$interval64_plus1 = unwrap(cast(1 as interval64)); +$interval64_minus1 = unwrap(cast(-1 as interval64)); +$interval64_zero = unwrap(cast(0 as interval64)); + +$date_max_value = 49673l; +$date_max = unwrap(cast($date_max_value - 1 as date)); +$datetime_max = unwrap(cast($date_max_value*86400 - 1 as datetime)); +$timestamp_max = unwrap(cast($date_max_value*86400*1000000 - 1 as timestamp)); +$interval_min = unwrap(cast(-$date_max_value*86400*1000000 + 1 as interval)); +$interval_max = unwrap(cast($date_max_value*86400*1000000 - 1 as interval)); +$interval_plus1 = unwrap(cast(1 as interval)); +$interval_minus1 = unwrap(cast(-1 as interval)); + +$i64_max = 9223372036854775807l; +$ui64_max = 18446744073709551615ul; + +select 1, $date32_min - $date32_max, $date32_max - $date32_min +, $date32_min - $datetime64_max, $date32_max - $datetime64_min +, $date32_min - $timestamp64_max, $date32_max - $timestamp64_min +, 2, $date32_min - $date_max +, $date32_min - $datetime_max +, $date32_min - $timestamp_max +, 3, $date32_min - $interval64_minus1, $date32_max - $interval64_minus1 +, $date32_min - $interval64_plus1, $date32_max - $interval64_plus1 +, $date32_min + $interval64_minus1, $date32_max + $interval64_minus1 +, $date32_min + $interval64_plus1, $date32_max + $interval64_plus1 +, 4, $date32_min - $interval_minus1, $date32_max - $interval_minus1 +, $date32_min - $interval_plus1, $date32_max - $interval_plus1 +, $date32_min + $interval_minus1, $date32_max + $interval_minus1 +, $date32_min + $interval_plus1, $date32_max + $interval_plus1; + +select 1, $datetime64_min - $date32_max, $datetime64_max - $date32_min +, $datetime64_min - $datetime64_max, $datetime64_max - $datetime64_min +, $datetime64_min - $timestamp64_max, $datetime64_max - $timestamp64_min +, 2, $datetime64_min - $date_max +, $datetime64_min - $datetime_max +, $datetime64_min - $timestamp_max +, 3, $datetime64_min - $interval64_minus1, $datetime64_max - $interval64_minus1 +, $datetime64_min - $interval64_plus1, $datetime64_max - $interval64_plus1 +, $datetime64_min + $interval64_minus1, $datetime64_max + $interval64_minus1 +, $datetime64_min + $interval64_plus1, $datetime64_max + $interval64_plus1 +, 4, $datetime64_min - $interval_minus1, $datetime64_max - $interval_minus1 +, $datetime64_min - $interval_plus1, $datetime64_max - $interval_plus1 +, $datetime64_min + $interval_minus1, $datetime64_max + $interval_minus1 +, $datetime64_min + $interval_plus1, $datetime64_max + $interval_plus1; + +select 1, $timestamp64_min - $date32_max, $timestamp64_max - $date32_min +, $timestamp64_min - $datetime64_max, $timestamp64_max - $datetime64_min +, $timestamp64_min - $timestamp64_max, $timestamp64_max - $timestamp64_min +, 2, $timestamp64_min - $date_max +, $timestamp64_min - $datetime_max +, $timestamp64_min - $timestamp_max +, 3, $timestamp64_min - $interval64_minus1, $timestamp64_max - $interval64_minus1 +, $timestamp64_min - $interval64_plus1, $timestamp64_max - $interval64_plus1 +, $timestamp64_min + $interval64_minus1, $timestamp64_max + $interval64_minus1 +, $timestamp64_min + $interval64_plus1, $timestamp64_max + $interval64_plus1 +, 4, $timestamp64_min - $interval_minus1, $timestamp64_max - $interval_minus1 +, $timestamp64_min - $interval_plus1, $timestamp64_max - $interval_plus1 +, $timestamp64_min + $interval_minus1, $timestamp64_max + $interval_minus1 +, $timestamp64_min + $interval_plus1, $timestamp64_max + $interval_plus1; + +select 1, $date_max - $date32_min, $date_max - $datetime64_min, $date_max - $timestamp64_min +, $date_max - $date32_max, $date_max - $datetime64_max, $date_max - $timestamp64_max +, $date_max - $interval64_minus1, $date_max + $interval64_minus1 +, $date_max - $interval64_plus1, $date_max + $interval64_plus1 +, 2, $datetime_max - $date32_min, $datetime_max - $datetime64_min, $datetime_max - $timestamp64_min +, $datetime_max - $date32_max, $datetime_max - $datetime64_max, $datetime_max - $timestamp64_max +, $datetime_max - $interval64_minus1, $datetime_max + $interval64_minus1 +, $datetime_max - $interval64_plus1, $datetime_max + $interval64_plus1 +, 3, $timestamp_max - $date32_min, $timestamp_max - $datetime64_min, $timestamp_max - $timestamp64_min +, $timestamp_max - $date32_max, $timestamp_max - $datetime64_max, $timestamp_max - $timestamp64_max +, $timestamp_max - $interval64_minus1, $timestamp_max + $interval64_minus1 +, $timestamp_max - $interval64_plus1, $timestamp_max + $interval64_plus1; + +select 1, $interval_min - $interval64_min, $interval_min + $interval64_min +, $interval_min - $interval64_max, $interval_min + $interval64_max +, $interval_max - $interval64_max, $interval_max + $interval64_max +, $interval_max - $interval64_min, $interval_max + $interval64_min +, 2, $interval64_max - $interval64_min, $interval64_min - $interval64_max +, $interval64_max + $interval64_min, $interval64_max + $interval64_max +, $interval64_min - $interval64_min, $interval64_max - $interval64_max; + +select 0, -$interval64_max, -$interval64_min, -$interval64_zero +, 1, $interval64_max*0, 0*$interval64_max +, 2, $interval64_max*1, 1*$interval64_max, $interval64_max*(-1), (-1)*$interval64_max +, 3, $interval64_min*1, 1*$interval64_min, $interval64_min*(-1), (-1)*$interval64_min +, 4, $interval64_plus1*cast($interval64_max as int64), $interval64_minus1*cast($interval64_min as int64) +, 5, $interval64_max*$ui64_max, $i64_max*$interval64_max, $interval64_min*$ui64_max, $i64_max*$interval64_min +, 6, $interval64_zero*$ui64_max, $ui64_max*$interval64_zero, $interval64_zero*$i64_max, $i64_max*$interval64_zero +, 7, $interval64_max/0, $interval64_min/0, $interval64_max/1, $interval64_min/1, $interval64_max/(-1), $interval64_min/(-1) +, 8, $interval64_zero/$ui64_max, $interval64_zero/$i64_max, $interval64_plus1/$ui64_max, $interval64_plus1/$i64_max, $interval64_minus1/$ui64_max, $interval64_minus1/$i64_max +, 9, $interval64_max/cast($interval64_max as int64), $interval64_min/cast($interval64_min as int64) +, 10, abs($interval64_max), abs($interval64_min), abs($interval64_zero) +, 11, cast(4294967296l as interval64) * 4294967296l, 4294967296ul * cast(4294967296l as interval64) +; diff --git a/yql/essentials/tests/sql/suites/bigdate/bitcast_date32.sql b/yql/essentials/tests/sql/suites/bigdate/bitcast_date32.sql new file mode 100644 index 0000000000..45870eac39 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/bitcast_date32.sql @@ -0,0 +1,10 @@ +pragma warning("disable","4510"); + +select bitcast(Yql::Date32(AsAtom("1")) as Uint8) +, 4, bitcast(Yql::Date32(AsAtom("1")) as Int8) +, 5, bitcast(Yql::Date32(AsAtom("1")) as Uint16) +, 6, bitcast(Yql::Date32(AsAtom("1")) as Int16) +, 7, bitcast(Yql::Date32(AsAtom("1")) as Uint32) +, 8, bitcast(Yql::Date32(AsAtom("1")) as Int32) +, 9, bitcast(Yql::Date32(AsAtom("1")) as Uint64) +, 10, bitcast(Yql::Date32(AsAtom("1")) as Int64); diff --git a/yql/essentials/tests/sql/suites/bigdate/bitcast_datetime64.sql b/yql/essentials/tests/sql/suites/bigdate/bitcast_datetime64.sql new file mode 100644 index 0000000000..bc5aec2b90 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/bitcast_datetime64.sql @@ -0,0 +1,10 @@ +pragma warning("disable","4510"); + +select bitcast(Yql::Datetime64(AsAtom("1")) as Uint8) +, bitcast(Yql::Datetime64(AsAtom("1")) as Int8) +, bitcast(Yql::Datetime64(AsAtom("1")) as Uint16) +, bitcast(Yql::Datetime64(AsAtom("1")) as Int16) +, bitcast(Yql::Datetime64(AsAtom("1")) as Uint32) +, bitcast(Yql::Datetime64(AsAtom("1")) as Int32) +, bitcast(Yql::Datetime64(AsAtom("1")) as Uint64) +, bitcast(Yql::Datetime64(AsAtom("1")) as Int64); diff --git a/yql/essentials/tests/sql/suites/bigdate/bitcast_interval64.sql b/yql/essentials/tests/sql/suites/bigdate/bitcast_interval64.sql new file mode 100644 index 0000000000..0762fd9192 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/bitcast_interval64.sql @@ -0,0 +1,10 @@ +pragma warning("disable","4510"); + +select bitcast(Yql::Interval64(AsAtom("1")) as Uint8) +, bitcast(Yql::Interval64(AsAtom("1")) as Int8) +, bitcast(Yql::Interval64(AsAtom("1")) as Uint16) +, bitcast(Yql::Interval64(AsAtom("1")) as Int16) +, bitcast(Yql::Interval64(AsAtom("1")) as Uint32) +, bitcast(Yql::Interval64(AsAtom("1")) as Int32) +, bitcast(Yql::Interval64(AsAtom("1")) as Uint64) +, bitcast(Yql::Interval64(AsAtom("1")) as Int64); diff --git a/yql/essentials/tests/sql/suites/bigdate/bitcast_timestamp64.sql b/yql/essentials/tests/sql/suites/bigdate/bitcast_timestamp64.sql new file mode 100644 index 0000000000..9261b04498 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/bitcast_timestamp64.sql @@ -0,0 +1,10 @@ +pragma warning("disable","4510"); + +select bitcast(Yql::Timestamp64(AsAtom("1")) as Uint8) +, bitcast(Yql::Timestamp64(AsAtom("1")) as Int8) +, bitcast(Yql::Timestamp64(AsAtom("1")) as Uint16) +, bitcast(Yql::Timestamp64(AsAtom("1")) as Int16) +, bitcast(Yql::Timestamp64(AsAtom("1")) as Uint32) +, bitcast(Yql::Timestamp64(AsAtom("1")) as Int32) +, bitcast(Yql::Timestamp64(AsAtom("1")) as Uint64) +, bitcast(Yql::Timestamp64(AsAtom("1")) as Int64); diff --git a/yql/essentials/tests/sql/suites/bigdate/common_type.sql b/yql/essentials/tests/sql/suites/bigdate/common_type.sql new file mode 100644 index 0000000000..0e4b6ff1b9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/common_type.sql @@ -0,0 +1,49 @@ +$date_val = unwrap(cast(1 as date)); +$date32_val = unwrap(cast(-1 as date32)); + +$datetime_val = unwrap(cast(86400 as datetime)); +$datetime64_val = unwrap(cast(-86400 as datetime64)); + +$timestamp_val = unwrap(cast(86400l*1000000 as timestamp)); +$timestamp64_val = unwrap(cast(-86400l*1000000 as timestamp64)); + +$interval_val = unwrap(cast(1 as interval)); +$interval64_val = unwrap(cast(-1 as interval64)); + +select 1, [$date_val, $datetime_val] +, [$date_val, $timestamp_val] +, [$date_val, $date32_val] +, [$date_val, $datetime64_val] +, [$date_val, $timestamp64_val] +, 2, [$datetime_val, $date_val] +, [$datetime_val, $timestamp_val] +, [$datetime_val, $date32_val] +, [$datetime_val, $datetime64_val] +, [$datetime_val, $timestamp64_val] +, 3, [$timestamp_val, $date_val] +, [$timestamp_val, $datetime_val] +, [$timestamp_val, $date32_val] +, [$timestamp_val, $datetime64_val] +, [$timestamp_val, $timestamp64_val] +, 4, [$date32_val, $date_val] +, [$date32_val, $datetime_val] +, [$date32_val, $timestamp_val] +, [$date32_val, $datetime64_val] +, [$date32_val, $timestamp64_val] +, 5, [$datetime64_val, $date_val] +, [$datetime64_val, $datetime_val] +, [$datetime64_val, $timestamp_val] +, [$datetime64_val, $date32_val] +, [$datetime64_val, $timestamp64_val] +, 6, [$timestamp64_val, $date_val] +, [$timestamp64_val, $datetime_val] +, [$timestamp64_val, $timestamp_val] +, [$timestamp64_val, $date32_val] +, [$timestamp64_val, $datetime64_val] +, 7, [$date_val, $datetime_val, $timestamp_val, $date32_val, $datetime64_val, $timestamp64_val]; + +select [unwrap(cast(1 as interval)), unwrap(cast(-1 as interval64))]; + +$datetime_values = [$date_val, $date32_val, $datetime_val, $datetime64_val, $timestamp_val, $timestamp64_val]; +$interval_values = [$interval_val, $interval64_val]; +select ListSort(DictKeys(ToSet($datetime_values))), ListSort(DictKeys(ToSet($interval_values))); diff --git a/yql/essentials/tests/sql/suites/bigdate/compare_big_big.sql b/yql/essentials/tests/sql/suites/bigdate/compare_big_big.sql new file mode 100644 index 0000000000..d160bcb31a --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/compare_big_big.sql @@ -0,0 +1,26 @@ +pragma warning("disable","4510"); +select + 3, Yql::Date32(AsAtom("0")) = Yql::Date32(AsAtom("1")), + 4, Yql::Datetime64(AsAtom("0")) = Yql::Datetime64(AsAtom("1")), + 5, Yql::Timestamp64(AsAtom("0")) = Yql::Timestamp64(AsAtom("1")), + 6, Yql::Interval64(AsAtom("0")) = Yql::Interval64(AsAtom("1")), + 7, Yql::Date32(AsAtom("0")) < Yql::Date32(AsAtom("1")), + 8, Yql::Datetime64(AsAtom("0")) < Yql::Datetime64(AsAtom("1")), + 9, Yql::Timestamp64(AsAtom("0")) < Yql::Timestamp64(AsAtom("1")), + 10, Yql::Interval64(AsAtom("0")) < Yql::Interval64(AsAtom("1")), + 11, Yql::Date32(AsAtom("0")) > Yql::Date32(AsAtom("1")), + 12, Yql::Datetime64(AsAtom("0")) > Yql::Datetime64(AsAtom("1")), + 13, Yql::Timestamp64(AsAtom("0")) > Yql::Timestamp64(AsAtom("1")), + 14, Yql::Interval64(AsAtom("0")) > Yql::Interval64(AsAtom("1")), + 15, Yql::Date32(AsAtom("0")) != Yql::Date32(AsAtom("1")), + 16, Yql::Datetime64(AsAtom("0")) != Yql::Datetime64(AsAtom("1")), + 17, Yql::Timestamp64(AsAtom("0")) != Yql::Timestamp64(AsAtom("1")), + 18, Yql::Interval64(AsAtom("0")) != Yql::Interval64(AsAtom("1")), + 19, Yql::Date32(AsAtom("0")) <= Yql::Date32(AsAtom("1")), + 20, Yql::Datetime64(AsAtom("0")) <= Yql::Datetime64(AsAtom("1")), + 21, Yql::Timestamp64(AsAtom("0")) <= Yql::Timestamp64(AsAtom("1")), + 22, Yql::Interval64(AsAtom("0")) <= Yql::Interval64(AsAtom("1")), + 23, Yql::Date32(AsAtom("0")) >= Yql::Date32(AsAtom("1")), + 24, Yql::Datetime64(AsAtom("0")) >= Yql::Datetime64(AsAtom("1")), + 25, Yql::Timestamp64(AsAtom("0")) >= Yql::Timestamp64(AsAtom("1")), + 26, Yql::Interval64(AsAtom("0")) >= Yql::Interval64(AsAtom("1")), diff --git a/yql/essentials/tests/sql/suites/bigdate/compare_big_small.sql b/yql/essentials/tests/sql/suites/bigdate/compare_big_small.sql new file mode 100644 index 0000000000..6f444bdec1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/compare_big_small.sql @@ -0,0 +1,85 @@ +pragma warning("disable","4510"); +select + 3, Yql::Date32(AsAtom("1")) = Yql::Date(AsAtom("1")), + 4, Yql::Datetime64(AsAtom("86400")) = Yql::Date(AsAtom("1")), + 5, Yql::Timestamp64(AsAtom("86400000000")) = Yql::Date(AsAtom("1")), + + 7, Yql::Date32(AsAtom("1")) = Yql::Datetime(AsAtom("86400")), + 8, Yql::Datetime64(AsAtom("86400")) = Yql::Datetime(AsAtom("86400")), + 9, Yql::Timestamp64(AsAtom("86400000000")) = Yql::Datetime(AsAtom("86400")), + + 11, Yql::Date32(AsAtom("1")) = Yql::Timestamp(AsAtom("86400000000")), + 12, Yql::Datetime64(AsAtom("86400")) = Yql::Timestamp(AsAtom("86400000000")), + 13, Yql::Timestamp64(AsAtom("86400000000")) = Yql::Timestamp(AsAtom("86400000000")), + + 15, Yql::Interval64(AsAtom("1")) = Yql::Interval(AsAtom("1")), + + 17, Yql::Date32(AsAtom("1")) < Yql::Date(AsAtom("1")), + 18, Yql::Datetime64(AsAtom("86400")) < Yql::Date(AsAtom("1")), + 19, Yql::Timestamp64(AsAtom("86400000000")) < Yql::Date(AsAtom("1")), + + 21, Yql::Date32(AsAtom("1")) < Yql::Datetime(AsAtom("86400")), + 22, Yql::Datetime64(AsAtom("86400")) < Yql::Datetime(AsAtom("86400")), + 23, Yql::Timestamp64(AsAtom("86400000000")) < Yql::Datetime(AsAtom("86400")), + + 25, Yql::Date32(AsAtom("1")) < Yql::Timestamp(AsAtom("86400000000")), + 26, Yql::Datetime64(AsAtom("86400")) < Yql::Timestamp(AsAtom("86400000000")), + 27, Yql::Timestamp64(AsAtom("86400000000")) < Yql::Timestamp(AsAtom("86400000000")), + + 29, Yql::Interval64(AsAtom("1")) < Yql::Interval(AsAtom("1")), + + 31, Yql::Date32(AsAtom("1")) <= Yql::Date(AsAtom("1")), + 32, Yql::Datetime64(AsAtom("86400")) <= Yql::Date(AsAtom("1")), + 33, Yql::Timestamp64(AsAtom("86400000000")) <= Yql::Date(AsAtom("1")), + + 35, Yql::Date32(AsAtom("1")) <= Yql::Datetime(AsAtom("86400")), + 36, Yql::Datetime64(AsAtom("86400")) <= Yql::Datetime(AsAtom("86400")), + 37, Yql::Timestamp64(AsAtom("86400000000")) <= Yql::Datetime(AsAtom("86400")), + + 39, Yql::Date32(AsAtom("1")) <= Yql::Timestamp(AsAtom("86400000000")), + 40, Yql::Datetime64(AsAtom("86400")) <= Yql::Timestamp(AsAtom("86400000000")), + 41, Yql::Timestamp64(AsAtom("86400000000")) <= Yql::Timestamp(AsAtom("86400000000")), + + 43, Yql::Interval64(AsAtom("1")) <= Yql::Interval(AsAtom("1")), + + 45, Yql::Date32(AsAtom("1")) > Yql::Date(AsAtom("1")), + 46, Yql::Datetime64(AsAtom("86400")) > Yql::Date(AsAtom("1")), + 47, Yql::Timestamp64(AsAtom("86400000000")) > Yql::Date(AsAtom("1")), + + 49, Yql::Date32(AsAtom("1")) > Yql::Datetime(AsAtom("86400")), + 50, Yql::Datetime64(AsAtom("86400")) > Yql::Datetime(AsAtom("86400")), + 51, Yql::Timestamp64(AsAtom("86400000000")) > Yql::Datetime(AsAtom("86400")), + + 53, Yql::Date32(AsAtom("1")) > Yql::Timestamp(AsAtom("86400000000")), + 54, Yql::Datetime64(AsAtom("86400")) > Yql::Timestamp(AsAtom("86400000000")), + 55, Yql::Timestamp64(AsAtom("86400000000")) > Yql::Timestamp(AsAtom("86400000000")), + + 57, Yql::Interval64(AsAtom("1")) > Yql::Interval(AsAtom("1")), + + 59, Yql::Date32(AsAtom("1")) >= Yql::Date(AsAtom("1")), + 60, Yql::Datetime64(AsAtom("86400")) >= Yql::Date(AsAtom("1")), + 61, Yql::Timestamp64(AsAtom("86400000000")) >= Yql::Date(AsAtom("1")), + + 63, Yql::Date32(AsAtom("1")) >= Yql::Datetime(AsAtom("86400")), + 64, Yql::Datetime64(AsAtom("86400")) >= Yql::Datetime(AsAtom("86400")), + 65, Yql::Timestamp64(AsAtom("86400000000")) >= Yql::Datetime(AsAtom("86400")), + + 67, Yql::Date32(AsAtom("1")) >= Yql::Timestamp(AsAtom("86400000000")), + 68, Yql::Datetime64(AsAtom("86400")) >= Yql::Timestamp(AsAtom("86400000000")), + 69, Yql::Timestamp64(AsAtom("86400000000")) >= Yql::Timestamp(AsAtom("86400000000")), + + 71, Yql::Interval64(AsAtom("1")) >= Yql::Interval(AsAtom("1")), + + 73, Yql::Date32(AsAtom("1")) != Yql::Date(AsAtom("1")), + 74, Yql::Datetime64(AsAtom("86400")) != Yql::Date(AsAtom("1")), + 75, Yql::Timestamp64(AsAtom("86400000000")) != Yql::Date(AsAtom("1")), + + 77, Yql::Date32(AsAtom("1")) != Yql::Datetime(AsAtom("86400")), + 78, Yql::Datetime64(AsAtom("86400")) != Yql::Datetime(AsAtom("86400")), + 79, Yql::Timestamp64(AsAtom("86400000000")) != Yql::Datetime(AsAtom("86400")), + + 81, Yql::Date32(AsAtom("1")) != Yql::Timestamp(AsAtom("86400000000")), + 82, Yql::Datetime64(AsAtom("86400")) != Yql::Timestamp(AsAtom("86400000000")), + 83, Yql::Timestamp64(AsAtom("86400000000")) != Yql::Timestamp(AsAtom("86400000000")), + + 85, Yql::Interval64(AsAtom("1")) != Yql::Interval(AsAtom("1")); diff --git a/yql/essentials/tests/sql/suites/bigdate/compare_small_big.sql b/yql/essentials/tests/sql/suites/bigdate/compare_small_big.sql new file mode 100644 index 0000000000..3e4834aba2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/compare_small_big.sql @@ -0,0 +1,85 @@ +pragma warning("disable","4510"); +select + 3, Yql::Date(AsAtom("1")) = Yql::Date32(AsAtom("1")), + 4, Yql::Date(AsAtom("1")) = Yql::Datetime64(AsAtom("86400")), + 5, Yql::Date(AsAtom("1")) = Yql::Timestamp64(AsAtom("86400000000")), + + 7, Yql::Datetime(AsAtom("86400")) = Yql::Date32(AsAtom("1")), + 8, Yql::Datetime(AsAtom("86400")) = Yql::Datetime64(AsAtom("86400")), + 9, Yql::Datetime(AsAtom("86400")) = Yql::Timestamp64(AsAtom("86400000000")), + + 11, Yql::Timestamp(AsAtom("86400000000")) = Yql::Date32(AsAtom("1")), + 12, Yql::Timestamp(AsAtom("86400000000")) = Yql::Datetime64(AsAtom("86400")), + 13, Yql::Timestamp(AsAtom("86400000000")) = Yql::Timestamp64(AsAtom("86400000000")), + + 15, Yql::Interval(AsAtom("1")) = Yql::Interval64(AsAtom("1")), + + 17, Yql::Date(AsAtom("1")) < Yql::Date32(AsAtom("1")), + 18, Yql::Date(AsAtom("1")) < Yql::Datetime64(AsAtom("86400")), + 19, Yql::Date(AsAtom("1")) < Yql::Timestamp64(AsAtom("86400000000")), + + 21, Yql::Datetime(AsAtom("86400")) < Yql::Date32(AsAtom("1")), + 22, Yql::Datetime(AsAtom("86400")) < Yql::Datetime64(AsAtom("86400")), + 23, Yql::Datetime(AsAtom("86400")) < Yql::Timestamp64(AsAtom("86400000000")), + + 25, Yql::Timestamp(AsAtom("86400000000")) < Yql::Date32(AsAtom("1")), + 26, Yql::Timestamp(AsAtom("86400000000")) < Yql::Datetime64(AsAtom("86400")), + 27, Yql::Timestamp(AsAtom("86400000000")) < Yql::Timestamp64(AsAtom("86400000000")), + + 29, Yql::Interval(AsAtom("1")) < Yql::Interval64(AsAtom("1")), + + 31, Yql::Date(AsAtom("1")) <= Yql::Date32(AsAtom("1")), + 32, Yql::Date(AsAtom("1")) <= Yql::Datetime64(AsAtom("86400")), + 33, Yql::Date(AsAtom("1")) <= Yql::Timestamp64(AsAtom("86400000000")), + + 35, Yql::Datetime(AsAtom("86400")) <= Yql::Date32(AsAtom("1")), + 36, Yql::Datetime(AsAtom("86400")) <= Yql::Datetime64(AsAtom("86400")), + 37, Yql::Datetime(AsAtom("86400")) <= Yql::Timestamp64(AsAtom("86400000000")), + + 39, Yql::Timestamp(AsAtom("86400000000")) <= Yql::Date32(AsAtom("1")), + 40, Yql::Timestamp(AsAtom("86400000000")) <= Yql::Datetime64(AsAtom("86400")), + 41, Yql::Timestamp(AsAtom("86400000000")) <= Yql::Timestamp64(AsAtom("86400000000")), + + 43, Yql::Interval(AsAtom("1")) <= Yql::Interval64(AsAtom("1")), + + 45, Yql::Date(AsAtom("1")) > Yql::Date32(AsAtom("1")), + 46, Yql::Date(AsAtom("1")) > Yql::Datetime64(AsAtom("86400")), + 47, Yql::Date(AsAtom("1")) > Yql::Timestamp64(AsAtom("86400000000")), + + 49, Yql::Datetime(AsAtom("86400")) > Yql::Date32(AsAtom("1")), + 50, Yql::Datetime(AsAtom("86400")) > Yql::Datetime64(AsAtom("86400")), + 51, Yql::Datetime(AsAtom("86400")) > Yql::Timestamp64(AsAtom("86400000000")), + + 53, Yql::Timestamp(AsAtom("86400000000")) > Yql::Date32(AsAtom("1")), + 54, Yql::Timestamp(AsAtom("86400000000")) > Yql::Datetime64(AsAtom("86400")), + 55, Yql::Timestamp(AsAtom("86400000000")) > Yql::Timestamp64(AsAtom("86400000000")), + + 57, Yql::Interval(AsAtom("1")) > Yql::Interval64(AsAtom("1")), + + 59, Yql::Date(AsAtom("1")) >= Yql::Date32(AsAtom("1")), + 60, Yql::Date(AsAtom("1")) >= Yql::Datetime64(AsAtom("86400")), + 61, Yql::Date(AsAtom("1")) >= Yql::Timestamp64(AsAtom("86400000000")), + + 63, Yql::Datetime(AsAtom("86400")) >= Yql::Date32(AsAtom("1")), + 64, Yql::Datetime(AsAtom("86400")) >= Yql::Datetime64(AsAtom("86400")), + 65, Yql::Datetime(AsAtom("86400")) >= Yql::Timestamp64(AsAtom("86400000000")), + + 67, Yql::Timestamp(AsAtom("86400000000")) >= Yql::Date32(AsAtom("1")), + 68, Yql::Timestamp(AsAtom("86400000000")) >= Yql::Datetime64(AsAtom("86400")), + 69, Yql::Timestamp(AsAtom("86400000000")) >= Yql::Timestamp64(AsAtom("86400000000")), + + 71, Yql::Interval(AsAtom("1")) >= Yql::Interval64(AsAtom("1")), + + 73, Yql::Date(AsAtom("1")) != Yql::Date32(AsAtom("1")), + 74, Yql::Date(AsAtom("1")) != Yql::Datetime64(AsAtom("86400")), + 75, Yql::Date(AsAtom("1")) != Yql::Timestamp64(AsAtom("86400000000")), + + 77, Yql::Datetime(AsAtom("86400")) != Yql::Date32(AsAtom("1")), + 78, Yql::Datetime(AsAtom("86400")) != Yql::Datetime64(AsAtom("86400")), + 79, Yql::Datetime(AsAtom("86400")) != Yql::Timestamp64(AsAtom("86400000000")), + + 81, Yql::Timestamp(AsAtom("86400000000")) != Yql::Date32(AsAtom("1")), + 82, Yql::Timestamp(AsAtom("86400000000")) != Yql::Datetime64(AsAtom("86400")), + 83, Yql::Timestamp(AsAtom("86400000000")) != Yql::Timestamp64(AsAtom("86400000000")), + + 85, Yql::Interval(AsAtom("1")) != Yql::Interval64(AsAtom("1")); diff --git a/yql/essentials/tests/sql/suites/bigdate/const_date32.sql b/yql/essentials/tests/sql/suites/bigdate/const_date32.sql new file mode 100644 index 0000000000..8e31b97181 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/const_date32.sql @@ -0,0 +1,10 @@ +select 1, date32("-144169-01-01"), cast(date32("-144169-01-01") as string); +select 2, date32("-1-1-1"), cast(date32("-1-1-1") as string); +select 3, date32("1-1-1"), cast(date32("1-1-1") as string); +select 4, date32("1969-12-31"), cast(date32("1969-12-31") as string); +select 5, date32("1970-01-01"), cast(date32("1970-01-01") as string); +select 6, date32("2000-04-05"), cast(date32("2000-04-05") as string); +select 7, date32("2100-03-01"), cast(date32("2100-03-01") as string); +select 8, date32("2105-12-31"), cast(date32("2105-12-31") as string); +select 9, date32("2106-01-01"), cast(date32("2106-01-01") as string); +select 10, date32("148107-12-31"), cast(date32("148107-12-31") as string); diff --git a/yql/essentials/tests/sql/suites/bigdate/const_datetime64.sql b/yql/essentials/tests/sql/suites/bigdate/const_datetime64.sql new file mode 100644 index 0000000000..ee21673f5e --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/const_datetime64.sql @@ -0,0 +1,19 @@ +select datetime64("-144169-01-01T00:00:00-0:1"), cast(datetime64("-144169-01-01T00:00:00-0:1") as string); +select datetime64("-144169-01-01T00:00:00Z"), cast(datetime64("-144169-01-01T00:00:00Z") as string); + +select datetime64("-1-1-1T00:00:00Z"), cast(datetime64("-1-1-1T00:00:00Z") as string); +select datetime64("1-1-1T00:00:00Z"), cast(datetime64("1-1-1T00:00:00Z") as string); + +select datetime64("1969-12-31T23:59:59Z"), cast(datetime64("1969-12-31T23:59:59Z") as string); +select datetime64("1969-12-31T23:59:59-0:1"), cast(datetime64("1969-12-31T23:59:59-0:1") as string); +select datetime64("1970-01-01T00:00:00Z"), cast(datetime64("1970-01-01T00:00:00Z") as string); +select datetime64("1970-1-1T0:0:1Z"), cast(datetime64("1970-1-1T0:0:1Z") as string); +select datetime64("1970-01-01T00:00:00+0:1"), cast(datetime64("1970-01-01T00:00:00+0:1") as string); + +select datetime64("2000-04-05T00:00:00Z"), cast(datetime64("2000-04-05T00:00:00Z") as string); +select datetime64("2100-03-01T00:00:00Z"), cast(datetime64("2100-03-01T00:00:00Z") as string); +select datetime64("2105-12-31T00:00:00Z"), cast(datetime64("2105-12-31T00:00:00Z") as string); +select datetime64("2106-01-01T00:00:00Z"), cast(datetime64("2106-01-01T00:00:00Z") as string); + +select datetime64("148107-12-31T23:59:59Z"), cast(datetime64("148107-12-31T23:59:59Z") as string); +select datetime64("148107-12-31T23:59:59+0:1"), cast(datetime64("148107-12-31T23:59:59+0:1") as string); diff --git a/yql/essentials/tests/sql/suites/bigdate/const_interval64.sql b/yql/essentials/tests/sql/suites/bigdate/const_interval64.sql new file mode 100644 index 0000000000..517ee1317d --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/const_interval64.sql @@ -0,0 +1,35 @@ +select interval64("-P106751616DT23H59M59.999999S"), cast(interval64("-P106751616DT23H59M59.999999S") as string); +select interval64("P106751616DT23H59M59.999999S"), cast(interval64("P106751616DT23H59M59.999999S") as string); +select interval64("P106709244DT999999H999999M999999.999999S"), cast(interval64("P106709244DT999999H999999M999999.999999S") as string); +select interval64("P000000000DT00H00M00.000000S"), cast(interval64("P000000000DT00H00M00.000000S") as string); + +select interval64("PT0S"), cast(interval64("PT0S") as string); +select interval64("-PT0S"), cast(interval64("-PT0S") as string); +select interval64("PT0.000001S"), cast(interval64("PT0.000001S") as string); +select interval64("-PT0.000001S"), cast(interval64("-PT0.000001S") as string); + +select interval64("PT0S"), cast(interval64("PT0S") as string); +select interval64("PT0M"), cast(interval64("PT0M") as string); +select interval64("PT0H"), cast(interval64("PT0H") as string); +select interval64("P0D"), cast(interval64("P0D") as string); +select interval64("P0W"), cast(interval64("P0W") as string); + +select interval64("PT999999S"), cast(interval64("PT999999S") as string); +select interval64("PT999999M"), cast(interval64("PT999999M") as string); +select interval64("PT999999H"), cast(interval64("PT999999H") as string); + +select interval64("P106751616D"), cast(interval64("P106751616D") as string); +select interval64("P15250230W"), cast(interval64("P15250230W") as string); + +select interval64("PT1S"), cast(interval64("PT1S") as string); +select interval64("PT1M"), cast(interval64("PT1M") as string); +select interval64("PT1H"), cast(interval64("PT1H") as string); +select interval64("P1D"), cast(interval64("P1D") as string); +select interval64("P1W"), cast(interval64("P1W") as string); + +select interval64("-PT1S"), cast(interval64("-PT1S") as string); +select interval64("-PT1M"), cast(interval64("-PT1M") as string); +select interval64("-PT1H"), cast(interval64("-PT1H") as string); +select interval64("-P1D"), cast(interval64("-P1D") as string); +select interval64("-P1W"), cast(interval64("-P1W") as string); + diff --git a/yql/essentials/tests/sql/suites/bigdate/const_timestamp64.sql b/yql/essentials/tests/sql/suites/bigdate/const_timestamp64.sql new file mode 100644 index 0000000000..919e19e196 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/const_timestamp64.sql @@ -0,0 +1,39 @@ +-- +-- with microseconds +-- + +select timestamp64("-144169-01-01T00:00:00.000000-0:1"), cast(timestamp64("-144169-01-01T00:00:00.000000-0:1") as string); +select timestamp64("-144169-01-01T00:00:00.000000Z"), cast(timestamp64("-144169-01-01T00:00:00.000000Z") as string); + +select timestamp64("1969-12-31T23:59:59.999999Z"), cast(timestamp64("1969-12-31T23:59:59.999999Z") as string); +select timestamp64("1969-12-31T23:59:59.999999-0:1"), cast(timestamp64("1969-12-31T23:59:59.999999-0:1") as string); +select timestamp64("1970-1-1T0:0:0.0Z"), cast(timestamp64("1970-1-1T0:0:0.0Z") as string); +select timestamp64("1970-01-01T00:00:00.000001Z"), cast(timestamp64("1970-01-01T00:00:00.000001Z") as string); +select timestamp64("1970-01-01T00:00:00.000001+0:1"), cast(timestamp64("1970-01-01T00:00:00.000001+0:1") as string); + +select timestamp64("148107-12-31T23:59:59.999999Z"), cast(timestamp64("148107-12-31T23:59:59.999999Z") as string); +select timestamp64("148107-12-31T23:59:59.999999+0:1"), cast(timestamp64("148107-12-31T23:59:59.999999+0:1") as string); + +-- +-- without microseconds (like in datetime64) +-- + +select timestamp64("-144169-01-01T00:00:00-0:1"), cast(timestamp64("-144169-01-01T00:00:00-0:1") as string); +select timestamp64("-144169-01-01T00:00:00Z"), cast(timestamp64("-144169-01-01T00:00:00Z") as string); + +select timestamp64("-1-1-1T00:00:00Z"), cast(timestamp64("-1-1-1T00:00:00Z") as string); +select timestamp64("1-1-1T00:00:00Z"), cast(timestamp64("1-1-1T00:00:00Z") as string); + +select timestamp64("1969-12-31T00:00:00Z"), cast(timestamp64("1969-12-31T00:00:00Z") as string); +select timestamp64("1969-12-31T23:59:59-0:1"), cast(timestamp64("1969-12-31T23:59:59-0:1") as string); +select timestamp64("1970-01-01T00:00:00Z"), cast(timestamp64("1970-01-01T00:00:00Z") as string); +select timestamp64("1970-01-01T00:00:00+0:1"), cast(timestamp64("1970-01-01T00:00:00+0:1") as string); + +select timestamp64("2000-04-05T00:00:00Z"), cast(timestamp64("2000-04-05T00:00:00Z") as string); +select timestamp64("2100-03-01T00:00:00Z"), cast(timestamp64("2100-03-01T00:00:00Z") as string); +select timestamp64("2105-12-31T00:00:00Z"), cast(timestamp64("2105-12-31T00:00:00Z") as string); +select timestamp64("2106-01-01T00:00:00Z"), cast(timestamp64("2106-01-01T00:00:00Z") as string); + +select timestamp64("148107-12-31T23:59:59Z"), cast(timestamp64("148107-12-31T23:59:59Z") as string); +select timestamp64("148107-12-31T23:59:59+0:1"), cast(timestamp64("148107-12-31T23:59:59+0:1") as string); + diff --git a/yql/essentials/tests/sql/suites/bigdate/default.cfg b/yql/essentials/tests/sql/suites/bigdate/default.cfg new file mode 100644 index 0000000000..fc68a75b1e --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/default.cfg @@ -0,0 +1,5 @@ +in Signed Signed.txt +in Unsigned Unsigned.txt +in NarrowDates NarrowDates.txt +in NarrowInterval NarrowInterval.txt +in BigDates BigDates.txt diff --git a/yql/essentials/tests/sql/suites/bigdate/explicit_cast.sql b/yql/essentials/tests/sql/suites/bigdate/explicit_cast.sql new file mode 100644 index 0000000000..d57c9c20ae --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/explicit_cast.sql @@ -0,0 +1,64 @@ +$date32_min = unwrap(cast(-53375809 as date32)); +$date32_max = unwrap(cast(53375807 as date32)); + +$datetime64_min = unwrap(cast(-4611669897600 as datetime64)); +$datetime64_max = unwrap(cast(4611669811199 as datetime64)); + +$timestamp64_min = unwrap(cast(-4611669897600000000 as timestamp64)); +$timestamp64_max = unwrap(cast(4611669811199999999 as timestamp64)); + +$date32_minus1 = unwrap(cast(-1 as date32)); +$datetime64_minus1 = unwrap(cast(-1 as datetime64)); +$timestamp64_minus1 = unwrap(cast(-1 as timestamp64)); + +-- scale up +select 1, cast($date32_minus1 as datetime64), cast($date32_min as datetime64), cast($date32_max as datetime64) +, 2, cast($date32_minus1 as timestamp64), cast($date32_min as timestamp64), cast($date32_max as timestamp64) +, 3, cast($datetime64_minus1 as timestamp64), cast($datetime64_min as timestamp64), cast($datetime64_max as timestamp64); + +-- scale down +select 1, cast($timestamp64_minus1 as datetime64), cast($timestamp64_min as datetime64), cast($timestamp64_max as datetime64) +, 2, cast($timestamp64_minus1 as date32), cast($timestamp64_min as date32), cast($timestamp64_max as date32) +, 3, cast($datetime64_minus1 as date32), cast($datetime64_min as date32), cast($datetime64_max as date32); + +$date_max_value = 49673l; +$date_max = unwrap(cast($date_max_value - 1 as date)); +$datetime_max = unwrap(cast($date_max_value*86400 - 1 as datetime)); +$timestamp_max = unwrap(cast($date_max_value*86400*1000000 - 1 as timestamp)); +$interval_min = unwrap(cast(-$date_max_value*86400*1000000 + 1 as interval)); +$interval_max = unwrap(cast($date_max_value*86400*1000000 - 1 as interval)); + +-- narrow to bigdate +select 1, cast($date_max as date32), cast($date_max as datetime64), cast($date_max as timestamp64) +, 2 , cast($datetime_max as date32), cast($datetime_max as datetime64), cast($datetime_max as timestamp64) +, 3, cast($timestamp_max as date32), cast($timestamp_max as datetime64), cast($timestamp_max as timestamp64) +, 4, cast($interval_min as interval64), cast($interval_max as interval64); + +$date32_val = unwrap(cast($date_max_value - 1 as date32)); +$datetime64_val = unwrap(cast($date_max_value*86400 - 1 as datetime64)); +$timestamp64_val = unwrap(cast($date_max_value*86400*1000000 - 1 as timestamp64)); +$interval64_val_min = unwrap(cast(-$date_max_value*86400*1000000 + 1 as interval64)); +$interval64_val_max = unwrap(cast($date_max_value*86400*1000000 - 1 as interval64)); + +-- bigdate to narrow +select 1, cast($date32_val as date), cast($date32_val as datetime), cast($date32_val as timestamp) +, 2, cast($datetime64_val as date), cast($datetime64_val as datetime), cast($datetime64_val as timestamp) +, 3, cast($timestamp64_val as date), cast($timestamp64_val as datetime), cast($timestamp64_val as timestamp) +, 4, cast($interval64_val_min as interval), cast($interval64_val_max as interval); + +select 1, cast($date32_minus1 as date), cast($date32_minus1 as datetime), cast($date32_minus1 as timestamp) +, 2, cast($datetime64_minus1 as date), cast($datetime64_minus1 as datetime), cast($datetime64_minus1 as timestamp) +, 3, cast($timestamp64_minus1 as date), cast($timestamp64_minus1 as datetime), cast($timestamp64_minus1 as timestamp); + +-- bigdate to narrow out of range + +$date32_big_val = unwrap(cast($date_max_value as date32)); +$datetime64_big_val = unwrap(cast($date_max_value*86400 as datetime64)); +$timestamp64_big_val = unwrap(cast($date_max_value*86400*1000000 as timestamp64)); +$interval64_big_val_min = unwrap(cast(-$date_max_value*86400*1000000 as interval64)); +$interval64_big_val_max = unwrap(cast($date_max_value*86400*1000000 as interval64)); + +select 1, cast($date32_big_val as date), cast($date32_big_val as datetime), cast($date32_big_val as timestamp) +, 2, cast($datetime64_big_val as date), cast($datetime64_big_val as datetime), cast($datetime64_big_val as timestamp) +, 3, cast($timestamp64_big_val as date), cast($timestamp64_big_val as datetime), cast($timestamp64_big_val as timestamp) +, 4, cast($interval64_big_val_min as interval), cast($interval64_big_val_max as interval); diff --git a/yql/essentials/tests/sql/suites/bigdate/implicit_cast_callable.sql b/yql/essentials/tests/sql/suites/bigdate/implicit_cast_callable.sql new file mode 100644 index 0000000000..005e686135 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/implicit_cast_callable.sql @@ -0,0 +1,43 @@ +/* postgres can not */ +use plato; + +$identity = ($x)-> { return $x }; + +$idDate32 = Callable(Callable<(date32)->date32>, $identity); +$idDatetime64 = Callable(Callable<(datetime64)->datetime64>, $identity); +$idTimestamp64 = Callable(Callable<(timestamp64)->timestamp64>, $identity); +$idInterval64 = Callable(Callable<(interval64)->interval64>, $identity); + +$valDate = unwrap(cast(1 as date)); +$valDate32 = unwrap(cast(-1 as date32)); +$valDatetime = unwrap(cast(86400 as datetime)); +$valDatetime64 = unwrap(cast(-86400 as datetime64)); +$valTimestamp = unwrap(cast(86400l*1000000 as timestamp)); +$valTimestamp64 = unwrap(cast(86400l*1000000 as timestamp64)); +$valInterval = unwrap(cast(1 as interval)); +$valInterval64 = unwrap(cast(-1 as interval64)); + +select 1, $idDate32($valDate), $idDate32($valDate32) +, 2, $idDatetime64($valDate), $idDatetime64($valDate32) +, $idDatetime64($valDatetime), $idDatetime64($valDatetime64) +, 3, $idTimestamp64($valDate), $idTimestamp64($valDate32) +, $idTimestamp64($valDatetime), $idTimestamp64($valDatetime64) +, $idTimestamp64($valTimestamp), $idTimestamp64($valTimestamp64) +, 4, $idInterval64($valInterval), $idInterval64($valInterval64); + +select row +, 1, $idTimestamp64(d32), $idDatetime64(d32), $idDate32(d32) +, 2, $idTimestamp64(dt64), $idDatetime64(dt64) +, 3, $idTimestamp64(ts64) +, 4, $idInterval64(i64) +from BigDates +order by row; + +select row +, 1, $idTimestamp64(d), $idDatetime64(d), $idDate32(d) +, 2, $idTimestamp64(dt), $idDatetime64(dt) +, 3, $idTimestamp64(ts) +from NarrowDates +order by row; + +select row, $idInterval64(i) from NarrowInterval order by row; diff --git a/yql/essentials/tests/sql/suites/bigdate/input_date32.sql b/yql/essentials/tests/sql/suites/bigdate/input_date32.sql new file mode 100644 index 0000000000..44e7f22b24 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/input_date32.sql @@ -0,0 +1,15 @@ +select 1, cast("-144170-12-31" as date32), cast(cast("-144170-12-31" as date32) as string); +select 2, cast("-144169-01-01" as date32), cast(cast("-144169-01-01" as date32) as string); +select 3, cast("-1-1-1" as date32), cast(cast("-1-1-1" as date32) as string); +select 4, cast("0-1-1" as date32), cast(cast("0-1-1" as date32) as string); +select 5, cast("1-1-1" as date32), cast(cast("1-1-1" as date32) as string); +select 6, cast("1-02-29" as date32), cast(cast("1-02-29" as date32) as string); +select 7, cast("1969-12-31" as date32), cast(cast("1969-12-31" as date32) as string); +select 8, cast("1970-01-01" as date32), cast(cast("1970-01-01" as date32) as string); +select 9, cast("2000-04-05" as date32), cast(cast("2000-04-05" as date32) as string); +select 10, cast("2100-02-29" as date32), cast(cast("2100-02-29" as date32) as string); +select 11, cast("2100-03-01" as date32), cast(cast("2100-03-01" as date32) as string); +select 12, cast("2105-12-31" as date32), cast(cast("2105-12-31" as date32) as string); +select 13, cast("2106-01-01" as date32), cast(cast("2106-01-01" as date32) as string); +select 14, cast("148107-12-31" as date32), cast(cast("148107-12-31" as date32) as string); +select 15, cast("148108-01-01" as date32), cast(cast("148108-01-01" as date32) as string); diff --git a/yql/essentials/tests/sql/suites/bigdate/input_datetime64.sql b/yql/essentials/tests/sql/suites/bigdate/input_datetime64.sql new file mode 100644 index 0000000000..1d008ec167 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/input_datetime64.sql @@ -0,0 +1,28 @@ +select cast("-144170-12-31T23:59:59Z" as timestamp64), cast(cast("-144170-12-31T23:59:59Z" as timestamp64) as string); +select cast("-144170-12-31T23:59:59-0:1" as timestamp64), cast(cast("-144170-12-31T23:59:59-0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00+0:1" as timestamp64), cast(cast("-144169-01-01T00:00:00+0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00-0:1" as timestamp64), cast(cast("-144169-01-01T00:00:00-0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00Z" as timestamp64), cast(cast("-144169-01-01T00:00:00Z" as timestamp64) as string); + +select cast("-1-1-1T00:00:00Z" as timestamp64), cast(cast("-1-1-1T00:00:00Z" as timestamp64) as string); +select cast("0-1-1T00:00:00Z" as timestamp64), cast(cast("0-1-1T00:00:00Z" as timestamp64) as string); +select cast("1-1-1T00:00:00Z" as timestamp64), cast(cast("1-1-1T00:00:00Z" as timestamp64) as string); +select cast("1-02-29T00:00:00Z" as timestamp64), cast(cast("1-02-29T00:00:00Z" as timestamp64) as string); + +select cast("1969-12-31T00:00:00Z" as timestamp64), cast(cast("1969-12-31T00:00:00Z" as timestamp64) as string); +select cast("1969-12-31T23:59:59-0:1" as timestamp64), cast(cast("1969-12-31T23:59:59-0:1" as timestamp64) as string); +select cast("1970-01-01T00:00:00Z" as timestamp64), cast(cast("1970-01-01T00:00:00Z" as timestamp64) as string); +select cast("1970-01-01T00:00:00+0:1" as timestamp64), cast(cast("1970-01-01T00:00:00+0:1" as timestamp64) as string); + +select cast("2000-04-05T00:00:00Z" as timestamp64), cast(cast("2000-04-05T00:00:00Z" as timestamp64) as string); +select cast("2100-02-29T00:00:00Z" as timestamp64), cast(cast("2100-02-29T00:00:00Z" as timestamp64) as string); +select cast("2100-03-01T00:00:00Z" as timestamp64), cast(cast("2100-03-01T00:00:00Z" as timestamp64) as string); +select cast("2105-12-31T00:00:00Z" as timestamp64), cast(cast("2105-12-31T00:00:00Z" as timestamp64) as string); +select cast("2106-01-01T00:00:00Z" as timestamp64), cast(cast("2106-01-01T00:00:00Z" as timestamp64) as string); + +select cast("148107-12-31T23:59:59Z" as timestamp64), cast(cast("148107-12-31T23:59:59Z" as timestamp64) as string); +select cast("148107-12-31T23:59:59-0:1" as timestamp64), cast(cast("148107-12-31T23:59:59-0:1" as timestamp64) as string); +select cast("148107-12-31T23:59:59+0:1" as timestamp64), cast(cast("148107-12-31T23:59:59+0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00-0:1" as timestamp64), cast(cast("148108-01-01T00:00:00-0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00+0:1" as timestamp64), cast(cast("148108-01-01T00:00:00+0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00Z" as timestamp64), cast(cast("148108-01-01T00:00:00Z" as timestamp64) as string); diff --git a/yql/essentials/tests/sql/suites/bigdate/input_interval64.sql b/yql/essentials/tests/sql/suites/bigdate/input_interval64.sql new file mode 100644 index 0000000000..47627b6003 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/input_interval64.sql @@ -0,0 +1,45 @@ +select cast("-P106751616DT23H59M59.999999S" as interval64), cast(cast("-P106751616DT23H59M59.999999S" as interval64) as string); +select cast("P106751616DT23H59M59.999999S" as interval64), cast(cast("P106751616DT23H59M59.999999S" as interval64) as string); + +select cast("-P106751616DT23H59M60S" as interval64), cast(cast("-P106751616DT23H59M60S" as interval64) as string); +select cast("P106751616DT23H59M60S" as interval64), cast(cast("P106751616DT23H59M60S" as interval64) as string); + +select cast("P000000000DT00H00M00.000000S" as interval64), cast(cast("P000000000DT00H00M00.000000S" as interval64) as string); +select cast("PT0S" as interval64), cast(cast("PT0S" as interval64) as string); +select cast("-PT0S" as interval64), cast(cast("-PT0S" as interval64) as string); +select cast("PT0.000001S" as interval64), cast(cast("PT0.000001S" as interval64) as string); +select cast("-PT0.000001S" as interval64), cast(cast("-PT0.000001S" as interval64) as string); + +select cast("PT0S" as interval64), cast(cast("PT0S" as interval64) as string); +select cast("PT0M" as interval64), cast(cast("PT0M" as interval64) as string); +select cast("PT0H" as interval64), cast(cast("PT0H" as interval64) as string); +select cast("P0D" as interval64), cast(cast("P0D" as interval64) as string); +select cast("P0W" as interval64), cast(cast("P0W" as interval64) as string); + +select cast("PT999999S" as interval64), cast(cast("PT999999S" as interval64) as string); +select cast("PT999999M" as interval64), cast(cast("PT999999M" as interval64) as string); +select cast("PT999999H" as interval64), cast(cast("PT999999H" as interval64) as string); + +select cast("P106751616D" as interval64), cast(cast("P106751616D" as interval64) as string); +select cast("P106751617D" as interval64), cast(cast("P106751617D" as interval64) as string); +select cast("P15250230W" as interval64), cast(cast("P15250230W" as interval64) as string); +select cast("P15250231W" as interval64), cast(cast("P15250231W" as interval64) as string); + +select cast("PT0000000S" as interval64), cast(cast("PT0000000S" as interval64) as string); +select cast("PT0000000M" as interval64), cast(cast("PT0000000M" as interval64) as string); +select cast("PT0000000H" as interval64), cast(cast("PT0000000H" as interval64) as string); +select cast("P0000000000D" as interval64), cast(cast("P0000000000D" as interval64) as string); +select cast("P0000000000W" as interval64), cast(cast("P0000000000W" as interval64) as string); + +select cast("PT1S" as interval64), cast(cast("PT1S" as interval64) as string); +select cast("PT1M" as interval64), cast(cast("PT1M" as interval64) as string); +select cast("PT1H" as interval64), cast(cast("PT1H" as interval64) as string); +select cast("P1D" as interval64), cast(cast("P1D" as interval64) as string); +select cast("P1W" as interval64), cast(cast("P1W" as interval64) as string); + +select cast("-PT1S" as interval64), cast(cast("-PT1S" as interval64) as string); +select cast("-PT1M" as interval64), cast(cast("-PT1M" as interval64) as string); +select cast("-PT1H" as interval64), cast(cast("-PT1H" as interval64) as string); +select cast("-P1D" as interval64), cast(cast("-P1D" as interval64) as string); +select cast("-P1W" as interval64), cast(cast("-P1W" as interval64) as string); + diff --git a/yql/essentials/tests/sql/suites/bigdate/input_timestamp64.sql b/yql/essentials/tests/sql/suites/bigdate/input_timestamp64.sql new file mode 100644 index 0000000000..f267080659 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/input_timestamp64.sql @@ -0,0 +1,65 @@ +-- +-- with microseconds +-- + +select cast("-144170-12-31T23:59:59.999999Z" as timestamp64), cast(cast("-144170-12-31T23:59:59.999999Z" as timestamp64) as string); +select cast("-144170-12-31T23:59:59.999999-0:1" as timestamp64), cast(cast("-144170-12-31T23:59:59.999999-0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00.000001+0:1" as timestamp64), cast(cast("-144169-01-01T00:00:00.000001+0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00.000001-0:1" as timestamp64), cast(cast("-144169-01-01T00:00:00.000001-0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00.000001Z" as timestamp64), cast(cast("-144169-01-01T00:00:00.000001Z" as timestamp64) as string); + +select cast("-1-1-1T00:00:00.000001Z" as timestamp64), cast(cast("-1-1-1T00:00:00.000001Z" as timestamp64) as string); +select cast("0-1-1T00:00:00.000001Z" as timestamp64), cast(cast("0-1-1T00:00:00.000001Z" as timestamp64) as string); +select cast("1-1-1T00:00:00.000001Z" as timestamp64), cast(cast("1-1-1T00:00:00.000001Z" as timestamp64) as string); +select cast("1-02-29T00:00:00.000001Z" as timestamp64), cast(cast("1-02-29T00:00:00.000001Z" as timestamp64) as string); + +select cast("1969-12-31T00:00:00.000001Z" as timestamp64), cast(cast("1969-12-31T00:00:00.000001Z" as timestamp64) as string); +select cast("1969-12-31T23:59:59.999999-0:1" as timestamp64), cast(cast("1969-12-31T23:59:59.999999-0:1" as timestamp64) as string); +select cast("1970-01-01T00:00:00.000001Z" as timestamp64), cast(cast("1970-01-01T00:00:00.000001Z" as timestamp64) as string); +select cast("1970-01-01T00:00:00.000001+0:1" as timestamp64), cast(cast("1970-01-01T00:00:00.000001+0:1" as timestamp64) as string); + +select cast("2000-04-05T00:00:00.000001Z" as timestamp64), cast(cast("2000-04-05T00:00:00.000001Z" as timestamp64) as string); +select cast("2100-02-29T00:00:00.000001Z" as timestamp64), cast(cast("2100-02-29T00:00:00.000001Z" as timestamp64) as string); +select cast("2100-03-01T00:00:00.000001Z" as timestamp64), cast(cast("2100-03-01T00:00:00.000001Z" as timestamp64) as string); +select cast("2105-12-31T00:00:00.000001Z" as timestamp64), cast(cast("2105-12-31T00:00:00.000001Z" as timestamp64) as string); +select cast("2106-01-01T00:00:00.000001Z" as timestamp64), cast(cast("2106-01-01T00:00:00.000001Z" as timestamp64) as string); + +select cast("148107-12-31T23:59:59.999999Z" as timestamp64), cast(cast("148107-12-31T23:59:59.999999Z" as timestamp64) as string); +select cast("148107-12-31T23:59:59.999999-0:1" as timestamp64), cast(cast("148107-12-31T23:59:59.999999-0:1" as timestamp64) as string); +select cast("148107-12-31T23:59:59.999999+0:1" as timestamp64), cast(cast("148107-12-31T23:59:59.999999+0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00.000001-0:1" as timestamp64), cast(cast("148108-01-01T00:00:00.000001-0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00.000001+0:1" as timestamp64), cast(cast("148108-01-01T00:00:00.000001+0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00.000001Z" as timestamp64), cast(cast("148108-01-01T00:00:00.000001Z" as timestamp64) as string); + +-- +-- without microseconds (like in timestamp64) +-- + +select cast("-144170-12-31T23:59:59Z" as timestamp64), cast(cast("-144170-12-31T23:59:59Z" as timestamp64) as string); +select cast("-144170-12-31T23:59:59-0:1" as timestamp64), cast(cast("-144170-12-31T23:59:59-0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00+0:1" as timestamp64), cast(cast("-144169-01-01T00:00:00+0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00-0:1" as timestamp64), cast(cast("-144169-01-01T00:00:00-0:1" as timestamp64) as string); +select cast("-144169-01-01T00:00:00Z" as timestamp64), cast(cast("-144169-01-01T00:00:00Z" as timestamp64) as string); + +select cast("-1-1-1T00:00:00Z" as timestamp64), cast(cast("-1-1-1T00:00:00Z" as timestamp64) as string); +select cast("0-1-1T00:00:00Z" as timestamp64), cast(cast("0-1-1T00:00:00Z" as timestamp64) as string); +select cast("1-1-1T00:00:00Z" as timestamp64), cast(cast("1-1-1T00:00:00Z" as timestamp64) as string); +select cast("1-02-29T00:00:00Z" as timestamp64), cast(cast("1-02-29T00:00:00Z" as timestamp64) as string); + +select cast("1969-12-31T00:00:00Z" as timestamp64), cast(cast("1969-12-31T00:00:00Z" as timestamp64) as string); +select cast("1969-12-31T23:59:59-0:1" as timestamp64), cast(cast("1969-12-31T23:59:59-0:1" as timestamp64) as string); +select cast("1970-01-01T00:00:00Z" as timestamp64), cast(cast("1970-01-01T00:00:00Z" as timestamp64) as string); +select cast("1970-01-01T00:00:00+0:1" as timestamp64), cast(cast("1970-01-01T00:00:00+0:1" as timestamp64) as string); + +select cast("2000-04-05T00:00:00Z" as timestamp64), cast(cast("2000-04-05T00:00:00Z" as timestamp64) as string); +select cast("2100-02-29T00:00:00Z" as timestamp64), cast(cast("2100-02-29T00:00:00Z" as timestamp64) as string); +select cast("2100-03-01T00:00:00Z" as timestamp64), cast(cast("2100-03-01T00:00:00Z" as timestamp64) as string); +select cast("2105-12-31T00:00:00Z" as timestamp64), cast(cast("2105-12-31T00:00:00Z" as timestamp64) as string); +select cast("2106-01-01T00:00:00Z" as timestamp64), cast(cast("2106-01-01T00:00:00Z" as timestamp64) as string); + +select cast("148107-12-31T23:59:59Z" as timestamp64), cast(cast("148107-12-31T23:59:59Z" as timestamp64) as string); +select cast("148107-12-31T23:59:59-0:1" as timestamp64), cast(cast("148107-12-31T23:59:59-0:1" as timestamp64) as string); +select cast("148107-12-31T23:59:59+0:1" as timestamp64), cast(cast("148107-12-31T23:59:59+0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00-0:1" as timestamp64), cast(cast("148108-01-01T00:00:00-0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00+0:1" as timestamp64), cast(cast("148108-01-01T00:00:00+0:1" as timestamp64) as string); +select cast("148108-01-01T00:00:00Z" as timestamp64), cast(cast("148108-01-01T00:00:00Z" as timestamp64) as string); diff --git a/yql/essentials/tests/sql/suites/bigdate/int_cast.sql b/yql/essentials/tests/sql/suites/bigdate/int_cast.sql new file mode 100644 index 0000000000..f2d796a961 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/int_cast.sql @@ -0,0 +1,53 @@ +$date32_min = unwrap(cast(-53375809 as date32)); +$date32_max = unwrap(cast(53375807 as date32)); + +$datetime64_min = unwrap(cast(-4611669897600 as datetime64)); +$datetime64_max = unwrap(cast(4611669811199 as datetime64)); + +$timestamp64_min = unwrap(cast(-4611669897600000000 as timestamp64)); +$timestamp64_max = unwrap(cast(4611669811199999999 as timestamp64)); + +$interval64_min = unwrap(cast(-9223339708799999999 as interval64)); +$interval64_max = unwrap(cast(9223339708799999999 as interval64)); + +$date32_minus1 = unwrap(cast(-1 as date32)); +$datetime64_minus1 = unwrap(cast(-1 as datetime64)); +$timestamp64_minus1 = unwrap(cast(-1 as timestamp64)); +$interval64_minus1 = unwrap(cast(-1 as interval64)); + +-- to signed +select 1, $date32_minus1, cast($date32_minus1 as int8), cast($date32_minus1 as int16), cast($date32_minus1 as int32), cast($date32_minus1 as int64) +, 2, $datetime64_minus1, cast($datetime64_minus1 as int8), cast($datetime64_minus1 as int16), cast($datetime64_minus1 as int32), cast($datetime64_minus1 as int64) +, 3, $timestamp64_minus1, cast($timestamp64_minus1 as int8), cast($timestamp64_minus1 as int16), cast($timestamp64_minus1 as int32), cast($timestamp64_minus1 as int64) +, 4, $interval64_minus1, cast($interval64_minus1 as int8), cast($interval64_minus1 as int16), cast($interval64_minus1 as int32), cast($interval64_minus1 as int64); + +-- to unsigned +select 1, cast($date32_minus1 as uint32), cast($date32_minus1 as uint64) +, 2, cast($datetime64_minus1 as uint32), cast($datetime64_minus1 as uint64) +, 3, cast($timestamp64_minus1 as uint32), cast($timestamp64_minus1 as uint64) +, 4, cast($interval64_minus1 as uint32), cast($interval64_minus1 as uint64); + +-- min/max values +select 1, $date32_min, cast($date32_min as int32) +, 2, $datetime64_min, cast($datetime64_min as int64) +, 3, $timestamp64_min, cast($timestamp64_min as int64) +, 4, $interval64_min, cast($interval64_min as int64) +, 5, $date32_max, cast($date32_max as int32) +, 6, $datetime64_max, cast($datetime64_max as int64) +, 7, $timestamp64_max, cast($timestamp64_max as int64) +, 8, $interval64_max, cast($interval64_max as int64); + +-- out of range +select 1, cast(-53375810 as date32), cast(53375808 as date32) +, 2, cast(-4611669897601 as datetime64), cast(4611669811200 as datetime64) +, 3, cast(-4611669897600000001 as timestamp64), cast(4611669811200000000 as timestamp64) +, 4, cast(-9223339708800000000 as interval64), cast(9223339708800000000 as interval64); + +-- insufficient int size +select 1, cast(unwrap(cast(32768 as date32)) as int16), cast(unwrap(cast(65536 as date32)) as uint16) +, 2, cast(unwrap(cast(32768 as datetime64)) as int16), cast(unwrap(cast(2147483648 as datetime64)) as int32) +, 3, cast(unwrap(cast(65536 as datetime64)) as uint16), cast(unwrap(cast(4294967296 as datetime64)) as uint32) +, 4, cast(unwrap(cast(32768 as timestamp64)) as int16), cast(unwrap(cast(2147483648 as timestamp64)) as int32) +, 5, cast(unwrap(cast(65536 as timestamp64)) as uint16), cast(unwrap(cast(4294967296 as timestamp64)) as uint32) +, 6, cast(unwrap(cast(32768 as interval64)) as int16), cast(unwrap(cast(2147483648 as interval64)) as int32) +, 7, cast(unwrap(cast(65536 as interval64)) as uint16), cast(unwrap(cast(4294967296 as interval64)) as uint32); diff --git a/yql/essentials/tests/sql/suites/bigdate/int_literals.sql b/yql/essentials/tests/sql/suites/bigdate/int_literals.sql new file mode 100644 index 0000000000..9b3c2edfc3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/int_literals.sql @@ -0,0 +1,14 @@ +pragma warning("disable","4510"); +select + 3, Yql::Date32(AsAtom("0")), + 4, Yql::Datetime64(AsAtom("0")), + 5, Yql::Timestamp64(AsAtom("0")), + 6, Yql::Interval64(AsAtom("0")), + 7, Yql::Date32(AsAtom("-53375809")), + 8, Yql::Date32(AsAtom("53375807")), + 9, Yql::Datetime64(AsAtom("-4611669897600")), + 10, Yql::Datetime64(AsAtom("4611669811199")), + 11, Yql::Timestamp64(AsAtom("-4611669897600000000")), + 12, Yql::Timestamp64(AsAtom("4611669811199999999")), + 13, Yql::Interval64(AsAtom("-9223339708799999999")), + 14, Yql::Interval64(AsAtom("9223339708799999999")); diff --git a/yql/essentials/tests/sql/suites/bigdate/misc.sql b/yql/essentials/tests/sql/suites/bigdate/misc.sql new file mode 100644 index 0000000000..510a8dc6d6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/misc.sql @@ -0,0 +1,27 @@ +pragma warning("disable","4510"); + +$date32_min = unwrap(cast(-53375809 as date32)); +$date32_max = unwrap(cast(53375807 as date32)); +$datetime64_min = unwrap(cast(-4611669897600 as datetime64)); +$datetime64_max = unwrap(cast(4611669811199 as datetime64)); +$timestamp64_min = unwrap(cast(-4611669897600000000 as timestamp64)); +$timestamp64_max = unwrap(cast(4611669811199999999 as timestamp64)); +$interval64_min = unwrap(cast(-9223339708799999999 as interval64)); +$interval64_max = unwrap(cast(9223339708799999999 as interval64)); + +select 1, ListFromRange(date32("1969-12-30"), date32("1970-1-5")) +, 2, ListFromRange(date32("1970-1-3"), date32("1969-12-30")) +, 3, ListFromRange(date32("1969-12-30"), date32("1970-1-5"), interval("P2D")) +, 4, ListFromRange(date32("1969-12-30"), date32("1970-1-5"), interval64("P2D")) +, 5, ListFromRange(date32("1970-1-5"), date32("1969-12-30")) +, 6, ListFromRange(date32("1970-1-5"), date32("1969-12-30"), interval("P2D")) +, 7, ListFromRange(date32("1970-1-5"), date32("1969-12-29"), interval("-P2D")) +, 8, ListFromRange(datetime64("1969-12-31T23:59:57Z"), datetime64("1970-1-1T0:0:3Z")) +, 9, ListFromRange(datetime64("1969-12-31T23:59:57Z"), datetime64("1970-1-1T0:0:3Z"), interval("PT2S")) +, 10, ListFromRange(datetime64("1969-12-31T23:59:57Z"), datetime64("1970-1-1T0:0:3Z"), interval64("PT2S")) +, 11, ListFromRange(timestamp64("1969-12-31T23:59:57Z"), timestamp64("1970-1-1T0:0:3Z"), interval("PT2.5S")) +, 12, ListFromRange($date32_min, $date32_max, interval64("P53375808D")) +, 13, ListFromRange($datetime64_min, $datetime64_max, interval64("P53375808D")) +, 14, ListFromRange($timestamp64_min, $timestamp64_max, interval64("P53375808D")) +, 15, ListFromRange($interval64_min, $interval64_max, interval64("P53375808D")) +; diff --git a/yql/essentials/tests/sql/suites/bigdate/output_date32.sql b/yql/essentials/tests/sql/suites/bigdate/output_date32.sql new file mode 100644 index 0000000000..2de16e8bb1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/output_date32.sql @@ -0,0 +1,10 @@ +select cast(cast(-53375810 as date32) as string) +, 2, cast(cast(-53375809 as date32) as string) +, 3, cast(cast(-1 as date32) as string) +, 4, cast(cast(0 as date32) as string) +, 5, cast(cast(1 as date32) as string) +, 6, cast(cast(18000u as date32) as string) +, 7, cast(cast(49673u - 1u as date32) as string) +, 8, cast(cast(49673u as date32) as string) +, 9, cast(cast(53375807ul as date32) as string) +, 10, cast(cast(53375808ul as date32) as string); diff --git a/yql/essentials/tests/sql/suites/bigdate/output_datetime64.sql b/yql/essentials/tests/sql/suites/bigdate/output_datetime64.sql new file mode 100644 index 0000000000..449906ad81 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/output_datetime64.sql @@ -0,0 +1,13 @@ +select cast(cast(-4611669897601 as datetime64) as string) +, cast(cast(-4611669897600 as datetime64) as string) +, cast(cast(-86401 as datetime64) as string) +, cast(cast(-86400 as datetime64) as string) +, cast(cast(-86399 as datetime64) as string) +, cast(cast(-1 as datetime64) as string) +, cast(cast(0 as datetime64) as string) +, cast(cast(1 as datetime64) as string) +, cast(cast(86399 as datetime64) as string) +, cast(cast(86400 as datetime64) as string) +, cast(cast(86401 as datetime64) as string) +, cast(cast(4611669811199 as datetime64) as string) +, cast(cast(4611669811200 as datetime64) as string)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/bigdate/output_interval64.sql b/yql/essentials/tests/sql/suites/bigdate/output_interval64.sql new file mode 100644 index 0000000000..c64be2ef58 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/output_interval64.sql @@ -0,0 +1,11 @@ +select cast(cast(-9223339708800000000 as interval64) as string) +, cast(cast(-9223339708799999999 as interval64) as string) +, cast(cast(-1 as interval64) as string) +, cast(cast(0 as interval64) as string) +, cast(cast(1 as interval64) as string) +, cast(cast(1000000 as interval64) as string) +, cast(cast(60000000 as interval64) as string) +, cast(cast(3600000000 as interval64) as string) +, cast(cast(86400000000 as interval64) as string) +, cast(cast(9223339708799999999 as interval64) as string) +, cast(cast(9223339708800000000 as interval64) as string);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/bigdate/output_timestamp64.sql b/yql/essentials/tests/sql/suites/bigdate/output_timestamp64.sql new file mode 100644 index 0000000000..d710e3976b --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/output_timestamp64.sql @@ -0,0 +1,13 @@ +select cast(cast(-4611669897601 as timestamp64) as string) +, cast(cast(-4611669897600 as timestamp64) as string) +, cast(cast(-86401 as timestamp64) as string) +, cast(cast(-86400 as timestamp64) as string) +, cast(cast(-86399 as timestamp64) as string) +, cast(cast(-1 as timestamp64) as string) +, cast(cast(0 as timestamp64) as string) +, cast(cast(1 as timestamp64) as string) +, cast(cast(86399 as timestamp64) as string) +, cast(cast(86400 as timestamp64) as string) +, cast(cast(86401 as timestamp64) as string) +, cast(cast(4611669811199 as timestamp64) as string) +, cast(cast(4611669811200 as timestamp64) as string)
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/bigdate/presort.sql b/yql/essentials/tests/sql/suites/bigdate/presort.sql new file mode 100644 index 0000000000..f0277dcf1d --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/presort.sql @@ -0,0 +1,25 @@ +pragma warning("disable","4510"); +$wa1 = Date32("1900-01-01"); +$wd1 = Datetime64("1900-01-01T02:03:04Z"); +$wt1 = Timestamp64("1900-01-01T02:03:04.567891Z"); + +$waz1 = TzDate32("1900-01-01,Europe/Moscow"); +$wdz1 = TzDatetime64("1900-01-01T02:03:04,Europe/Moscow"); +$wtz1 = TzTimestamp64("1900-01-01T02:03:04.567891,Europe/Moscow"); + +$wa2 = Date32("1901-01-01"); +$wd2 = Datetime64("1901-01-01T02:03:04Z"); +$wt2 = Timestamp64("1901-01-01T02:03:04.567891Z"); + +$waz2 = TzDate32("1901-01-01,Europe/Moscow"); +$wdz2 = TzDatetime64("1901-01-01T02:03:04,Europe/Moscow"); +$wtz2 = TzTimestamp64("1901-01-01T02:03:04.567891,Europe/Moscow"); + +select +cast(ListSortDesc([(Yql::Ascending($wa1),$wa1),(Yql::Ascending($wa2),$wa2)]) as List<Tuple<String,String>>), +cast(ListSortDesc([(Yql::Ascending($wd1),$wd1),(Yql::Ascending($wd2),$wd2)]) as List<Tuple<String,String>>), +cast(ListSortDesc([(Yql::Ascending($wt1),$wt1),(Yql::Ascending($wt2),$wt2)]) as List<Tuple<String,String>>), +cast(ListSortDesc([(Yql::Ascending($waz1),$waz1),(Yql::Ascending($waz2),$waz2)]) as List<Tuple<String,String>>), +cast(ListSortDesc([(Yql::Ascending($wdz1),$wdz1),(Yql::Ascending($wdz2),$wdz2)]) as List<Tuple<String,String>>), +cast(ListSortDesc([(Yql::Ascending($wtz1),$wtz1),(Yql::Ascending($wtz2),$wtz2)]) as List<Tuple<String,String>>); + diff --git a/yql/essentials/tests/sql/suites/bigdate/round.sql b/yql/essentials/tests/sql/suites/bigdate/round.sql new file mode 100644 index 0000000000..9e2830cc8b --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/round.sql @@ -0,0 +1,136 @@ +pragma warning("disable","4510"); + +$date_max_value = 49673l; +$date_max = unwrap(cast($date_max_value - 1 as date)); +$datetime_max = unwrap(cast($date_max_value*86400 - 1 as datetime)); +$timestamp_max = unwrap(cast($date_max_value*86400*1000000 - 1 as timestamp)); + +$date32_min = unwrap(cast(-53375809 as date32)); +$date32_max = unwrap(cast(53375807 as date32)); +$date32_plus1 = unwrap(cast(1 as date32)); +$datetime64_min = unwrap(cast(-4611669897600 as datetime64)); +$datetime64_max = unwrap(cast(4611669811199 as datetime64)); +$timestamp64_min = unwrap(cast(-4611669897600000000 as timestamp64)); +$timestamp64_max = unwrap(cast(4611669811199999999 as timestamp64)); + +$timestamp64_max_narrow = unwrap(cast($timestamp_max as timestamp64)); +$datetime64_max_narrow = unwrap(cast($datetime_max as datetime64)); + +$datetime64_zero = unwrap(cast(0 as datetime64)); +$timestamp64_zero = unwrap(cast(0 as timestamp64)); +$datetime64_minus1 = unwrap(cast(-1 as datetime64)); +$timestamp64_minus1 = unwrap(cast(-1 as timestamp64)); +$timestamp64_2xx31 = unwrap(cast(2147483648 as timestamp64)); + +$datetime64_day_ml = unwrap(cast(-86401 as datetime64)); +$datetime64_day_m = unwrap(cast(-86400 as datetime64)); +$datetime64_day_mr = unwrap(cast(-86399 as datetime64)); +$datetime64_day_pl = unwrap(cast(86399 as datetime64)); +$datetime64_day_p = unwrap(cast(86400 as datetime64)); +$datetime64_day_pr = unwrap(cast(86401 as datetime64)); + +$timestamp64_day_ml = unwrap(cast(-86400l*1000000 - 1 as timestamp64)); +$timestamp64_day_m = unwrap(cast(-86400l*1000000 as timestamp64)); +$timestamp64_day_mr = unwrap(cast(-86400l*1000000 + 1 as timestamp64)); +$timestamp64_day_pl = unwrap(cast(86400l*1000000 - 1 as timestamp64)); +$timestamp64_day_p = unwrap(cast(86400l*1000000 as timestamp64)); +$timestamp64_day_pr = unwrap(cast(86400l*1000000 + 1 as timestamp64)); + +-- bigdate to bigdate +select -4, Yql::RoundDown($datetime64_min, date32), Yql::RoundUp($datetime64_min, date32) +, Yql::RoundDown($timestamp64_min, date32), Yql::RoundUp($timestamp64_min, date32) +, Yql::RoundDown($timestamp64_min, datetime64), Yql::RoundUp($timestamp64_min, datetime64) + +, -3, Yql::RoundDown($datetime64_day_ml, date32), Yql::RoundUp($datetime64_day_ml, date32) +, Yql::RoundDown($timestamp64_day_ml, date32), Yql::RoundUp($timestamp64_day_ml, date32) +, Yql::RoundDown($timestamp64_day_ml, datetime64), Yql::RoundUp($timestamp64_day_ml, datetime64) + +, -2, Yql::RoundDown($datetime64_day_m, date32), Yql::RoundUp($datetime64_day_m, date32) +, Yql::RoundDown($timestamp64_day_m, date32), Yql::RoundUp($timestamp64_day_m, date32) +, Yql::RoundDown($timestamp64_day_m, datetime64), Yql::RoundUp($timestamp64_day_m, datetime64) + +, -1, Yql::RoundDown($datetime64_day_mr, date32), Yql::RoundUp($datetime64_day_mr, date32) +, Yql::RoundDown($timestamp64_day_mr, date32), Yql::RoundUp($timestamp64_day_mr, date32) +, Yql::RoundDown($timestamp64_day_mr, datetime64), Yql::RoundUp($timestamp64_day_mr, datetime64) + +, 0, Yql::RoundDown($datetime64_zero, date32), Yql::RoundUp($datetime64_zero, date32) +, Yql::RoundDown($timestamp64_zero, date32), Yql::RoundUp($timestamp64_zero, date32) +, Yql::RoundDown($timestamp64_zero, datetime64), Yql::RoundUp($timestamp64_zero, datetime64) + +, 1, Yql::RoundDown($datetime64_day_pl, date32), Yql::RoundUp($datetime64_day_pl, date32) +, Yql::RoundDown($timestamp64_day_pl, date32), Yql::RoundUp($timestamp64_day_pl, date32) +, Yql::RoundDown($timestamp64_day_pl, datetime64), Yql::RoundUp($timestamp64_day_pl, datetime64) + +, 2, Yql::RoundDown($datetime64_day_p, date32), Yql::RoundUp($datetime64_day_p, date32) +, Yql::RoundDown($timestamp64_day_p, date32), Yql::RoundUp($timestamp64_day_p, date32) +, Yql::RoundDown($timestamp64_day_p, datetime64), Yql::RoundUp($timestamp64_day_p, datetime64) + +, 3, Yql::RoundDown($datetime64_day_pr, date32), Yql::RoundUp($datetime64_day_pr, date32) +, Yql::RoundDown($timestamp64_day_pr, date32), Yql::RoundUp($timestamp64_day_pr, date32) +, Yql::RoundDown($timestamp64_day_pr, datetime64), Yql::RoundUp($timestamp64_day_pr, datetime64) + +, 4, Yql::RoundDown($datetime64_max, date32), Yql::RoundUp($datetime64_max, date32) +, Yql::RoundDown($timestamp64_max, date32), Yql::RoundUp($timestamp64_max, date32) +, Yql::RoundDown($timestamp64_max, datetime64), Yql::RoundUp($timestamp64_max, datetime64) +; + +-- bigdate to narrowdate +select -1, Yql::RoundDown($datetime64_minus1, date), Yql::RoundUp($datetime64_minus1, date) +, Yql::RoundDown($timestamp64_minus1, date), Yql::RoundUp($timestamp64_minus1, date) +, Yql::RoundDown($timestamp64_minus1, datetime), Yql::RoundUp($timestamp64_minus1, datetime) + +, 0, Yql::RoundDown($datetime64_zero, date), Yql::RoundUp($datetime64_zero, date) +, Yql::RoundDown($timestamp64_zero, date), Yql::RoundUp($timestamp64_zero, date) +, Yql::RoundDown($timestamp64_zero, datetime), Yql::RoundUp($timestamp64_zero, datetime) + +, 1, Yql::RoundDown($datetime64_day_pl, date), Yql::RoundUp($datetime64_day_pl, date) +, Yql::RoundDown($timestamp64_day_pl, date), Yql::RoundUp($timestamp64_day_pl, date) +, Yql::RoundDown($timestamp64_day_pl, datetime), Yql::RoundUp($timestamp64_day_pl, datetime) + +, 2, Yql::RoundDown($datetime64_day_p, date), Yql::RoundUp($datetime64_day_p, date) +, Yql::RoundDown($timestamp64_day_p, date), Yql::RoundUp($timestamp64_day_p, date) +, Yql::RoundDown($timestamp64_day_p, datetime), Yql::RoundUp($timestamp64_day_p, datetime) + +, 3, Yql::RoundDown($datetime64_day_pr, date), Yql::RoundUp($datetime64_day_pr, date) +, Yql::RoundDown($timestamp64_day_pr, date), Yql::RoundUp($timestamp64_day_pr, date) +, Yql::RoundDown($timestamp64_day_pr, datetime), Yql::RoundUp($timestamp64_day_pr, datetime) + +, 4, Yql::RoundDown($datetime64_max_narrow, date), Yql::RoundUp($datetime64_max_narrow, date) +, Yql::RoundDown($timestamp64_max_narrow, date), Yql::RoundUp($timestamp64_max_narrow, date) +, Yql::RoundDown($timestamp64_max_narrow, datetime), Yql::RoundUp($timestamp64_max_narrow, datetime) + +, 5, Yql::RoundDown($datetime64_max, date), Yql::RoundUp($datetime64_max, date) +, Yql::RoundDown($timestamp64_max, date), Yql::RoundUp($timestamp64_max, date) +, Yql::RoundDown($timestamp64_max, datetime), Yql::RoundUp($timestamp64_max, datetime) + +, 6, Yql::RoundDown($date32_plus1, date), Yql::RoundUp($date32_plus1, date) +, Yql::RoundDown($date32_plus1, datetime), Yql::RoundUp($date32_plus1, datetime) +, Yql::RoundDown($date32_plus1, timestamp), Yql::RoundUp($date32_plus1, timestamp) +; + +-- from narrowdate +select 0, Yql::RoundDown($datetime_max, date), Yql::RoundUp($datetime_max, date) +, Yql::RoundDown($datetime_max, date32), Yql::RoundUp($datetime_max, date32) +, 1, Yql::RoundDown($timestamp_max, date), Yql::RoundUp($timestamp_max, date) +, Yql::RoundDown($timestamp_max, date32), Yql::RoundUp($timestamp_max, date32) +, 2, Yql::RoundDown($timestamp_max, datetime), Yql::RoundUp($timestamp_max, datetime) +, Yql::RoundDown($timestamp_max, datetime64), Yql::RoundUp($timestamp_max, datetime64) +; + +select 0, Yql::RoundDown($timestamp64_2xx31, date32), Yql::RoundUp($timestamp64_2xx31, date32) +, Yql::RoundDown($timestamp64_2xx31, datetime64), Yql::RoundUp($timestamp64_2xx31, datetime64) + +, 1, Yql::RoundDown($date_max, date), Yql::RoundUp($date_max, date32) +, 2, Yql::RoundDown($date_max, datetime), Yql::RoundUp($date_max, datetime64) +, 3, Yql::RoundDown($date_max, timestamp), Yql::RoundUp($date_max, timestamp64) +, 4, Yql::RoundDown($datetime_max, datetime), Yql::RoundUp($datetime_max, datetime64) +, 5, Yql::RoundDown($datetime_max, timestamp), Yql::RoundUp($datetime_max, timestamp64) +, 6, Yql::RoundDown($timestamp_max, timestamp), Yql::RoundUp($timestamp_max, timestamp64) + +, 10, Yql::RoundDown($date32_min, date32), Yql::RoundUp($date32_max, date32) +, 11, Yql::RoundDown($date32_min, datetime64), Yql::RoundUp($date32_max, datetime64) +, 12, Yql::RoundDown($date32_min, timestamp64), Yql::RoundUp($date32_max, timestamp64) +, 13, Yql::RoundDown($datetime64_min, datetime64), Yql::RoundUp($datetime64_max, datetime64) +, 14, Yql::RoundDown($datetime64_min, timestamp64), Yql::RoundUp($datetime64_max, timestamp64) +, 15, Yql::RoundDown($timestamp64_min, timestamp64), Yql::RoundUp($timestamp64_max, timestamp64) +; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_arithmetic.sql b/yql/essentials/tests/sql/suites/bigdate/table_arithmetic.sql new file mode 100644 index 0000000000..9fb30acf44 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_arithmetic.sql @@ -0,0 +1,17 @@ +/* postgres can not */ +use plato; + +select row, -i64, +i64, abs(i64) +from BigDates +order by row; + +select min(d32), min(dt64), min(ts64), min(i64) +, max(d32), max(dt64), max(ts64), max(i64) +from BigDates; + +select l.row, r.row +, 1, l.d32 - r.i64, l.dt64 - r.i64, l.ts64 - r.i64, l.i64 - r.i64 +, 2, l.d32 + r.i64, l.dt64 + r.i64, l.ts64 + r.i64, l.i64 + r.i64 +from BigDates as l cross join BigDates as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_mul_div.sql b/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_mul_div.sql new file mode 100644 index 0000000000..1674243a00 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_mul_div.sql @@ -0,0 +1,35 @@ +/* postgres can not */ +use plato; + +select l.row, r.row +, 1, l.i64*i8, l.i64*i16, l.i64*i32, l.i64*r.i64 +, 2, i8*l.i64, i16*l.i64, i32*l.i64, r.i64*l.i64 +, 3, l.i64/i8, l.i64/i16, l.i64/i32, l.i64/r.i64 +from BigDates as l cross join Signed as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; + +select l.row, r.row +, 1, i64*ui8, i64*ui16, i64*ui32, i64*ui64 +, 2, ui8*i64, ui16*i64, ui32*i64, ui64*i64 +, 3, i64/ui8, i64/ui16, i64/ui32, i64/ui64 +from BigDates as l cross join Unsigned as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; + +select l.row, r.row +, 1, l.i*i8, l.i*i16, l.i*i32, l.i*r.i64 +, 2, i8*l.i, i16*l.i, i32*l.i, r.i64*l.i +, 3, l.i/i8, l.i/i16, l.i/i32, l.i/r.i64 +from NarrowInterval as l cross join Signed as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; + +select l.row, r.row +, 1, i*ui8, i*ui16, i*ui32, i*ui64 +, 2, ui8*i, ui16*i, ui32*i, ui64*i +, 3, i/ui8, i/ui16, i/ui32, i/ui64 +from NarrowInterval as l cross join Unsigned as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; + diff --git a/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_narrow.sql b/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_narrow.sql new file mode 100644 index 0000000000..7e86c038fd --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_narrow.sql @@ -0,0 +1,25 @@ +/* postgres can not */ +use plato; + +select l.row, r.row +, 1, l.d32 - r.d, l.d32 - r.dt, l.d32 - r.ts +, 2, l.dt64 - r.d, l.dt64 - r.dt, l.dt64 - r.ts +, 3, l.ts64 - r.d, l.ts64 - r.dt, l.ts64 - r.ts +from BigDates as l cross join NarrowDates as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; + +select l.row, r.row +, 1, l.d - r.d32, l.d - r.dt64, l.d - r.ts64, l.d - r.i64, l.d + r.i64 +, 2, l.dt - r.d32, l.dt - r.dt64, l.dt - r.ts64, l.dt - r.i64, l.dt + r.i64 +, 3, l.ts - r.d32, l.ts - r.dt64, l.ts - r.ts64, l.ts - r.i64, l.ts + r.i64 +from NarrowDates as l cross join BigDates as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; + +select l.row, r.row +, 1, l.d32 - r.i, l.dt64 - r.i, l.ts64 - r.i, l.i64 - r.i +, 2, l.d32 + r.i, l.dt64 + r.i, l.ts64 + r.i, l.i64 + r.i +from BigDates as l cross join NarrowInterval as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_sub.sql b/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_sub.sql new file mode 100644 index 0000000000..18c040d8b4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_arithmetic_sub.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +use plato; + +select l.row, r.row +, 1, l.d32 - r.d32, l.d32 - r.dt64, l.d32 - r.ts64 +, 2, l.dt64 - r.d32, l.dt64 - r.dt64, l.dt64 - r.ts64 +, 3, l.ts64 - r.d32, l.ts64 - r.dt64, l.ts64 - r.ts64 +from BigDates as l cross join BigDates as r +where abs(l.row) <= 7 and abs(r.row) <= 7 +order by l.row, r.row; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_common_type.sql b/yql/essentials/tests/sql/suites/bigdate/table_common_type.sql new file mode 100644 index 0000000000..f059db2f0a --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_common_type.sql @@ -0,0 +1,27 @@ +/* postgres can not */ +use plato; + +select bd.row as row +, 1, [d, dt] +, [d, ts] +, [d, d32] +, [d, dt64] +, [d, ts64] +, 2, [dt, ts] +, [dt, d32] +, [dt, dt64] +, [dt, ts64] +, 3, [ts, d32] +, [ts, dt64] +, [ts, ts64] +, 4, [d32, dt64] +, [d32, ts64] +, 5, [dt64, ts64] +from BigDates as bd +join NarrowDates using (row) +order by row; + +select bd.row as row, [i, i64] +from BigDates as bd +join NarrowInterval using (row) +order by row; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_explicit_cast.sql b/yql/essentials/tests/sql/suites/bigdate/table_explicit_cast.sql new file mode 100644 index 0000000000..263d90fbd2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_explicit_cast.sql @@ -0,0 +1,29 @@ +/* postgres can not */ +use plato; + +select row, cast(i as interval64) from NarrowInterval order by row; + +select row +, 1, cast(d as date), cast(d as datetime), cast(d as timestamp) +, 2, cast(dt as date), cast(dt as datetime), cast(dt as timestamp) +, 3, cast(ts as date), cast(ts as datetime), cast(ts as timestamp) +from NarrowDates order by row; + +select row +, 1, cast(d as date32), cast(d as datetime64), cast(d as timestamp64) +, 2, cast(dt as date32), cast(dt as datetime64), cast(dt as timestamp64) +, 3, cast(ts as date32), cast(ts as datetime64), cast(ts as timestamp64) +from NarrowDates order by row; + +select row +, 1, cast(d32 as date), cast(d32 as datetime), cast(d32 as timestamp) +, 2, cast(dt64 as date), cast(dt64 as datetime), cast(dt64 as timestamp) +, 3, cast(ts64 as date), cast(ts64 as datetime), cast(ts64 as timestamp) +, 4, cast(i64 as interval) +from BigDates order by row; + +select row +, 1, cast(d32 as datetime64), cast(d32 as timestamp64) +, 2, cast(dt64 as date32), cast(dt64 as timestamp64) +, 3, cast(ts64 as date32), cast(ts64 as datetime64) +from BigDates order by row; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_int_cast.sql b/yql/essentials/tests/sql/suites/bigdate/table_int_cast.sql new file mode 100644 index 0000000000..a6b772f62e --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_int_cast.sql @@ -0,0 +1,34 @@ +/* postgres can not */ +use plato; + +select row +, 1, cast(i8 as date32), cast(i8 as datetime64), cast(i8 as timestamp64), cast(i8 as interval64) +, 2, cast(i16 as date32), cast(i16 as datetime64), cast(i16 as timestamp64), cast(i16 as interval64) +, 3, cast(i32 as date32), cast(i32 as datetime64), cast(i32 as timestamp64), cast(i32 as interval64) +, 4, cast(i64 as date32), cast(i64 as datetime64), cast(i64 as timestamp64), cast(i64 as interval64) +from Signed +order by row; + +select row +, 1, cast(d32 as int8), cast(d32 as int16), cast(d32 as int32), cast(d32 as int64) +, 2, cast(dt64 as int8), cast(dt64 as int16), cast(dt64 as int32), cast(dt64 as int64) +, 3, cast(ts64 as int8), cast(ts64 as int16), cast(ts64 as int32), cast(ts64 as int64) +, 4, cast(i64 as int8), cast(i64 as int16), cast(i64 as int32), cast(i64 as int64) +from BigDates +order by row; + +select row +, 1, cast(d32 as uint8), cast(d32 as uint16), cast(d32 as uint32), cast(d32 as uint64) +, 2, cast(dt64 as uint8), cast(dt64 as uint16), cast(dt64 as uint32), cast(dt64 as uint64) +, 3, cast(ts64 as uint8), cast(ts64 as uint16), cast(ts64 as uint32), cast(ts64 as uint64) +, 4, cast(i64 as uint8), cast(i64 as uint16), cast(i64 as uint32), cast(i64 as uint64) +from BigDates +order by row; + +select row +, 1, cast(ui8 as date32), cast(ui8 as datetime64), cast(ui8 as timestamp64), cast(ui8 as interval64) +, 2, cast(ui16 as date32), cast(ui16 as datetime64), cast(ui16 as timestamp64), cast(ui16 as interval64) +, 3, cast(ui32 as date32), cast(ui32 as datetime64), cast(ui32 as timestamp64), cast(ui32 as interval64) +, 4, cast(ui64 as date32), cast(ui64 as datetime64), cast(ui64 as timestamp64), cast(ui64 as interval64) +from Unsigned +order by row; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_io.sql b/yql/essentials/tests/sql/suites/bigdate/table_io.sql new file mode 100644 index 0000000000..b3a3f2cd65 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_io.sql @@ -0,0 +1,17 @@ +/* postgres can not */ +use plato; + +select * from BigDates order by row; + +insert into @Output with truncate +select * from BigDates +where row > -100 +order by row; + +commit; + +select * from @Output order by row; + +select row, cast(d32 as string), cast(dt64 as string), cast(ts64 as string), cast(i64 as string) +from BigDates +order by row; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-default.cfg b/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-default.cfg new file mode 100644 index 0000000000..9256d900c4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-default.cfg @@ -0,0 +1,6 @@ +out OutDate32 OutDate32.txt +out OutDatetime64 OutDatetime64.txt +out OutTimestamp64 OutTimestamp64.txt +providers yt +canonize_yt + diff --git a/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-on.cfg b/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-on.cfg new file mode 100644 index 0000000000..be9c9911e3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-on.cfg @@ -0,0 +1,7 @@ +out OutDate32 OutDate32.txt +out OutDatetime64 OutDatetime64.txt +out OutTimestamp64 OutTimestamp64.txt +providers yt +canonize_yt +pragma yt.UseNativeYtTypes; +pragma yt.NativeYtTypeCompatibility = "date;bigdate"; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-wo_compat.cfg b/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-wo_compat.cfg new file mode 100644 index 0000000000..0f936de55c --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter-wo_compat.cfg @@ -0,0 +1,8 @@ +out OutDate32 OutDate32.txt +out OutDatetime64 OutDatetime64.txt +out OutTimestamp64 OutTimestamp64.txt +providers yt +canonize_yt +pragma yt.UseNativeYtTypes; +pragma yt.NativeYtTypeCompatibility = "void"; + diff --git a/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter.sql b/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter.sql new file mode 100644 index 0000000000..87a1364cb1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_yt_key_filter.sql @@ -0,0 +1,110 @@ +/* postgres can not */ +/* multirun can not */ +use plato; + +pragma yt.UseNewPredicateExtraction; + +insert into OutDate32 +select * from as_table(AsList( + <|key:Date32('-144169-1-1')|>, + <|key:Date32('148107-12-31')|>)) +order by key; + +insert into OutDatetime64 +select * from as_table(AsList( + <|key:Datetime64('-144169-1-1T0:0:0Z')|>, + <|key:Datetime64('148107-12-31T23:59:59Z')|>)) +order by key; + +insert into OutTimestamp64 +select * from as_table(AsList( + <|key:Timestamp64('-144169-1-1T0:0:0Z')|>, + <|key:Timestamp64('148107-12-31T23:59:59.999999Z')|>)) +order by key; + +commit; + +select * from OutDate32 +where key > Date('1970-1-1') +and key > Datetime('1970-1-1T0:0:0Z') +and key > Timestamp('1970-1-1T0:0:0Z') +and key > Date32('-144169-1-1') +and key > Datetime64('-144169-1-1T0:0:0Z') +and key > Timestamp64('-144169-1-1T0:0:0Z') +and key >= Date('2105-12-31') +and key >= Datetime('2105-12-31T23:59:59Z') +and key >= Timestamp('2105-12-31T23:59:59Z') +and key >= Date32('148107-12-31') +and key >= Datetime64('148107-12-31T0:0:0Z') +and key >= Timestamp64('148107-12-31T0:0:0Z') +; +select * from OutDate32 +where key < Date('2105-12-31') +and key < Datetime('2105-12-31T23:59:59Z') +and key < Timestamp('2105-12-31T23:59:59.999999Z') +and key < Date32('148107-12-31') +and key < Datetime64('148107-12-31T23:59:59Z') +and key < Timestamp64('148107-12-31T23:59:59.999999Z') +and key <= Date('1970-1-1') +and key <= Datetime('1970-1-1T0:0:0Z') +and key <= Timestamp('1970-1-1T0:0:0Z') +and key <= Date32('-144169-1-1') +and key <= Datetime64('-144169-1-1T0:0:0Z') +and key <= Timestamp64('-144169-1-1T0:0:0Z') +; +select * from OutDatetime64 +where key > Date('1970-1-1') +and key > Datetime('1970-1-1T0:0:0Z') +and key > Timestamp('1970-1-1T0:0:0Z') +and key > Date32('-144169-1-1') +and key > Datetime64('-144169-1-1T0:0:0Z') +and key > Timestamp64('-144169-1-1T0:0:0Z') +and key >= Date('2105-12-31') +and key >= Datetime('2105-12-31T23:59:59Z') +and key >= Timestamp('2105-12-31T23:59:59Z') +and key >= Date32('148107-12-31') +and key >= Datetime64('148107-12-31T0:0:0Z') +and key >= Timestamp64('148107-12-31T0:0:0Z') +; +select * from OutDatetime64 +where key < Date('2105-12-31') +and key < Datetime('2105-12-31T23:59:59Z') +and key < Timestamp('2105-12-31T23:59:59.999999Z') +and key < Date32('148107-12-31') +and key < Datetime64('148107-12-31T23:59:59Z') +and key < Timestamp64('148107-12-31T23:59:59.999999Z') +and key <= Date('1970-1-1') +and key <= Datetime('1970-1-1T0:0:0Z') +and key <= Timestamp('1970-1-1T0:0:0Z') +and key <= Date32('-144169-1-1') +and key <= Datetime64('-144169-1-1T0:0:0Z') +and key <= Timestamp64('-144169-1-1T0:0:0Z') +; +select * from OutTimestamp64 +where key > Date('1970-1-1') +and key > Datetime('1970-1-1T0:0:0Z') +and key > Timestamp('1970-1-1T0:0:0Z') +and key > Date32('-144169-1-1') +and key > Datetime64('-144169-1-1T0:0:0Z') +and key > Timestamp64('-144169-1-1T0:0:0Z') +and key >= Date('2105-12-31') +and key >= Datetime('2105-12-31T23:59:59Z') +and key >= Timestamp('2105-12-31T23:59:59Z') +and key >= Date32('148107-12-31') +and key >= Datetime64('148107-12-31T0:0:0Z') +and key >= Timestamp64('148107-12-31T0:0:0Z') +; +select * from OutTimestamp64 +where key < Date('2105-12-31') +and key < Datetime('2105-12-31T23:59:59Z') +and key < Timestamp('2105-12-31T23:59:59.999999Z') +and key < Date32('148107-12-31') +and key < Datetime64('148107-12-31T23:59:59Z') +and key < Timestamp64('148107-12-31T23:59:59.999999Z') +and key <= Date('1970-1-1') +and key <= Datetime('1970-1-1T0:0:0Z') +and key <= Timestamp('1970-1-1T0:0:0Z') +and key <= Date32('-144169-1-1') +and key <= Datetime64('-144169-1-1T0:0:0Z') +and key <= Timestamp64('-144169-1-1T0:0:0Z') +; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_yt_native-default.cfg b/yql/essentials/tests/sql/suites/bigdate/table_yt_native-default.cfg new file mode 100644 index 0000000000..cd64dfec4a --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_yt_native-default.cfg @@ -0,0 +1,3 @@ +out Output output.txt +providers yt +canonize_yt diff --git a/yql/essentials/tests/sql/suites/bigdate/table_yt_native-on.cfg b/yql/essentials/tests/sql/suites/bigdate/table_yt_native-on.cfg new file mode 100644 index 0000000000..6486efd742 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_yt_native-on.cfg @@ -0,0 +1,5 @@ +out Output output.txt +providers yt +canonize_yt +pragma yt.UseNativeYtTypes; +pragma yt.NativeYtTypeCompatibility = "bigdate"; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_yt_native-wo_compat.cfg b/yql/essentials/tests/sql/suites/bigdate/table_yt_native-wo_compat.cfg new file mode 100644 index 0000000000..c5e08d7e27 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_yt_native-wo_compat.cfg @@ -0,0 +1,5 @@ +out Output output.txt +providers yt +canonize_yt +pragma yt.UseNativeYtTypes; +pragma yt.NativeYtTypeCompatibility = "date"; diff --git a/yql/essentials/tests/sql/suites/bigdate/table_yt_native.sql b/yql/essentials/tests/sql/suites/bigdate/table_yt_native.sql new file mode 100644 index 0000000000..3d7d636051 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/table_yt_native.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +/* multirun can not */ +use plato; + +insert into @tmpTable +select date32('1969-12-31') as d32, datetime64('1969-12-31T0:0:0Z') as dt64, timestamp64('1969-12-31T0:0:0Z') as ts64, interval64('P65536D') as i64; + +commit; + +insert into Output +select * from @tmpTable where d32 < date32('1970-1-1'); diff --git a/yql/essentials/tests/sql/suites/bigdate/tz.txt b/yql/essentials/tests/sql/suites/bigdate/tz.txt new file mode 100644 index 0000000000..8be05f77ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz.txt @@ -0,0 +1 @@ +{"waz"="\x7f\xff\x9c\x20\x00\x01";"wdz"="\x7f\xff\xff\xff\x7c\x55\x7b\x1f\x00\x01";"wtz"="\x7f\xf8\x26\xef\x55\xf6\x70\x13\x00\x01"}; diff --git a/yql/essentials/tests/sql/suites/bigdate/tz.txt.attr b/yql/essentials/tests/sql/suites/bigdate/tz.txt.attr new file mode 100644 index 0000000000..0d613e597e --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz.txt.attr @@ -0,0 +1,8 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["waz";["DataType";"TzDate32"]]; + ["wdz";["DataType";"TzDatetime64"]]; + ["wtz";["DataType";"TzTimestamp64"]]; + ]]; +}} + diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_bytes.sql b/yql/essentials/tests/sql/suites/bigdate/tz_bytes.sql new file mode 100644 index 0000000000..3a79dfdd2d --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_bytes.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +/* syntax version 1 */ +select ToBytes(TzDate32("1901-01-01,Europe/Moscow")); +select cast(FromBytes(ToBytes(TzDate32("1901-01-01,Europe/Moscow")),TzDate32) as string); + +select ToBytes(TzDatetime64("1901-01-01T01:02:03,Europe/Moscow")); +select cast(FromBytes(ToBytes(TzDatetime64("1901-01-01T01:02:03,Europe/Moscow")),TzDatetime64) as string); + +select ToBytes(TzTimestamp64("1901-01-01T01:02:03.456789,Europe/Moscow")); +select cast(FromBytes(ToBytes(TzTimestamp64("1901-01-01T01:02:03.456789,Europe/Moscow")),TzTimestamp64) as string); + diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_table_fill.cfg b/yql/essentials/tests/sql/suites/bigdate/tz_table_fill.cfg new file mode 100644 index 0000000000..879bab94a0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_table_fill.cfg @@ -0,0 +1,2 @@ +out Output output.txt + diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_table_fill.sql b/yql/essentials/tests/sql/suites/bigdate/tz_table_fill.sql new file mode 100644 index 0000000000..f97466cd0c --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_table_fill.sql @@ -0,0 +1,12 @@ +use plato; + +$waz = TzDate32("1900-01-01,Europe/Moscow"); +$wdz = TzDatetime64("1900-01-01T02:03:04,Europe/Moscow"); +$wtz = TzTimestamp64("1900-01-01T02:03:04.567891,Europe/Moscow"); + +insert into Output +select + $waz as waz, + $wdz as wdz, + $wtz as wtz, + ($waz, $wdz, $waz) as tup; diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_table_pull.cfg b/yql/essentials/tests/sql/suites/bigdate/tz_table_pull.cfg new file mode 100644 index 0000000000..9fc18af3ef --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_table_pull.cfg @@ -0,0 +1,2 @@ +in Input tz.txt + diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_table_pull.sql b/yql/essentials/tests/sql/suites/bigdate/tz_table_pull.sql new file mode 100644 index 0000000000..334892b938 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_table_pull.sql @@ -0,0 +1,6 @@ +USE plato; + +SELECT + waz,wdz,wtz +FROM Input + diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_table_rw.cfg b/yql/essentials/tests/sql/suites/bigdate/tz_table_rw.cfg new file mode 100644 index 0000000000..9793ce0c22 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_table_rw.cfg @@ -0,0 +1,2 @@ +in Input tz.txt +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_table_rw.sql b/yql/essentials/tests/sql/suites/bigdate/tz_table_rw.sql new file mode 100644 index 0000000000..ca11765fc8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_table_rw.sql @@ -0,0 +1,6 @@ +use plato; + +insert into Output +select (waz,wdz,wtz),waz,wdz,wtz from Input; + + diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_table_yt_key_filter.cfg b/yql/essentials/tests/sql/suites/bigdate/tz_table_yt_key_filter.cfg new file mode 100644 index 0000000000..a51be0aec2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_table_yt_key_filter.cfg @@ -0,0 +1,5 @@ +out OutTzDate32 OutTzDate32.txt +out OutTzDatetime64 OutTzDatetime64.txt +out OutTzTimestamp64 OutTzTimestamp64.txt +providers yt +canonize_yt diff --git a/yql/essentials/tests/sql/suites/bigdate/tz_table_yt_key_filter.sql b/yql/essentials/tests/sql/suites/bigdate/tz_table_yt_key_filter.sql new file mode 100644 index 0000000000..7f96c9b32b --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tz_table_yt_key_filter.sql @@ -0,0 +1,62 @@ +/* postgres can not */ +/* multirun can not */ +use plato; + +pragma yt.UseNewPredicateExtraction; + +insert into OutTzDate32 +select * from as_table(AsList( + <|key:TzDate32('-144169-1-1,UTC')|>, + <|key:TzDate32('148107-12-31,UTC')|>)) +order by key; + +insert into OutTzDatetime64 +select * from as_table(AsList( + <|key:TzDatetime64('-144169-1-1T0:0:0,UTC')|>, + <|key:TzDatetime64('148107-12-31T23:59:59,UTC')|>)) +order by key; + +insert into OutTzTimestamp64 +select * from as_table(AsList( + <|key:TzTimestamp64('-144169-1-1T0:0:0,UTC')|>, + <|key:TzTimestamp64('148107-12-31T23:59:59.999999,UTC')|>)) +order by key; + +commit; + +select * from OutTzDate32 +where key > TzDate32('-144169-1-1,UTC') +and key > TzDatetime64('-144169-1-1T0:0:0,UTC') +and key > TzTimestamp64('-144169-1-1T0:0:0,UTC') +and key >= TzDate32('148107-12-31,UTC') +and key >= TzDatetime64('148107-12-31T0:0:0,UTC') +and key >= TzTimestamp64('148107-12-31T0:0:0,UTC') +; +select * from OutTzDate32 +where key < TzDate32('148107-12-31,UTC') +and key < TzDatetime64('148107-12-31T23:59:59,UTC') +and key < TzTimestamp64('148107-12-31T23:59:59.999999,UTC') +and key <= TzDate32('-144169-1-1,UTC') +and key <= TzDatetime64('-144169-1-1T0:0:0,UTC') +and key <= TzTimestamp64('-144169-1-1T0:0:0,UTC') +; +select * from OutTzDatetime64 +where key > TzDatetime64('-144169-1-1T0:0:0,UTC') +and key > TzTimestamp64('-144169-1-1T0:0:0,UTC') +and key >= TzDatetime64('148107-12-31T0:0:0,UTC') +and key >= TzTimestamp64('148107-12-31T0:0:0,UTC') +; +select * from OutTzDatetime64 +where key < TzDatetime64('148107-12-31T23:59:59,UTC') +and key < TzTimestamp64('148107-12-31T23:59:59.999999,UTC') +and key <= TzDatetime64('-144169-1-1T0:0:0,UTC') +and key <= TzTimestamp64('-144169-1-1T0:0:0,UTC') +; +select * from OutTzTimestamp64 +where key > TzTimestamp64('-144169-1-1T0:0:0,UTC') +and key >= TzTimestamp64('148107-12-31T0:0:0,UTC') +; +select * from OutTzTimestamp64 +where key < TzTimestamp64('148107-12-31T23:59:59.999999,UTC') +and key <= TzTimestamp64('-144169-1-1T0:0:0,UTC') +; diff --git a/yql/essentials/tests/sql/suites/bigdate/tzcasts.sql b/yql/essentials/tests/sql/suites/bigdate/tzcasts.sql new file mode 100644 index 0000000000..8a5516b435 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tzcasts.sql @@ -0,0 +1,18 @@ +select + cast(TzDate32("1900-01-01,Europe/Moscow") as String), + cast("1900-01-01,Europe/Moscow" as TzDate32), + cast(TzDatetime64("1900-01-01T01:02:03,Europe/Moscow") as String), + cast("1900-01-01T01:02:03,Europe/Moscow" as TzDatetime64), + cast(TzTimestamp64("1900-01-01T01:02:03.456789,Europe/Moscow") as String), + cast("1900-01-01T01:02:03.456789,Europe/Moscow" as TzTimestamp64), + + AddTimezone(Date32("1900-01-01"),"Europe/Moscow"), + AddTimezone(Datetime64("1900-01-01T01:02:03Z"),"Europe/Moscow"), + AddTimezone(Timestamp64("1900-01-01T01:02:03.456789Z"),"Europe/Moscow"), + + cast(RemoveTimezone(TzDate32("1900-01-02,Europe/Moscow")) as String), + cast(RemoveTimezone(TzDatetime64("1900-01-01T03:32:20,Europe/Moscow")) as String), + cast(RemoveTimezone(TzTimestamp64("1900-01-01T03:32:20.456789,Europe/Moscow")) as String); + + + diff --git a/yql/essentials/tests/sql/suites/bigdate/tznumliterals.sql b/yql/essentials/tests/sql/suites/bigdate/tznumliterals.sql new file mode 100644 index 0000000000..d62c3d6c61 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tznumliterals.sql @@ -0,0 +1,51 @@ +pragma warning("disable","4510"); + +select +cast(Yql::Date32(AsAtom('-53375809')) as string), +Yql::TzDate32(AsAtom('-53375809,UTC')), +Yql::TzDate32(AsAtom('-53375809,Europe/Moscow')), +Yql::TzDate32(AsAtom('-53375809,America/Los_Angeles')), +cast(Yql::Date32(AsAtom('53375807')) as string), +Yql::TzDate32(AsAtom('53375807,UTC')), +Yql::TzDate32(AsAtom('53375807,Europe/Moscow')), +Yql::TzDate32(AsAtom('53375807,America/Los_Angeles')), +cast(Yql::Date32(AsAtom('-719162')) as string), +Yql::TzDate32(AsAtom('-719162,UTC')), +Yql::TzDate32(AsAtom('-719162,Europe/Moscow')), +cast(Yql::Date32(AsAtom('-719163')) as string), +Yql::TzDate32(AsAtom('-719163,UTC')), +Yql::TzDate32(AsAtom('-719163,Europe/Moscow')); + +select +cast(Yql::Datetime64(AsAtom('-4611669897600')) as string), +Yql::TzDatetime64(AsAtom('-4611669897600,UTC')), +Yql::TzDatetime64(AsAtom('-4611669897600,Europe/Moscow')), +Yql::TzDatetime64(AsAtom('-4611669897600,America/Los_Angeles')), +cast(Yql::Datetime64(AsAtom('4611669811199')) as string), +Yql::TzDatetime64(AsAtom('4611669811199,UTC')), +Yql::TzDatetime64(AsAtom('4611669811199,Europe/Moscow')), +Yql::TzDatetime64(AsAtom('4611669811199,America/Los_Angeles')), +cast(Yql::Datetime64(AsAtom('-62135596800')) as string), +Yql::TzDatetime64(AsAtom('-62135596800,UTC')), +Yql::TzDatetime64(AsAtom('-62135596800,Europe/Moscow')), +cast(Yql::Datetime64(AsAtom('-62135596801')) as string), +Yql::TzDatetime64(AsAtom('-62135596801,UTC')), +Yql::TzDatetime64(AsAtom('-62135596801,Europe/Moscow')); + + +select +cast(Yql::Timestamp64(AsAtom('-4611669897600000000')) as string), +Yql::TzTimestamp64(AsAtom('-4611669897600000000,UTC')), +Yql::TzTimestamp64(AsAtom('-4611669897600000000,Europe/Moscow')), +Yql::TzTimestamp64(AsAtom('-4611669897600000000,America/Los_Angeles')), +cast(Yql::Timestamp64(AsAtom('4611669811199999999')) as string), +Yql::TzTimestamp64(AsAtom('4611669811199999999,UTC')), +Yql::TzTimestamp64(AsAtom('4611669811199999999,Europe/Moscow')), +Yql::TzTimestamp64(AsAtom('4611669811199999999,America/Los_Angeles')), +cast(Yql::Timestamp64(AsAtom('-62135596800000000')) as string), +Yql::TzTimestamp64(AsAtom('-62135596800000000,UTC')), +Yql::TzTimestamp64(AsAtom('-62135596800000000,Europe/Moscow')), +cast(Yql::Timestamp64(AsAtom('-62135596800000001')) as string), +Yql::TzTimestamp64(AsAtom('-62135596800000001,UTC')), +Yql::TzTimestamp64(AsAtom('-62135596800000001,Europe/Moscow')); + diff --git a/yql/essentials/tests/sql/suites/bigdate/tzstrliterals.sql b/yql/essentials/tests/sql/suites/bigdate/tzstrliterals.sql new file mode 100644 index 0000000000..72b2433075 --- /dev/null +++ b/yql/essentials/tests/sql/suites/bigdate/tzstrliterals.sql @@ -0,0 +1,48 @@ +select + cast(Date32("-144169-01-01") as string), + TzDate32("-144169-01-01,UTC"), + TzDate32("-144169-01-02,Europe/Moscow"), + TzDate32("-144169-01-01,America/Los_Angeles"), + cast(Date32("148107-12-31") as string), + TzDate32("148107-12-31,UTC"), + TzDate32("148108-01-01,Europe/Moscow"), + TzDate32("148107-12-31,America/Los_Angeles"), + cast(Date32("1-01-01") as string), + TzDate32("1-01-01,UTC"), + TzDate32("1-01-02,Europe/Moscow"), + cast(Date32("-1-12-31") as string), + TzDate32("-1-12-31,UTC"), + TzDate32("1-01-01,Europe/Moscow"); + +select + cast(Datetime64("-144169-01-01T00:00:00Z") as string), + TzDatetime64("-144169-01-01T00:00:00,UTC"), + TzDatetime64("-144169-01-01T02:30:17,Europe/Moscow"), + TzDatetime64("-144170-12-31T16:07:02,America/Los_Angeles"), + cast(Datetime64("148107-12-31T23:59:59Z") as string), + TzDatetime64("148107-12-31T23:59:59,UTC"), + TzDatetime64("148108-01-01T02:59:59,Europe/Moscow"), + TzDatetime64("148107-12-31T15:59:59,America/Los_Angeles"), + cast(Datetime64("1-01-01T00:00:00Z") as string), + TzDatetime64("1-01-01T00:00:00,UTC"), + TzDatetime64("1-01-01T02:30:17,Europe/Moscow"), + cast(Datetime64("-1-12-31T23:59:59Z") as string), + TzDatetime64("-1-12-31T23:59:59,UTC"), + TzDatetime64("1-01-01T02:30:16,Europe/Moscow"); + +select + cast(Timestamp64("-144169-01-01T00:00:00Z") as string), + TzTimestamp64("-144169-01-01T00:00:00,UTC"), + TzTimestamp64("-144169-01-01T02:30:17,Europe/Moscow"), + TzTimestamp64("-144170-12-31T16:07:02,America/Los_Angeles"), + cast(Timestamp64("148107-12-31T23:59:59.999999Z") as string), + TzTimestamp64("148107-12-31T23:59:59.999999,UTC"), + TzTimestamp64("148108-01-01T02:59:59.999999,Europe/Moscow"), + TzTimestamp64("148107-12-31T15:59:59.999999,America/Los_Angeles"), + cast(Timestamp64("1-01-01T00:00:00Z") as string), + TzTimestamp64("1-01-01T00:00:00,UTC"), + TzTimestamp64("1-01-01T02:30:17,Europe/Moscow"), + cast(Timestamp64("-1-12-31T23:59:59.999999Z") as string), + TzTimestamp64("-1-12-31T23:59:59.999999,UTC"), + TzTimestamp64("1-01-01T02:30:16.999999,Europe/Moscow"); + |