diff options
| author | udovichenko-r <udovichenko-r@yandex-team.com> | 2024-11-19 14:58:38 +0300 |
|---|---|---|
| committer | udovichenko-r <udovichenko-r@yandex-team.com> | 2024-11-19 15:16:27 +0300 |
| commit | 24521403b1c44303e043ba540c09b1fe991c7474 (patch) | |
| tree | 341d1e7206bc7c143d04d2d96f05b6dc0655606d /yql/essentials/tests/sql/suites/expr | |
| parent | 72b3cd51dc3fb9d16975d353ea82fd85701393cc (diff) | |
| download | ydb-24521403b1c44303e043ba540c09b1fe991c7474.tar.gz | |
YQL-19206 Move contrib/ydb/library/yql/tests/sql/suites -> yql/essentials/tests/sql/suites
commit_hash:d0ef1f92b09c94db7c2408f946d2a4c62b603f00
Diffstat (limited to 'yql/essentials/tests/sql/suites/expr')
210 files changed, 2899 insertions, 0 deletions
diff --git a/yql/essentials/tests/sql/suites/expr/as_dict_dict_key.sql b/yql/essentials/tests/sql/suites/expr/as_dict_dict_key.sql new file mode 100644 index 0000000000..40024336bf --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_dict_dict_key.sql @@ -0,0 +1,24 @@ +/* postgres can not */ +$d1 = AsDict( + AsTuple(AsList(1,2,3),"foo"), + AsTuple(AsList(1,2),"bar") +); + + +$d2 = AsDict( + AsTuple(AsList(1,3),"baz"), + AsTuple(AsList(1,2),"qwe") +); + +$d3 = DictCreate(DictKeyType(TypeOf($d2)), DictPayloadType(TypeOf($d2))); + +$d = AsDict( + AsTuple($d1, 17), + AsTuple($d2, 32) +); + +select ListSort(ListFlatten(ListMap(DictItems($d), ($x) -> {return ListMap(DictItems($x.0), ($y) -> {return ($y, $x.1)})}))), + ListSort(ListFlatten(ListMap(DictKeys($d), ($x) -> {return DictItems($x)}))), + ListSort(DictPayloads($d)); +select DictLookup($d,$d1), DictLookup($d,$d3); +select DictContains($d,$d1), DictContains($d,$d3); diff --git a/yql/essentials/tests/sql/suites/expr/as_dict_implicit_cast.sql b/yql/essentials/tests/sql/suites/expr/as_dict_implicit_cast.sql new file mode 100644 index 0000000000..e9024cf14e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_dict_implicit_cast.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +SELECT {{13:42U},{13U:42}}, {{13U:42},{13:42U}}; diff --git a/yql/essentials/tests/sql/suites/expr/as_dict_list_key.sql b/yql/essentials/tests/sql/suites/expr/as_dict_list_key.sql new file mode 100644 index 0000000000..1d105cfdd2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_dict_list_key.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +$d = AsDict( + AsTuple(AsList(1,2,3),"foo"), + AsTuple(AsList(1,2),"bar") +); + +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); diff --git a/yql/essentials/tests/sql/suites/expr/as_dict_tuple_key.sql b/yql/essentials/tests/sql/suites/expr/as_dict_tuple_key.sql new file mode 100644 index 0000000000..78c8bb3924 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_dict_tuple_key.sql @@ -0,0 +1,27 @@ +/* postgres can not */ +$d = AsDict( + AsTuple(AsTuple(),"foo"), + AsTuple(AsTuple(),"bar") +); + +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$d = AsDict( + AsTuple(AsTuple(1),"foo"), + AsTuple(AsTuple(2),"bar") +); + +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(1)),DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(1)),DictContains($d,AsTuple(3)); + +$d = AsDict( + AsTuple(AsTuple(1,2),"foo"), + AsTuple(AsTuple(1,3),"bar") +); + +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(1,2)),DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)),DictContains($d,AsTuple(1,4)); diff --git a/yql/essentials/tests/sql/suites/expr/as_set.sql b/yql/essentials/tests/sql/suites/expr/as_set.sql new file mode 100644 index 0000000000..fedd028b97 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_set.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select DictLength(AsSetStrict(1,2,3)),DictLength(AsSet(1,2,3u)),DictLength(SetCreate(Int32))
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/as_struct_0.sqlx b/yql/essentials/tests/sql/suites/expr/as_struct_0.sqlx new file mode 100644 index 0000000000..fd59e199a4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_struct_0.sqlx @@ -0,0 +1 @@ +select AsStruct(5, 1 as key, 2l as subkey, 'cool' as value) diff --git a/yql/essentials/tests/sql/suites/expr/as_struct_1.sqlx b/yql/essentials/tests/sql/suites/expr/as_struct_1.sqlx new file mode 100644 index 0000000000..449debf70f --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_struct_1.sqlx @@ -0,0 +1 @@ +select AsStruct(1 as key, 2l as subkey, 'cool' as value, 321) diff --git a/yql/essentials/tests/sql/suites/expr/as_struct_2.sqlx b/yql/essentials/tests/sql/suites/expr/as_struct_2.sqlx new file mode 100644 index 0000000000..67dd7fac8c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_struct_2.sqlx @@ -0,0 +1 @@ +select AsStruct(1, 2, 3) diff --git a/yql/essentials/tests/sql/suites/expr/as_struct_3.sqlx b/yql/essentials/tests/sql/suites/expr/as_struct_3.sqlx new file mode 100644 index 0000000000..d2f2e9038d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_struct_3.sqlx @@ -0,0 +1 @@ +select AsStruct('funny', 1, 100500l, 'bunny') diff --git a/yql/essentials/tests/sql/suites/expr/as_struct_syntax.sql b/yql/essentials/tests/sql/suites/expr/as_struct_syntax.sql new file mode 100644 index 0000000000..d9f0aeef02 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_struct_syntax.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select AsStruct(1 as key, 2l as subkey, 'cool' as value) diff --git a/yql/essentials/tests/sql/suites/expr/as_table.sql b/yql/essentials/tests/sql/suites/expr/as_table.sql new file mode 100644 index 0000000000..8a293d03a9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table.sql @@ -0,0 +1,14 @@ +/* postgres can not */ + +$data = AsList( + AsStruct(1u AS Key, "v1" AS Value1, 100 AS Value2), + AsStruct(2u AS Key, "v2" AS Value1, 200 AS Value2), + AsStruct(3u AS Key, "v3" AS Value1, 300 AS Value2)); + +SELECT Key, Value2 FROM AS_TABLE($data); + +$input = ( + SELECT * FROM AS_TABLE($data) WHERE Key > 1 +); + +SELECT Key, Value1 FROM $input; diff --git a/yql/essentials/tests/sql/suites/expr/as_table_args.sqlx b/yql/essentials/tests/sql/suites/expr/as_table_args.sqlx new file mode 100644 index 0000000000..83a28b7fbd --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_args.sqlx @@ -0,0 +1,5 @@ +$data = AsList( + AsStruct(1u AS Key, "v1" AS Value1, 100 AS Value2), + AsStruct(2u AS Key, "v2" AS Value1, 200 AS Value2)); + +SELECT * FROM AS_TABLE($data, "BadArg"); diff --git a/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.cfg b/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.cfg new file mode 100644 index 0000000000..5dae597903 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.sql b/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.sql new file mode 100644 index 0000000000..a21a19e481 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_bad_columns_fail.sql @@ -0,0 +1,9 @@ +/* postgres can not */ + +$data = AsList( + AsStruct(1u AS Key, "v1" AS Value1, 100 AS Value2), + AsStruct(2u AS Key, "v2" AS Value1, 200 AS Value2), + AsStruct(3u AS Key, "v3" AS Value1, 300 AS Value2)); + +SELECT Key, Value3 FROM AS_TABLE($data); + diff --git a/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.cfg b/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.cfg new file mode 100644 index 0000000000..5dae597903 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.sql b/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.sql new file mode 100644 index 0000000000..beb55021a6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_bad_row_type_fail.sql @@ -0,0 +1,5 @@ +/* postgres can not */ + +$data = AsList(1u, 2u, 3u); + +SELECT Key, Value3 FROM AS_TABLE($data); diff --git a/yql/essentials/tests/sql/suites/expr/as_table_cluster.sqlx b/yql/essentials/tests/sql/suites/expr/as_table_cluster.sqlx new file mode 100644 index 0000000000..d8e29e1dba --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_cluster.sqlx @@ -0,0 +1,5 @@ +$data = AsList( + AsStruct(1u AS Key, "v1" AS Value1, 100 AS Value2), + AsStruct(2u AS Key, "v2" AS Value1, 200 AS Value2)); + +SELECT * FROM plato.AS_TABLE($data); diff --git a/yql/essentials/tests/sql/suites/expr/as_table_emptylist.cfg b/yql/essentials/tests/sql/suites/expr/as_table_emptylist.cfg new file mode 100644 index 0000000000..9b85bd0ae8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_emptylist.cfg @@ -0,0 +1 @@ +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/expr/as_table_emptylist.sql b/yql/essentials/tests/sql/suites/expr/as_table_emptylist.sql new file mode 100644 index 0000000000..6896846199 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_emptylist.sql @@ -0,0 +1,17 @@ +/* syntax version 1 */ +/* postgres can not */ +select * from as_table([]); +select x+1 from as_table([]); +select * from as_table([]) order by x limit 5 offset 2; +select x from as_table([]) order by x; +select count(*) from as_table([]); +select x, count(*) from as_table([]) group by x; +select x, count(*) from as_table([]) group by x having count(x) > 1; +select lead(x) over w, lag(x) over w, row_number() over w, + count(*) over w from as_table([]) window w as (); + +select lead(x) over w, lag(x) over w, rank() over w, denserank() over w, + count(*) over w from as_table([]) window w as (order by x); + +insert into plato.Output +select * from as_table([]); diff --git a/yql/essentials/tests/sql/suites/expr/as_table_emptylist2.sql b/yql/essentials/tests/sql/suites/expr/as_table_emptylist2.sql new file mode 100644 index 0000000000..588e1e3a02 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_emptylist2.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +/* postgres can not */ +select * from as_table([]) flatten columns; +select * from as_table([]) group by key, subkey; +select * from as_table([]) flatten optional by (1+x as y); +select distinct * from as_table([]); diff --git a/yql/essentials/tests/sql/suites/expr/as_table_hints.sqlx b/yql/essentials/tests/sql/suites/expr/as_table_hints.sqlx new file mode 100644 index 0000000000..dcb548d062 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_hints.sqlx @@ -0,0 +1,5 @@ +$data = AsList( + AsStruct(1u AS Key, "v1" AS Value1, 100 AS Value2), + AsStruct(2u AS Key, "v2" AS Value1, 200 AS Value2)); + +SELECT * FROM AS_TABLE($data) WITH "hint"; diff --git a/yql/essentials/tests/sql/suites/expr/as_table_subquery.sqlx b/yql/essentials/tests/sql/suites/expr/as_table_subquery.sqlx new file mode 100644 index 0000000000..778ea45e99 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_table_subquery.sqlx @@ -0,0 +1,3 @@ +$data = (select * from plato.Input); + +SELECT * FROM AS_TABLE($data); diff --git a/yql/essentials/tests/sql/suites/expr/as_tuple_named.sqlx b/yql/essentials/tests/sql/suites/expr/as_tuple_named.sqlx new file mode 100644 index 0000000000..3557fa700d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_tuple_named.sqlx @@ -0,0 +1 @@ +select AsTuple(1 as key, 2l as subkey, 'cool' as value) diff --git a/yql/essentials/tests/sql/suites/expr/as_tuple_syntax.sql b/yql/essentials/tests/sql/suites/expr/as_tuple_syntax.sql new file mode 100644 index 0000000000..2d2728f229 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_tuple_syntax.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select AsTuple('funny', 1, 100500l, 'bunny') diff --git a/yql/essentials/tests/sql/suites/expr/as_variant_enum.sql b/yql/essentials/tests/sql/suites/expr/as_variant_enum.sql new file mode 100644 index 0000000000..dd14c8c1f6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_variant_enum.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* syntax version 1 */ +select AsVariant(1,"int"),AsEnum("monday");
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/as_variant_enum_compare.sql b/yql/essentials/tests/sql/suites/expr/as_variant_enum_compare.sql new file mode 100644 index 0000000000..34fbbeb93b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/as_variant_enum_compare.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +/* syntax version 1 */ +Select Enum("foo",Enum<"foo","bar">) == AsEnum("foo"), + Enum("foo",Enum<"foo","bar">) == AsEnum("bar"), + Enum("foo",Enum<"foo","bar">) == AsEnum("baz"); diff --git a/yql/essentials/tests/sql/suites/expr/backtick_escape.sql b/yql/essentials/tests/sql/suites/expr/backtick_escape.sql new file mode 100644 index 0000000000..1996b6d37a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/backtick_escape.sql @@ -0,0 +1,2 @@ +/* syntax version 1 */ +select 1 as `a``b`, 2 as ````, 3 as `\x60a\x60`, 4 as ```b```, 5 as `\`c\``; diff --git a/yql/essentials/tests/sql/suites/expr/between.sql b/yql/essentials/tests/sql/suites/expr/between.sql new file mode 100644 index 0000000000..df6af1dc4a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/between.sql @@ -0,0 +1,39 @@ +select + 1 between 2 and 4, + 3 between 2 and 4, + 5 between 2 and 4, + 3 between 4 and 2, + 5 between 4 and 2, + null between 2 and 4, + 1 between null and 4, + 1 between 4 and null, + 1 between 0 and null, + 1 between null and null, + null between null and null; + +select + 1 between asymmetric 2 and 4, + 3 between asymmetric 2 and 4, + 5 between asymmetric 2 and 4, + 3 between asymmetric 4 and 2, + 5 between asymmetric 4 and 2, + null between asymmetric 2 and 4, + 1 between asymmetric null and 4, + 1 between asymmetric 4 and null, + 1 between asymmetric 0 and null, + 1 between asymmetric null and null, + null between asymmetric null and null; + +select + 1 between symmetric 2 and 4, + 3 between symmetric 2 and 4, + 5 between symmetric 2 and 4, + 1 between symmetric 4 and 2, + 3 between symmetric 4 and 2, + 5 between symmetric 4 and 2, + null between symmetric 2 and 4, + 1 between symmetric null and 4, + 1 between symmetric 4 and null, + 1 between symmetric 0 and null, + 1 between symmetric null and null, + null between symmetric null and null; diff --git a/yql/essentials/tests/sql/suites/expr/callable.sql b/yql/essentials/tests/sql/suites/expr/callable.sql new file mode 100644 index 0000000000..b5af9143d5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/callable.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +/* syntax version 1 */ +$lambda = ($x) -> { + return cast($x as String) +}; + +$callables = AsTuple( + Callable(Callable<(Int32)->String>, $lambda), + Callable(Callable<(Bool)->String>, $lambda), +); + +select $callables.0(10), $callables.1(true); diff --git a/yql/essentials/tests/sql/suites/expr/cast_dynumber.sql b/yql/essentials/tests/sql/suites/expr/cast_dynumber.sql new file mode 100644 index 0000000000..840f928de2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_dynumber.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* syntax version 1 */ +SELECT CAST(DyNumber("-21.33") AS String), CAST(DyNumber("0") AS Utf8), + CAST(["-21.33E2","3.14","42","","bad"] AS List<DyNumber>); + +SELECT ListMap(["\x00\x80\x65","\x00\x81\x66","\x00\x82\x56","\x01","\x02\x84\x9A","\x02\x85\x99","\x02\x86\xA9"], ($i)->(FromBytes($i, DyNumber))); diff --git a/yql/essentials/tests/sql/suites/expr/cast_from_utf8.sql b/yql/essentials/tests/sql/suites/expr/cast_from_utf8.sql new file mode 100644 index 0000000000..acc97a5bad --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_from_utf8.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +select cast(Utf8("true") as bool), +cast(Utf8("-1") as Int32), +cast(Utf8("-3.5") as Double), +cast(Utf8("P1D") as Interval), +cast(Utf8("2000-01-01") as Date), +cast(Utf8("2000-01-01,GMT") as TzDate); diff --git a/yql/essentials/tests/sql/suites/expr/cast_longint.sql b/yql/essentials/tests/sql/suites/expr/cast_longint.sql new file mode 100644 index 0000000000..227f56992c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_longint.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +$value = CAST(-7 AS Decimal(10, 0)); +SELECT + $value AS binary, + CAST($value AS String) AS to_string, + CAST("+123" AS Decimal(10,0)) AS from_string, + CAST("bad" AS Decimal(10,0)) AS bad_cast; diff --git a/yql/essentials/tests/sql/suites/expr/cast_reverse_list.sql b/yql/essentials/tests/sql/suites/expr/cast_reverse_list.sql new file mode 100644 index 0000000000..dc88313866 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_reverse_list.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +/* syntax version 1 */ +select CAST(ListReverse(ListExtend(["1","2","3"], ["4","5","6"])) AS List<Int64>); + diff --git a/yql/essentials/tests/sql/suites/expr/cast_string_implicit.sql b/yql/essentials/tests/sql/suites/expr/cast_string_implicit.sql new file mode 100644 index 0000000000..b2872be2a1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_string_implicit.sql @@ -0,0 +1,16 @@ +select AsList('aaa', 'aaa'u); -- List<String> +select AsList('aaa', '[1, 2, 3]'j); -- List<String> +select AsList('aaa', '[1; 2; 3]'y); -- List<String> + +select AsList('aaa'u, 'aaa'); -- List<String> +select AsList('aaa'u, '[1, 2, 3]'j); -- List<Utf8> +select AsList('aaa'u, '[1; 2; 3]'y); -- List<String> + +select AsList('[1, 2, 3]'j, 'aaa'); -- List<String> +select AsList('[1, 2, 3]'j, 'aaa'u); -- List<Utf8> +select AsList('[1, 2, 3]'j, '[1; 2; 3]'y); -- List<String> + +select AsList('[1; 2; 3]'y, 'aaa'); -- List<String> +select AsList('[1; 2; 3]'y, 'aaa'u); -- List<String> +select AsList('[1; 2; 3]'y, '[1, 2, 3]'j); -- List<String> + diff --git a/yql/essentials/tests/sql/suites/expr/cast_struct.sql b/yql/essentials/tests/sql/suites/expr/cast_struct.sql new file mode 100644 index 0000000000..1fd1cd914b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_struct.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +/* syntax version 1 */ +$struct = <|du:3.14, fl:-1.f, i:0, s:"str"u, u:123u|>; +select + CAST($struct AS Struct<>) as empty, + CAST($struct AS Struct<du:Uint32?, fl:Uint32?, s:Uint16?, u:Int32?>) as partial, + CAST($struct AS Struct<x:Uint8?, y:Uint16?, z:Int8?>) as others, + CAST($struct AS Struct<du:Uint32, fl:Uint32, s:Uint16, u:Int32>) as fail; diff --git a/yql/essentials/tests/sql/suites/expr/cast_type_bind.sql b/yql/essentials/tests/sql/suites/expr/cast_type_bind.sql new file mode 100644 index 0000000000..c5cdce4889 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_type_bind.sql @@ -0,0 +1,4 @@ +/* postgres can not */ +/* syntax version 1 */ +$t = List<Uint32>; +select cast([1] as $t);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/cast_utf8.sql b/yql/essentials/tests/sql/suites/expr/cast_utf8.sql new file mode 100644 index 0000000000..f7ebf5ffb1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_utf8.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +SELECT CAST("Espa\u00f1ol" AS Utf8); diff --git a/yql/essentials/tests/sql/suites/expr/cast_variant.sql b/yql/essentials/tests/sql/suites/expr/cast_variant.sql new file mode 100644 index 0000000000..f453baed16 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/cast_variant.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +/* syntax version 1 */ +$a = AsEnum("a"); +$b = AsEnum("b"); +$c = AsEnum("c"); +$d = AsEnum("d"); + +$t = TypeOf([Just($a), Just($b), just($c)]); +select CAST([just($a), just($b), just($d)] AS $t); + +$t0 = TypeOf([Just($a), Just($b)]); +select CAST([$c, $d] AS $t0); diff --git a/yql/essentials/tests/sql/suites/expr/checked_ops.sql b/yql/essentials/tests/sql/suites/expr/checked_ops.sql new file mode 100644 index 0000000000..0b9d9e4243 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/checked_ops.sql @@ -0,0 +1,21 @@ +pragma CheckedOps="true"; +select +5+3, +200ut+200ut, +18446744073709551615ul+18446744073709551615ul; +select +5-3, +-120t-100t, +-9223372036854775807L-2l; +select +5*3, +200ut*200ut, +18446744073709551615ul*18446744073709551615ul; +select +5/3, +200ut/1t; +select +5%3, +100t%200ut; +select +-cast("-128" as int8); diff --git a/yql/essentials/tests/sql/suites/expr/checked_ops_agg.sql b/yql/essentials/tests/sql/suites/expr/checked_ops_agg.sql new file mode 100644 index 0000000000..b85db3cc07 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/checked_ops_agg.sql @@ -0,0 +1,3 @@ +pragma CheckedOps="true"; +select sum(null),sum(x),sumif(x,true),sum(just(x)),sumif(just(x),true) +from (values (18446744073709551615ul),(2ul)) as a(x); diff --git a/yql/essentials/tests/sql/suites/expr/common_type_for_resource_and_data.cfg b/yql/essentials/tests/sql/suites/expr/common_type_for_resource_and_data.cfg new file mode 100644 index 0000000000..d016b0b562 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/common_type_for_resource_and_data.cfg @@ -0,0 +1,3 @@ +udf yson2_udf +udf json2_udf +udf datetime2_udf diff --git a/yql/essentials/tests/sql/suites/expr/common_type_for_resource_and_data.sql b/yql/essentials/tests/sql/suites/expr/common_type_for_resource_and_data.sql new file mode 100644 index 0000000000..554c348b62 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/common_type_for_resource_and_data.sql @@ -0,0 +1,6 @@ +select AsList(DateTime::StartOfMonth(CurrentUtcDate()), CurrentUtcDate()); + +select AsList(Yson::Parse("1"), "2"y); +select AsList(Yson::ParseJson("1"), "2"j); +select AsList(Json2::Parse("1"), "2"j); + diff --git a/yql/essentials/tests/sql/suites/expr/constraints_of.cfg b/yql/essentials/tests/sql/suites/expr/constraints_of.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/constraints_of.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/constraints_of.sql b/yql/essentials/tests/sql/suites/expr/constraints_of.sql new file mode 100644 index 0000000000..2306ba3532 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/constraints_of.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +pragma warning("disable", "4510"); +use plato; + +$foo = select subkey, key, value as v from Input order by subkey asc, key desc limit 0; +$x = process $foo; + +select YQL::ConstraintsOf($x) as constraints; + diff --git a/yql/essentials/tests/sql/suites/expr/container_comp.sql b/yql/essentials/tests/sql/suites/expr/container_comp.sql new file mode 100644 index 0000000000..cd9246f4aa --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/container_comp.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +select AsStruct() == AsStruct(); +select AsTuple() == AsTuple(); +select AsTuple(0xffffffffu) == AsTuple(-1); +select AsTuple(1,2/0) < AsTuple(10,1); +select AsStruct(1 as a,2 as b) == AsStruct(1u as a,2u as b); +select AsStruct(1 as a,2 as b) == AsStruct(1u as a,2u as c); +select AsTuple(Void())<=AsTuple(Void()); +select AsTuple(null)<=AsTuple(null); +select AsTagged(1, "foo") = AsTagged(1u, "foo"); diff --git a/yql/essentials/tests/sql/suites/expr/convert_to_lambda_optional_args.sql b/yql/essentials/tests/sql/suites/expr/convert_to_lambda_optional_args.sql new file mode 100644 index 0000000000..729486ea2d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/convert_to_lambda_optional_args.sql @@ -0,0 +1,7 @@ +/* syntax version 1 */ +/* postgres can not */ +$f = ($x,$y?)->($x + ($y ?? 0)); +select ListMap([1,2,3],$f); + +$g = ($x,$y,$z?)->(($x + $y + $z) ?? 100); +select Yql::Fold([1,2,3],0,$g); diff --git a/yql/essentials/tests/sql/suites/expr/current_tz.sql b/yql/essentials/tests/sql/suites/expr/current_tz.sql new file mode 100644 index 0000000000..62381a378f --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/current_tz.sql @@ -0,0 +1,10 @@ +/* syntax version 1 */ +/* postgres can not */ +select + CurrentTzDate("Europe/Moscow"), + CurrentTzDatetime("Europe/Moscow"), + CurrentTzTimestamp("Europe/Moscow"), + CurrentUtcDate(), + CurrentUtcDatetime(), + CurrentUtcTimestamp(); + diff --git a/yql/essentials/tests/sql/suites/expr/decimal_bytes.sql b/yql/essentials/tests/sql/suites/expr/decimal_bytes.sql new file mode 100644 index 0000000000..aa21b040f1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/decimal_bytes.sql @@ -0,0 +1,14 @@ +select ToBytes(Decimal("14.2",5,1)), + ToBytes(Decimal("inf",5,1)), + ToBytes(Decimal("-inf",5,1)), + ToBytes(Decimal("nan",5,1)), + ToBytes(Nothing(Optional<Decimal(5,1)>)); + +select FromBytes(ToBytes(Decimal("14.2",5,1)),Decimal(5,1)), + FromBytes(ToBytes(Decimal("10",5,1)),Decimal(2,1)), + FromBytes(ToBytes(Decimal("-10",5,1)),Decimal(2,0)), + FromBytes(ToBytes(Decimal("inf",5,1)),Decimal(5,1)), + FromBytes(ToBytes(Decimal("-inf",5,1)),Decimal(5,1)), + FromBytes(ToBytes(Decimal("nan",5,1)),Decimal(5,1)), + FromBytes(Nothing(String?),Decimal(5,1)); + diff --git a/yql/essentials/tests/sql/suites/expr/default.cfg b/yql/essentials/tests/sql/suites/expr/default.cfg new file mode 100644 index 0000000000..e69de29bb2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/default.cfg diff --git a/yql/essentials/tests/sql/suites/expr/dict_builtins.sql b/yql/essentials/tests/sql/suites/expr/dict_builtins.sql new file mode 100644 index 0000000000..8b83553452 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/dict_builtins.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +/* syntax version 1 */ +$dict = AsDict(AsTuple("foo", 3), AsTuple("bar", 4)); + +SELECT + DictKeys($dict), + DictPayloads($dict), + DictItems($dict), + DictLookup($dict, "foo"), + DictLookup($dict, "baz"), + DictContains($dict, "foo"), + DictContains($dict, "baz"), + DictCreate(String, Tuple<String,Double?>), + DictCreate(Tuple<Int32?,String>, OptionalType(DataType("String"))); + diff --git a/yql/essentials/tests/sql/suites/expr/dict_builtins_null_lookup.sql b/yql/essentials/tests/sql/suites/expr/dict_builtins_null_lookup.sql new file mode 100644 index 0000000000..87e1bd0ff9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/dict_builtins_null_lookup.sql @@ -0,0 +1,27 @@ +/* postgres can not */ + +$d2 = AsDict(AsTuple(1/1, "bar")); +$d3 = AsDict(AsTuple(1/0, "baz")); + +$t1 = AsDict(AsTuple(AsTuple(1, "key"), AsTuple(1, "value"))); +$t2 = AsDict(AsTuple(AsTuple(1/1, "key"), AsTuple(2/1, "value"))); +$t3 = AsDict(AsTuple(AsTuple(1/0, "key"), AsTuple(123, "value"))); + +SELECT + DictContains($d2, null), -- false, no such key + DictContains($d3, null), -- true, null is convertible to Nothing<T> for any T + + DictLookup($d2, null), -- Nothing(String?), no such key + DictLookup($d3, null); -- Just("baz"), null is convertible to Nothing<T> for any T + + +SELECT + DictContains($t1, AsTuple(1, "keyy")), -- false, missing key + DictContains($t1, AsTuple(1, "key")), -- true, match + DictContains($t1, Just(AsTuple(1, "key"))), -- true, match with optional + + DictContains($t2, AsTuple(null, "key")), -- false, no such key + DictContains($t3, AsTuple(null, "key")), -- true, null is convertible to Nothing<T> for any T + + DictLookup($t2, AsTuple(null, "key")), -- Nothing(Tuple<Int32?, String>?), no such key + DictLookup($t3, AsTuple(null, "key")); -- Just(AsTuple(123, "value")), null is convertible to Nothing<T> for any T diff --git a/yql/essentials/tests/sql/suites/expr/dict_common_type.cfg b/yql/essentials/tests/sql/suites/expr/dict_common_type.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/dict_common_type.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/expr/dict_common_type.sql b/yql/essentials/tests/sql/suites/expr/dict_common_type.sql new file mode 100644 index 0000000000..7eca9cb631 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/dict_common_type.sql @@ -0,0 +1,15 @@ +/* syntax version 1 */ +/* postgres can not */ +/* yt can not */ +pragma warning("disable", "4510"); + +$d1 = ToDict([(1, 1u)]); +$d2 = AsDict((2, 2u)); +$d3 = YQL::Dict(Dict<Int32, Uint32>); +$d4 = YQL::Dict(Dict<Int32, Uint32>, (3, 3u)); + +$s1 = ToSet([1u]); +$s2 = AsSet(2u); + +select AsList({100u:100}, $d1, $d2, $d3, $d4); +select AsList({100}, $s1, $s2); diff --git a/yql/essentials/tests/sql/suites/expr/dict_comp.sql b/yql/essentials/tests/sql/suites/expr/dict_comp.sql new file mode 100644 index 0000000000..2f82dfdaad --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/dict_comp.sql @@ -0,0 +1,18 @@ +/* postgres can not */ +select AsTuple( + AsDict(AsTuple(1,2u)) == AsDict(AsTuple(1,2)), + AsDict(AsTuple(1,2u)) == AsDict(AsTuple(1,3)), + AsDict(AsTuple(1,2u)) == AsDict(AsTuple(1,2), AsTuple(3,4)), + AsDict(AsTuple(1,2u)) == AsDict(AsTuple(2,2)), + AsDict(AsTuple(1u,2l)) == AsDict(AsTuple(1u,just(2u))), + AsDict(AsTuple(1,2u)) == AsDict(AsTuple(1,2/0)), +); + +select AsTuple( + AsDict(AsTuple(1,2u)) != AsDict(AsTuple(1,2)), + AsDict(AsTuple(1,2u)) != AsDict(AsTuple(1,3)), + AsDict(AsTuple(1,2u)) != AsDict(AsTuple(1,2), AsTuple(3,4)), + AsDict(AsTuple(1,2u)) != AsDict(AsTuple(2,2)), + AsDict(AsTuple(1u,2l)) != AsDict(AsTuple(1u,just(2u))), + AsDict(AsTuple(1,2u)) != AsDict(AsTuple(1,2/0)), +);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/distinct_from.sql b/yql/essentials/tests/sql/suites/expr/distinct_from.sql new file mode 100644 index 0000000000..bcb2a12714 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/distinct_from.sql @@ -0,0 +1,11 @@ +/* syntax version 1 */ +/* postgres can not */ + +select + 1 is distinct from 2, + 1 is not distinct from 2, + null is distinct from null, + Just(1 + 2) is distinct from Nothing(Int32?), + Nothing(Int32??) is not distinct from Just(Nothing(Int32?)) +; + diff --git a/yql/essentials/tests/sql/suites/expr/distinct_from_containers.sql b/yql/essentials/tests/sql/suites/expr/distinct_from_containers.sql new file mode 100644 index 0000000000..622fdddc30 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/distinct_from_containers.sql @@ -0,0 +1,18 @@ +/* syntax version 1 */ +/* postgres can not */ + +$vt1 = ParseType("Variant<Int32,Int32?>"); +$vt2 = ParseType("Variant<Int64,Null>"); + +$svt1 = ParseType("Variant<a:Int32,b:Int32?>"); +$svt2 = ParseType("Variant<a:Int64,b:Null>"); + +select + (1, 2) is not distinct from (1, 2, 1/0), --true + <|a:1/0, b:Nothing(String?), c:1|> is not distinct from + <|c:1u, d:1u/0u, e:Nothing(Utf8?)|>, --true + [1, 2, null] is not distinct from [1, 2, just(1/0)], --false + {1:null} is distinct from {1u:2/0}, --false + Variant(1/0, "1", $vt1) is distinct from Variant(null, "1", $vt2), --false + Variant(1/0, "b", $svt1) is not distinct from Variant(null, "b", $svt2), --true +; diff --git a/yql/essentials/tests/sql/suites/expr/distinct_from_opt.sql b/yql/essentials/tests/sql/suites/expr/distinct_from_opt.sql new file mode 100644 index 0000000000..33bfbbf368 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/distinct_from_opt.sql @@ -0,0 +1,13 @@ +/* syntax version 1 */ +/* postgres can not */ + +select + Null is not distinct from 1/0, --true + 1/0 is distinct from Null, --false + 1u/0u is distinct from 1/0, --false + Just(1u) is not distinct from 1/0, --false + 1u/0u is distinct from Just(1), --true + 1u is distinct from 1, --false + Nothing(Int32??) is distinct from Just(1/0), --true + 1 is not distinct from Just(Just(1u)), --true +; diff --git a/yql/essentials/tests/sql/suites/expr/double.txt b/yql/essentials/tests/sql/suites/expr/double.txt new file mode 100644 index 0000000000..b5d25384ec --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/double.txt @@ -0,0 +1,12 @@ +{"key"=23.0;"subkey"="3";"value"="aaa"}; +{"key"=37.0;"subkey"="5";"value"="ddd"}; +{"key"=75.1;"subkey"="1";"value"="abc"}; +{"key"=150.0;"subkey"="1";"value"="aaa"}; +{"key"=150.0;"subkey"="3";"value"="iii"}; +{"key"=150.0;"subkey"="8";"value"="zzz"}; +{"key"=150.0;"subkey"="7";"value"="qqq"}; +{"key"=527.2;"subkey"="4";"value"="bbb"}; +{"key"=%inf;"subkey"="6";"value"="ccc"}; +{"key"=%+inf;"subkey"="11";"value"="qqq"}; +{"key"=%nan;"subkey"="2";"value"="kkk"}; +{"key"=%-inf;"subkey"="9";"value"="uuu"}; diff --git a/yql/essentials/tests/sql/suites/expr/double.txt.attr b/yql/essentials/tests/sql/suites/expr/double.txt.attr new file mode 100644 index 0000000000..f0f906d3f7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/double.txt.attr @@ -0,0 +1,7 @@ +{"_yql_row_spec"={ + "Type"=["StructType";[ + ["key";["DataType";"Double"]]; + ["subkey";["DataType";"String"]]; + ["value";["DataType";"String"]] + ]] +}} diff --git a/yql/essentials/tests/sql/suites/expr/double_join_with_list_from_range.cfg b/yql/essentials/tests/sql/suites/expr/double_join_with_list_from_range.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/double_join_with_list_from_range.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/double_join_with_list_from_range.sql b/yql/essentials/tests/sql/suites/expr/double_join_with_list_from_range.sql new file mode 100644 index 0000000000..9a430a536d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/double_join_with_list_from_range.sql @@ -0,0 +1,18 @@ +/* postgres can not */ +$list = select lst, row_number() over (order by lst) as rn from ( + select * from ( + select ListFromRange(1us, 333us) as lst + ) FLATTEN LIST by lst +); + +$usr = select value, CAST(key AS Uint16) + 3us AS age, CAST(key AS Uint16) + 7us as age1 +from plato.Input; + +select + u.*, + l1.rn as rn1, + l2.rn as rn2 +from $usr as u +join $list as l1 on u.age == l1.lst +join $list as l2 on u.age1 == l2.lst +order by value; diff --git a/yql/essentials/tests/sql/suites/expr/empty_dict_ops.sql b/yql/essentials/tests/sql/suites/expr/empty_dict_ops.sql new file mode 100644 index 0000000000..c6787ec825 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_dict_ops.sql @@ -0,0 +1,19 @@ +/* syntax version 1 */ +/* postgres can not */ +select + DictLength({}), + DictHasItems({}), + DictContains({},1), + DictLookup({},2), + DictKeys({}), + DictPayloads({}), + DictItems({}), + 3 in {}, + DictLength(Just({})), + DictHasItems(Just({})), + DictContains(Just({}),1), + DictLookup(Just({}),2), + DictKeys(Just({})), + DictPayloads(Just({})), + DictItems(Just({})), + 3 in Just({}); diff --git a/yql/essentials/tests/sql/suites/expr/empty_iterator.cfg b/yql/essentials/tests/sql/suites/expr/empty_iterator.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_iterator.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/empty_iterator.sql b/yql/essentials/tests/sql/suites/expr/empty_iterator.sql new file mode 100644 index 0000000000..0399f810a2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_iterator.sql @@ -0,0 +1,20 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$train = +SELECT * FROM Input WHERE key > "900" GROUP BY value; + +$method = ($stream) -> { + $func = Callable( + CallableType(0, TypeOf($stream), TypeOf($stream)), + ($_1) -> { return $_1; } + ); + RETURN $func($stream); +}; + +$prediction = + PROCESS $train + USING $method(TableRows()); + +SELECT * FROM $prediction; diff --git a/yql/essentials/tests/sql/suites/expr/empty_iterator2.cfg b/yql/essentials/tests/sql/suites/expr/empty_iterator2.cfg new file mode 100644 index 0000000000..e68191f82c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_iterator2.cfg @@ -0,0 +1 @@ +in Input input.txt
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/empty_iterator2.sql b/yql/essentials/tests/sql/suites/expr/empty_iterator2.sql new file mode 100644 index 0000000000..39164341d4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_iterator2.sql @@ -0,0 +1,20 @@ +/* syntax version 1 */ +/* postgres can not */ +USE plato; + +$train = +SELECT key, value FROM Input GROUP BY key, value having key > "900"; + +$method = ($stream) -> { + $func = Callable( + CallableType(0, TypeOf($stream), TypeOf($stream)), + ($_1) -> { return $_1; } + ); + RETURN $func($stream); +}; + +$prediction = + PROCESS $train + USING $method(TableRows()); + +SELECT * FROM $prediction; diff --git a/yql/essentials/tests/sql/suites/expr/empty_list_ops1.sql b/yql/essentials/tests/sql/suites/expr/empty_list_ops1.sql new file mode 100644 index 0000000000..cdf4eb2202 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_list_ops1.sql @@ -0,0 +1,60 @@ +/* syntax version 1 */ +/* postgres can not */ +select + ListLength([]), + Yql::Append([],1), + Yql::Prepend(2,[]), + Yql::Extend(), + Yql::Extend([],[]), + Yql::Extend([],[1]), + Yql::Extend([1],[]), + Yql::UnionAll(), + Yql::UnionAll([],[]), + Yql::UnionAll([],[<|a:1|>]), + Yql::UnionAll([<|a:1|>],[]), + ListExtend(), + ListExtend([],[]), + ListExtend([],[1]), + ListExtend([1],[]), + ListUnionAll(), + ListUnionAll([],[]), + ListUnionAll([],[<|a:1|>]), + ListUnionAll([<|a:1|>],[]), + Yql::Zip([]), + Yql::Zip([],[]), + Yql::Zip([],[1]), + Yql::Zip([1],[]), + Yql::ZipAll([]), + Yql::ZipAll([],[]), + Yql::ZipAll([],[1]), + Yql::ZipAll([1],[]), + ListZip([]), + ListZip([],[]), + ListZip([],[1]), + ListZip([1],[]), + ListZipAll([]), + ListZipAll([],[]), + ListZipAll([],[1]), + ListZipAll([1],[]), + + ListLength(Just([])), + ListExtend(), + ListExtend(Just([]),[]), + ListExtend(Just([]),Just([])), + ListExtend(Just([]),[1]), + ListExtend([1],Just([])), + ListUnionAll(), + ListUnionAll(Just([]),[]), + ListUnionAll(Just([]),Just([])), + ListUnionAll(Just([]),[<|a:1|>]), + ListUnionAll([<|a:1|>],Just([])), + ListZip(Just([])), + ListZip(Just([]),[]), + ListZip(Just([]),Just([])), + ListZip(Just([]),[1]), + ListZip([1],Just([])), + ListZipAll(Just([])), + ListZipAll(Just([]),[]), + ListZipAll(Just([]),Just([])), + ListZipAll(Just([]),[1]), + ListZipAll([1],Just([])); diff --git a/yql/essentials/tests/sql/suites/expr/empty_list_ops2.sql b/yql/essentials/tests/sql/suites/expr/empty_list_ops2.sql new file mode 100644 index 0000000000..a98298280b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_list_ops2.sql @@ -0,0 +1,52 @@ +/* syntax version 1 */ +/* postgres can not */ +select + ListHasItems([]), + Yql::Filter([],($x)->{return $x}), + Yql::Map([],($x)->{return $x*2}), + Yql::FlatMap([],($x)->{return Just($x*2)}), + Yql::OrderedFilter([],($x)->{return $x}), + Yql::OrderedMap([],($x)->{return $x*2}), + Yql::OrderedFlatMap([],($x)->{return Just($x*2)}), + ListFilter([],($x)->{return $x}), + ListMap([],($x)->{return $x*2}), + ListFlatMap([],($x)->{return Just($x*2)}), + ListCollect([]), + ListSort([]), + ListReverse([]), + ListEnumerate([]), + ListSkip([], 1), + ListTake([], 1), + ListIndexOf([], 1), + ListUniq([]), + ListAny([]), + ListAll([]), + ListHas([],1), + ListHead([]), + ListLast([]), + ListMin([]), + ListMax([]), + ListSum([]), + ListAvg([]), + + ListHasItems(Just([])), + ListFilter(Just([]),($x)->{return $x}), + ListMap(Just([]),($x)->{return $x*2}), + ListFlatMap(Just([]),($x)->{return Just($x*2)}), + ListCollect(Just([])), + ListSort(Just([])), + ListReverse(Just([])), + ListEnumerate(Just([])), + ListSkip(Just([]), 1), + ListTake(Just([]), 1), + ListUniq(Just([])), + ListAny(Just([])), + ListAll(Just([])), + ListHas(Just([]),1), + ListHead(Just([])), + ListLast(Just([])), + ListMin(Just([])), + ListMax(Just([])), + ListSum(Just([])), + ListAvg(Just([])), + diff --git a/yql/essentials/tests/sql/suites/expr/empty_list_ops3.sql b/yql/essentials/tests/sql/suites/expr/empty_list_ops3.sql new file mode 100644 index 0000000000..f7db80ed73 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_list_ops3.sql @@ -0,0 +1,34 @@ +/* syntax version 1 */ +/* postgres can not */ +$pred = ($x)->{ return $x > 0 }; + +select + ListExtract([],"a"), + ListTakeWhile([], $pred), + ListSkipWhile([], $pred), + ListTakeWhileInclusive([], $pred), + ListSkipWhileInclusive([], $pred), + ListAggregate([], AGGREGATION_FACTORY("Sum")), + ToDict([]), + ToMultiDict([]), + ToSet([]), + SetIsDisjoint({1, 2, 3}, []), + SetIncludes({1, 2, 3}, []), + Yql::Fold([],0,($x,$y)->{return $x+$y}), + Yql::Fold1([],($x)->{return $x},($x,$y)->{return $x+$y}), + Yql::FoldMap([],0,($x,$y)->{return ($x+$y,$y)}), + Yql::Fold1Map([],($x)->{return ($x,$x)},($x,$y)->{return ($x+$y,$y)}), + Yql::LazyList([]), + + ListExtract(Just([]),"a"), + ListTakeWhile(Just([]), $pred), + ListSkipWhile(Just([]), $pred), + ListTakeWhileInclusive(Just([]), $pred), + ListSkipWhileInclusive(Just([]), $pred), + ListAggregate(Just([]), AGGREGATION_FACTORY("Sum")), + ToDict(Just([])), + ToMultiDict(Just([])), + ToSet(Just([])), + SetIsDisjoint({1, 2, 3}, Just([])), + SetIncludes({1, 2, 3}, Just([])), +
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/empty_struct_tuple_types.sql b/yql/essentials/tests/sql/suites/expr/empty_struct_tuple_types.sql new file mode 100644 index 0000000000..14a1aea9d2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/empty_struct_tuple_types.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +/* postgres can not */ +select + FormatType(Tuple<>), + FormatType(Tuple< >), + FormatType(Struct<>), + FormatType(Struct< + -- whitespace + >); diff --git a/yql/essentials/tests/sql/suites/expr/ensure_ok.sql b/yql/essentials/tests/sql/suites/expr/ensure_ok.sql new file mode 100644 index 0000000000..f4a40c0e40 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_ok.sql @@ -0,0 +1 @@ +select Ensure(42, true, "message"); diff --git a/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.cfg b/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.cfg new file mode 100644 index 0000000000..5dae597903 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.sql b/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.sql new file mode 100644 index 0000000000..4ea3509678 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_runtime_fail.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select Ensure(42, 2 != 2, "message"); diff --git a/yql/essentials/tests/sql/suites/expr/ensure_type_fail.cfg b/yql/essentials/tests/sql/suites/expr/ensure_type_fail.cfg new file mode 100644 index 0000000000..eb2e5315d1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_type_fail.cfg @@ -0,0 +1 @@ +xfail
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/ensure_type_fail.sql b/yql/essentials/tests/sql/suites/expr/ensure_type_fail.sql new file mode 100644 index 0000000000..08ae648b5c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/ensure_type_fail.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ +/* syntax version 1 */ + +select EnsureType(42, String, "message"); diff --git a/yql/essentials/tests/sql/suites/expr/evaluate_parse_inf_nan.cfg b/yql/essentials/tests/sql/suites/expr/evaluate_parse_inf_nan.cfg new file mode 100644 index 0000000000..88d007fa60 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/evaluate_parse_inf_nan.cfg @@ -0,0 +1,3 @@ +in Input double.txt +providers yt + diff --git a/yql/essentials/tests/sql/suites/expr/evaluate_parse_inf_nan.sql b/yql/essentials/tests/sql/suites/expr/evaluate_parse_inf_nan.sql new file mode 100644 index 0000000000..ee49564f89 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/evaluate_parse_inf_nan.sql @@ -0,0 +1,6 @@ +/* syntax version 1 */ +use plato; + +$foo = process Input; +select ListSort(EvaluateExpr($foo), ($item) -> (AsTuple($item.key, $item.subkey))); + diff --git a/yql/essentials/tests/sql/suites/expr/exapnd_with_singular_types.sql b/yql/essentials/tests/sql/suites/expr/exapnd_with_singular_types.sql new file mode 100644 index 0000000000..20169c93b0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/exapnd_with_singular_types.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +$null_t = TypeOf(null); +$struct_t = Struct<a:String, b:Int32?, c:$null_t, d:pgtext>; +$callable = Callable(Callable<($struct_t)->$struct_t>, ($x) -> { return $x; }); + +select $callable(<|a:"1"|>), $callable(AddMember(<||>, "a", "2")); diff --git a/yql/essentials/tests/sql/suites/expr/expr_add_literal_nulls.sql b/yql/essentials/tests/sql/suites/expr/expr_add_literal_nulls.sql new file mode 100644 index 0000000000..521fcde8f0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_add_literal_nulls.sql @@ -0,0 +1 @@ +select NULL + 1, NULL + NULL;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/expr_cast.sql b/yql/essentials/tests/sql/suites/expr/expr_cast.sql new file mode 100644 index 0000000000..b03d53d63d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_cast.sql @@ -0,0 +1 @@ +select cast(3.14 as Uint32);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda.sql b/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda.sql new file mode 100644 index 0000000000..4c65330519 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda.sql @@ -0,0 +1,2 @@ +$foo = YQL::@@(lambda '(item) (+ item item))@@; +select $foo(1); diff --git a/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda_multiline_atoms.sql b/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda_multiline_atoms.sql new file mode 100644 index 0000000000..8809853db4 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda_multiline_atoms.sql @@ -0,0 +1,3 @@ +$foo = YQL::@@(lambda '(item) (Concat (String '@@@@foo@@@@@@@@ +@@@@) item))@@; +select $foo("bar"); diff --git a/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda_quotes.sql b/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda_quotes.sql new file mode 100644 index 0000000000..e4169a16b5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_named_yql_lambda_quotes.sql @@ -0,0 +1,3 @@ +$foo1 = YQL::"(lambda '(item) (Concat (String '\"foo\\\"\") item))"; +$foo2 = YQL::'(lambda \'(item) (Concat (String \'"foo\\\'") item))'; +select $foo1("bar"), $foo2("bar"); diff --git a/yql/essentials/tests/sql/suites/expr/expr_null.sql b/yql/essentials/tests/sql/suites/expr/expr_null.sql new file mode 100644 index 0000000000..8d6bd3be1a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_null.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select NULL; diff --git a/yql/essentials/tests/sql/suites/expr/expr_op_in_paren.sql b/yql/essentials/tests/sql/suites/expr/expr_op_in_paren.sql new file mode 100644 index 0000000000..1d63ebbafa --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_op_in_paren.sql @@ -0,0 +1 @@ +select (1 + 2);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/expr_trivial_named.sql b/yql/essentials/tests/sql/suites/expr/expr_trivial_named.sql new file mode 100644 index 0000000000..ff097ae0a0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_trivial_named.sql @@ -0,0 +1,2 @@ +$foo = 100500; +select $foo; diff --git a/yql/essentials/tests/sql/suites/expr/expr_yql_data.sql b/yql/essentials/tests/sql/suites/expr/expr_yql_data.sql new file mode 100644 index 0000000000..94a7860a70 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_yql_data.sql @@ -0,0 +1 @@ +select YQL::@@(Uint32 '100500)@@, YQL::@@(String '[WAT])@@;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/expr_yql_from_string.sql b/yql/essentials/tests/sql/suites/expr/expr_yql_from_string.sql new file mode 100644 index 0000000000..8d6ff8efba --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_yql_from_string.sql @@ -0,0 +1 @@ +select YQL::FromString("100500", YQL::@@'Uint64@@);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/expr_yql_function.sql b/yql/essentials/tests/sql/suites/expr/expr_yql_function.sql new file mode 100644 index 0000000000..979c482de2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/expr_yql_function.sql @@ -0,0 +1 @@ +select YQL::ToString(1 + 2);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/extra_opt_try_member.cfg b/yql/essentials/tests/sql/suites/expr/extra_opt_try_member.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/extra_opt_try_member.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/expr/extra_opt_try_member.sql b/yql/essentials/tests/sql/suites/expr/extra_opt_try_member.sql new file mode 100644 index 0000000000..9de0bf109a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/extra_opt_try_member.sql @@ -0,0 +1,22 @@ +/* postgres can not */ +/* syntax version 1 */ +/* yt can not */ + +$s = <|a:1, b:2u/1u, c:Just(Just(1))|>; +$js = Just($s); +$es = Nothing(Struct<a:Int32, b:Uint32?, c:Int32??>?); + +-- TypeOf TryMember is type of third argument +-- field type should either match third type exactly, or (if the third type is optional) +-- Optional(field) should be equal to third type +select + TryMember($s, "b", Just(Just(99u))), + TryMember($s, "c", Just(Just(Just(2)))), + + TryMember($js, "b", Just(Just(999u))), + TryMember($js, "c", Just(Just(Just(999)))), + + TryMember($es, "b", Just(Just(999u))), + TryMember($es, "c", Just(Just(Just(999)))), +; + diff --git a/yql/essentials/tests/sql/suites/expr/fallback_filternullelements.sql b/yql/essentials/tests/sql/suites/expr/fallback_filternullelements.sql new file mode 100644 index 0000000000..27e7d67ce2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/fallback_filternullelements.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +$lists = AsList(AsList("one", "two", "three"), AsList("head", null), AsList(null, "tail"), ListCreate(String?)); +$map = ($l) -> { RETURN AsTuple(ListHead($l), ListLast($l)) }; +$structs = ListMap($lists, $map); +SELECT YQL::FilterNullElements($structs), YQL::SkipNullElements($structs); diff --git a/yql/essentials/tests/sql/suites/expr/fallback_filternullmembers.sql b/yql/essentials/tests/sql/suites/expr/fallback_filternullmembers.sql new file mode 100644 index 0000000000..73a212aacd --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/fallback_filternullmembers.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +$lists = AsList(AsList("one", "two", "three"), AsList("head", null), AsList(null, "tail"), ListCreate(String?)); +$map = ($l) -> { RETURN AsStruct(ListHead($l) AS head, ListLast($l) AS tail) }; +$structs = ListMap($lists, $map); +SELECT YQL::FilterNullMembers($structs), YQL::SkipNullMembers($structs); diff --git a/yql/essentials/tests/sql/suites/expr/flatmap_by_map_lazy_list.sql b/yql/essentials/tests/sql/suites/expr/flatmap_by_map_lazy_list.sql new file mode 100644 index 0000000000..38473e2f56 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/flatmap_by_map_lazy_list.sql @@ -0,0 +1,6 @@ +$lazy = ListFromRange(1us, 3us); +$list = AsList("one", "two"); + +SELECT ListFlatMap($list, ($l)->{ RETURN ListMap($lazy, ($r)->{ RETURN AsTuple($l, $r) })}); +SELECT ListFlatMap($lazy, ($l)->{ RETURN ListMap($list, ($r)->{ RETURN AsTuple($l, $r) })}); + diff --git a/yql/essentials/tests/sql/suites/expr/formattypediff.sql b/yql/essentials/tests/sql/suites/expr/formattypediff.sql new file mode 100644 index 0000000000..4af4656733 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/formattypediff.sql @@ -0,0 +1,7 @@ +SELECT + FormatTypeDiff(ParseType("Tuple<Tuple<Int32>, Tuple<String, Double>>"), ParseType("Tuple<String, Tuple<String, String>>")), + FormatTypeDiffPretty(ParseType("Tuple<Tuple<Int32>, Tuple<String, Double>>"), ParseType("Tuple<String, Tuple<String, String>>")), + FormatTypeDiffPretty(TypeHandle(ParseType("Tuple<Tuple<Int32>, Tuple<String, Double>>")), TypeHandle(ParseType("Tuple<String, Tuple<String, String>>"))), + FormatTypeDiff(ParseType("Tuple<Tuple<Int32>, Tuple<String, Double>>"), TypeHandle(ParseType("Tuple<String, Tuple<String, String>>"))), + FormatTypeDiffPretty(TypeHandle(ParseType("Tuple<Tuple<Int32>, Tuple<String, Double>>")), ParseType("Tuple<String, Tuple<String, String>>")), + FormatTypeDiff(TypeHandle(ParseType("Int32")), TypeHandle(ParseType("Int32")))
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.cfg b/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.cfg new file mode 100644 index 0000000000..5dae597903 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.sql b/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.sql new file mode 100644 index 0000000000..b30ed85ce3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/implicit_bitcast_fail.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +pragma warning("error", "1107"); +select AsList( +7498311229109140978, +254610204336699107, +11580367904009864964); diff --git a/yql/essentials/tests/sql/suites/expr/implicit_cast_literals_under_if.sql b/yql/essentials/tests/sql/suites/expr/implicit_cast_literals_under_if.sql new file mode 100644 index 0000000000..8bcfdcf84b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/implicit_cast_literals_under_if.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +select + IF(3 < 2, 13, 42U) as i32_then, + IF(3 < 2, 13U, 42) as i32_else, + IF(3 < 2, 13L, 42U) as i64_then, + IF(3 < 2, 13U, 42L) as i64_else; diff --git a/yql/essentials/tests/sql/suites/expr/implicit_cast_literals_under_listfromrange.sql b/yql/essentials/tests/sql/suites/expr/implicit_cast_literals_under_listfromrange.sql new file mode 100644 index 0000000000..b7e8b25293 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/implicit_cast_literals_under_listfromrange.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +SELECT + ListFromRange(13, 42U), + ListFromRange(13U, 42), + ListFromRange(13, null, 42U), + ListFromRange(13U, null, 42); diff --git a/yql/essentials/tests/sql/suites/expr/inline_call.cfg b/yql/essentials/tests/sql/suites/expr/inline_call.cfg new file mode 100644 index 0000000000..2c6a0d040b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/inline_call.cfg @@ -0,0 +1,3 @@ +in Input input.txt +udf math_udf + diff --git a/yql/essentials/tests/sql/suites/expr/inline_call.sql b/yql/essentials/tests/sql/suites/expr/inline_call.sql new file mode 100644 index 0000000000..f93b2b64fc --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/inline_call.sql @@ -0,0 +1,28 @@ +/* postgres can not */ +/* syntax version 1 */ +use plato; + +$x = AsStruct(1 as a); +select $x.a; + +$y = AsTuple(2,3); +select $y.1; + +select length("foo"); + +select Math::Pi(); + +$f = () -> { + return () -> { + return AsDict(AsTuple("foo",AsList(AsStruct(AsTuple(1) as bar)))); + } +}; + +select $f()()["foo"][0].bar.0; + +select ()->{return 1}(); + +$type = Callable<()->List<Int32>>; +$g = AsStruct(Yql::Callable($type, ()->{return AsList(1,2,3)}) as foo); + +select $g.foo()[0]; diff --git a/yql/essentials/tests/sql/suites/expr/input.txt b/yql/essentials/tests/sql/suites/expr/input.txt new file mode 100644 index 0000000000..65949ea745 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/input.txt @@ -0,0 +1,4 @@ +{"key"="075";"subkey"="1";"value"="abc"}; +{"key"="800";"subkey"="2";"value"="ddd"}; +{"key"="020";"subkey"="3";"value"="q"}; +{"key"="150";"subkey"="4";"value"="qzz"}; diff --git a/yql/essentials/tests/sql/suites/expr/int_literals.sql b/yql/essentials/tests/sql/suites/expr/int_literals.sql new file mode 100644 index 0000000000..9d860f731f --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/int_literals.sql @@ -0,0 +1,8 @@ +/* postgres can not */ +select + 23 as small_int, + 9262583611491805930 as unsigned_long, + 0x7fffffff as i32max, + 0x80000000 as i32max_plus1, + 0x7fffffffffffffff as i64max, + 0x8000000000000000 as i64max_plus1 diff --git a/yql/essentials/tests/sql/suites/expr/int_literals_negative.sql b/yql/essentials/tests/sql/suites/expr/int_literals_negative.sql new file mode 100644 index 0000000000..2e8914827e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/int_literals_negative.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +select -9223372036854775808 as i64_min; +select 9223372036854775807 as i64_max; +select 9223372036854775808 as u64; + +select -2147483648 as i32_min; +select 2147483647 as i32_max; + +select 2147483648 as i64; +select 4294967295 as i64; + +select -32768 as i32; + +select 18446744073709551615 as u64_max; diff --git a/yql/essentials/tests/sql/suites/expr/int_literals_negative_typed.sql b/yql/essentials/tests/sql/suites/expr/int_literals_negative_typed.sql new file mode 100644 index 0000000000..9fb8777dfa --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/int_literals_negative_typed.sql @@ -0,0 +1,19 @@ +/* syntax version 1 */ +select -128t as i8_min; +select 127t as i8_max; + +select -32768s as i16_min; +select 32767s as i16_max; + +select -9223372036854775808l as i64_min; +select 9223372036854775807l as i64_max; + + +select 255ut as u8_max; +select 65535us as u16_max; +select 18446744073709551615ul as u64_max; + + +select -128ut; +select -32768us; +select -9223372036854775808ul; diff --git a/yql/essentials/tests/sql/suites/expr/is_null.sql b/yql/essentials/tests/sql/suites/expr/is_null.sql new file mode 100644 index 0000000000..e583901a72 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/is_null.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +select + 1/0 is NULL, + 1/0 isnull, + 1/0 is not NULL, + 1/0 notnull; diff --git a/yql/essentials/tests/sql/suites/expr/iterable.sql b/yql/essentials/tests/sql/suites/expr/iterable.sql new file mode 100644 index 0000000000..ebac483bf6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/iterable.sql @@ -0,0 +1,16 @@ +/* postgres can not */ +/* syntax version 1 */ +$a = Yql::ToList(()->(Yql::Iterator([1,2,3]))); +select ListExtend($a, $a), ListHasItems($a), ListLength($a); + +$b = Yql::ToList(()->(Yql::EmptyIterator(Stream<Int32>))); +select ListExtend($b, $b), ListHasItems($b), ListLength($b); + +$c = Yql::ToList(()->(Yql::EmptyIterator(Stream<Int32>))); +select ListExtend($b, $b), ListLength($c), ListHasItems($c); + +select ListMap(ListFromRange(1,4), ($x)->{ + $y = Yql::ToList(()->(Yql::Iterator([1,2,$x]))); + return ListExtend($y, $y); + }); +
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/lambda_as_optional_callable.sql b/yql/essentials/tests/sql/suites/expr/lambda_as_optional_callable.sql new file mode 100644 index 0000000000..5700bc9a71 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/lambda_as_optional_callable.sql @@ -0,0 +1,6 @@ +$callbackType = Callable<(Int32)->Int32>; +$defaultFunc = Callable($callbackType, ($x)->($x)); +$outerType = CallableType(1, Int32, Int32, Optional<$callbackType>); +$outer = Callable($outerType, ($x,$f)->(($f ?? $defaultFunc)($x))); + +select $outer(1,($y)->($y+1)), $outer(1); diff --git a/yql/essentials/tests/sql/suites/expr/lds_empty_cast.sql b/yql/essentials/tests/sql/suites/expr/lds_empty_cast.sql new file mode 100644 index 0000000000..fc3eae8e13 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/lds_empty_cast.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ +/* postgres can not */ +select + Yql::Append(AsList(ListCreate(Int32)), []), + Yql::Append(AsList(DictCreate(Int32, String)), {}), + cast([] as List<Int32>), + cast({} as Dict<Int32, String>), + cast({} as Set<Int32>), + AsList(ListCreate(Int32),[]), + AsList([],ListCreate(Int32)), + AsList(DictCreate(Int32, String),{}), + AsList({},DictCreate(Int32, String)), + AsList(SetCreate(Int32),{}), + AsList({},SetCreate(Int32)); diff --git a/yql/essentials/tests/sql/suites/expr/lds_empty_compare.sql b/yql/essentials/tests/sql/suites/expr/lds_empty_compare.sql new file mode 100644 index 0000000000..5dc4188f02 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/lds_empty_compare.sql @@ -0,0 +1,60 @@ +/* syntax version 1 */ +/* postgres can not */ +select + Yql::AggrEquals([],[]), + Yql::AggrNotEquals([],[]), + Yql::AggrLess([],[]), + Yql::AggrLessOrEqual([],[]), + Yql::AggrGreater([],[]), + Yql::AggrGreaterOrEqual([],[]), + + Yql::AggrEquals({},{}), + Yql::AggrNotEquals({},{}), + + [] = [], + [] = ListCreate(Int32), + ListCreate(Int32) = [], + [] = [1], + [1] = [], + + [] != [], + [] != ListCreate(Int32), + ListCreate(Int32) != [], + [] != [1], + [1] != [], + + [] < [], + [] < ListCreate(Int32), + ListCreate(Int32) < [], + [] < [1], + [1] < [], + + [] <= [], + [] <= ListCreate(Int32), + ListCreate(Int32) <= [], + [] <= [1], + [1] <= [], + + [] > [], + [] > ListCreate(Int32), + ListCreate(Int32) > [], + [] > [1], + [1] > [], + + [] >= [], + [] >= ListCreate(Int32), + ListCreate(Int32) >= [], + [] >= [1], + [1] >= [], + + {} = {}, + {} = SetCreate(Int32), + SetCreate(Int32) = {}, + {} = {1}, + {1} = {}, + + {} != {}, + {} != SetCreate(Int32), + SetCreate(Int32) != {}, + {} != {1}, + {1} != {}, diff --git a/yql/essentials/tests/sql/suites/expr/lds_literal.sql b/yql/essentials/tests/sql/suites/expr/lds_literal.sql new file mode 100644 index 0000000000..a71723f9f0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/lds_literal.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select [],[,],[1,],[1,2,3],{},{,},{1,1,},{"a":1,"a":2}; diff --git a/yql/essentials/tests/sql/suites/expr/lds_types.sql b/yql/essentials/tests/sql/suites/expr/lds_types.sql new file mode 100644 index 0000000000..69e4a241c0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/lds_types.sql @@ -0,0 +1,9 @@ +/* syntax version 1 */ +/* postgres can not */ +select + FormatType(EmptyList), TypeKind(TypeHandle(EmptyList)), + FormatType(ParseType("EmptyList")), EmptyList(), + FormatType(EvaluateType(EmptyListTypeHandle())), + FormatType(EmptyDict), TypeKind(TypeHandle(EmptyDict)), + FormatType(ParseType("EmptyDict")), EmptyDict(), + FormatType(EvaluateType(EmptyDictTypeHandle())); diff --git a/yql/essentials/tests/sql/suites/expr/len.cfg b/yql/essentials/tests/sql/suites/expr/len.cfg new file mode 100644 index 0000000000..58878f8945 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/len.cfg @@ -0,0 +1 @@ +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/len.sql b/yql/essentials/tests/sql/suites/expr/len.sql new file mode 100644 index 0000000000..9e35dda177 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/len.sql @@ -0,0 +1,5 @@ +USE plato; + +SELECT LEN("My String"); + +SELECT LEN(key) from Input; diff --git a/yql/essentials/tests/sql/suites/expr/length_of_optional_set_intesection.sql b/yql/essentials/tests/sql/suites/expr/length_of_optional_set_intesection.sql new file mode 100644 index 0000000000..f1866cba94 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/length_of_optional_set_intesection.sql @@ -0,0 +1 @@ +SELECT ListLength(SetIntersection(ToSet(AsList("1", "2")), ToSet(Just(AsList("2", "3", "4", "5")))));
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/list_builtins.cfg b/yql/essentials/tests/sql/suites/expr/list_builtins.cfg new file mode 100644 index 0000000000..5fee00e994 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_builtins.cfg @@ -0,0 +1 @@ +udf python3_udf diff --git a/yql/essentials/tests/sql/suites/expr/list_builtins.sql b/yql/essentials/tests/sql/suites/expr/list_builtins.sql new file mode 100644 index 0000000000..66dd5a0fcf --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_builtins.sql @@ -0,0 +1,70 @@ +/* postgres can not */ +/* syntax version 1 */ +$list = AsList(3, 1, 2, 3); +$other_list = AsList(4); +$bool_list = AsList(true, false, true); +$struct_list = AsList( + AsStruct(1 AS one, 2 AS two), + AsStruct(-1 AS one, -2 AS two) +); + +$script = @@ +def formula(a, b, c, d): + return a * b + c // d +@@; +$udf = Python3::formula( + Callable<(Int64, Int64, Int64, Int64)->Int64>, + $script +); + +$lambdaSum = ($x, $y) -> { RETURN $x + $y; }; +$lambdaMult = ($x) -> { RETURN 4 * $x; }; +$lambdaTuple = ($i, $s) -> { RETURN ($i * $s, $i + $s); }; +$lambdaInc = ($i) -> { RETURN ($i + 1, $i + 2); }; + +SELECT + ListLength($list) AS length, + ListExtend($list, $other_list) AS extend, + ListZip($list, $other_list) AS zip, + ListZipAll($list, $other_list) AS zipall, + ListEnumerate($list) AS enumerate, + ListReverse($list) AS reverse, + ListSkip($list, 2) AS skip, + ListTake($list, 2) AS take, + ListSort($list) AS sort, + ListSort($struct_list, ($x) -> { return $x.two; }) AS sort_structs, + ListMap($list, ($item) -> { return $udf($item, 6, 4, 2); }) AS map, + ListFlatMap($list, ($item) -> { return $item / 0; }) AS flatmap, + ListFilter($list, ($item) -> { return $item < 3; }) AS filter, + ListAny($bool_list) AS any, + ListAll($bool_list) AS all, + ListMax($list) AS max, + ListMin($list) AS min, + ListSum($list) AS sum, + ListAvg($list) AS avg, + ListUniq($list) AS uniq, + ListConcat(ListMap($list, ($item) -> { return CAST($item AS String); })) AS concat, + ListExtract($struct_list, "two") AS extract, + ListMap($list, ($item) -> { return CAST($item AS Double);}), + ListCreate(Tuple<Int64,Double>), + ListCreate(TypeOf("foo")), + ListFold($list, 6, $lambdaSum), + ListFold([], 3, $lambdaSum), + ListFold(Just($list), 6, $lambdaSum), + ListFold(Just([]), 3, $lambdaSum), + ListFold(Null, 3, $lambdaSum), + ListFold1($list, $lambdaMult, $lambdaSum), + ListFold1([], $lambdaMult, $lambdaSum), + ListFold1(Just($list), $lambdaMult, $lambdaSum), + ListFold1(Just([]), $lambdaMult, $lambdaSum), + ListFold1(Null, $lambdaMult, $lambdaSum), + ListFoldMap($list, 1, $lambdaTuple), + ListFoldMap([], 1, $lambdaTuple), + ListFoldMap(Just($list), 1, $lambdaTuple), + ListFoldMap(Just([]), 1, $lambdaTuple), + ListFoldMap(Null, 1, $lambdaTuple), + ListFold1Map($list, $lambdaInc, $lambdaTuple), + ListFold1Map([], $lambdaInc, $lambdaTuple), + ListFold1Map(Just($list), $lambdaInc, $lambdaTuple), + ListFold1Map(Just([]), $lambdaInc, $lambdaTuple), + ListFold1Map(Null, $lambdaInc, $lambdaTuple); diff --git a/yql/essentials/tests/sql/suites/expr/list_builtins_opt.sql b/yql/essentials/tests/sql/suites/expr/list_builtins_opt.sql new file mode 100644 index 0000000000..535c47cfe7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_builtins_opt.sql @@ -0,0 +1,34 @@ +/* postgres can not */ +/* syntax version 1 */ + +$list0 = AsList("a","b"); +$list1 = AsList(Just("a"), "b", Nothing(ParseType("String?"))); +$list2 = Just(AsList("a","b")); +$list3 = Just(AsList(Just("a"), "b", Nothing(ParseType("String?")))); + +$list_empty0 = ListCreate(ParseType("String")); +$list_empty1 = ListCreate(ParseType("String?")); +$list_empty2 = Just(ListCreate(ParseType("String"))); +$list_empty3 = Just(ListCreate(ParseType("String?"))); + + +$list_null0 = Just(AsList(Nothing(ParseType("String?")))); +$list_null1 = Nothing(ParseType("List<String?>?")); + +$list_min = Just(AsList(Just(Just(Just("a"))), "b")); + + +select ListConcat($list0) as list0, + ListConcat($list1) as list1, + ListConcat($list2) as list2, + ListConcat($list3) as list3, + + ListConcat($list_empty0) as list_empty0, + ListConcat($list_empty1) as list_empty1, + ListConcat($list_empty2) as list_empty2, + ListConcat($list_empty3) as list_empty3, + + ListConcat($list_null0) as list_null0, + ListConcat($list_null1) as list_null1, + + ListMin($list_min) as list_min; diff --git a/yql/essentials/tests/sql/suites/expr/list_comp.sql b/yql/essentials/tests/sql/suites/expr/list_comp.sql new file mode 100644 index 0000000000..fb3b91f27e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_comp.sql @@ -0,0 +1,70 @@ +/* postgres can not */ +select AsTuple( +AsList(1,2) < AsList(1u,3u), +AsList(1,2) < AsList(1u,2u), +AsList(1,2) < AsList(1u,1u), +AsList(1,2) < AsList(2u,1u), +AsList(1,2) < AsList(2u,3u), +AsList(1,2/1) < AsList(1u,3u), +AsList(1,2/1) < AsList(1u,2u), +AsList(1,2/1) < AsList(1u,1u), +AsList(1,2/0) < AsList(2u,3u), +AsList(1,2/0) < AsList(1u,3u), +); + +select AsTuple( +AsList(1,2) <= AsList(1u,3u), +AsList(1,2) <= AsList(1u,2u), +AsList(1,2) <= AsList(1u,1u), +AsList(1,2) <= AsList(2u,1u), +AsList(1,2) <= AsList(2u,3u), +AsList(1,2/1) <= AsList(1u,3u), +AsList(1,2/1) <= AsList(1u,2u), +AsList(1,2/1) <= AsList(1u,1u), +AsList(1,2/0) <= AsList(2u,3u), +AsList(1,2/0) <= AsList(1u,3u), +); + +select AsTuple( +AsList(1,2) > AsList(1u,3u), +AsList(1,2) > AsList(1u,2u), +AsList(1,2) > AsList(1u,1u), +AsList(1,2) > AsList(2u,1u), +AsList(1,2) > AsList(2u,3u), +AsList(1,2/1) > AsList(1u,3u), +AsList(1,2/1) > AsList(1u,2u), +AsList(1,2/1) > AsList(1u,1u), +AsList(1,2/0) > AsList(2u,3u), +AsList(1,2/0) > AsList(1u,3u), +); + +select AsTuple( +AsList(1,2) >= AsList(1u,3u), +AsList(1,2) >= AsList(1u,2u), +AsList(1,2) >= AsList(1u,1u), +AsList(1,2) >= AsList(2u,1u), +AsList(1,2) >= AsList(2u,3u), +AsList(1,2/1) >= AsList(1u,3u), +AsList(1,2/1) >= AsList(1u,2u), +AsList(1,2/1) >= AsList(1u,1u), +AsList(1,2/0) >= AsList(2u,3u), +AsList(1,2/0) >= AsList(1u,3u), +); + +select AsTuple ( +AsList(1,2,3) == AsList(1u,2u), +AsList(1/1,2/1) == AsList(1u,2u), +AsList(1/1,2/0) == AsList(1u,2u) +); + +select AsTuple( +AsList(1,2) < AsList(1u,2u,3u), +AsList(1,2) <= AsList(1u,2u,3u), +AsList(1,2) > AsList(1u,2u,3u), +AsList(1,2) >= AsList(1u,2u,3u), + +AsList(1,2,3) < AsList(1u,2u), +AsList(1,2,3) <= AsList(1u,2u), +AsList(1,2,3) > AsList(1u,2u), +AsList(1,2,3) >= AsList(1u,2u), +); diff --git a/yql/essentials/tests/sql/suites/expr/list_concat.sql b/yql/essentials/tests/sql/suites/expr/list_concat.sql new file mode 100644 index 0000000000..9f22a3c670 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_concat.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +/* syntax version 1 */ +$list = ["one","two","three","four","five"]; + +SELECT + ListConcat([], "."), + ListConcat($list), + ListConcat($list, ";"), + ListConcat($list, Just(", ")), + ListConcat($list, NULL), + ListConcat($list, Nothing(String?)), + ListConcat(["single"], "tail"); diff --git a/yql/essentials/tests/sql/suites/expr/list_extend.sql b/yql/essentials/tests/sql/suites/expr/list_extend.sql new file mode 100644 index 0000000000..337af69645 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_extend.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +/* syntax version 1 */ +PRAGMA warning("disable", "1107"); +SELECT + ListExtend([2u], [3s], [4l]), + ListExtend(1, "String", 123, null), + ListExtendStrict(1, "String", 123, null), + ListExtend([3s], [4], Just([5l])), + ListExtend([1u], [2u], Nothing(List<Int32>?)), + ListExtendStrict([1u], [2u], Nothing(List<UInt32>?)), + ListExtendStrict([1u], [2u], [4u, 3u]), + ListExtendStrict([1u], Just([2u]), [], [5u, 6u]), +; diff --git a/yql/essentials/tests/sql/suites/expr/list_flat_map_deprecated_opt.sql b/yql/essentials/tests/sql/suites/expr/list_flat_map_deprecated_opt.sql new file mode 100644 index 0000000000..70ce023824 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_flat_map_deprecated_opt.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* syntax version 1 */ +select ListFlatMap([1,2,null],($x)->(10 + $x)); diff --git a/yql/essentials/tests/sql/suites/expr/list_flatten.sql b/yql/essentials/tests/sql/suites/expr/list_flatten.sql new file mode 100644 index 0000000000..4a4eda4b46 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_flatten.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +/* syntax version 1 */ +select ListFlatten(null),ListFlatten([]), + ListFlatten(Just([])),ListFlatten(Nothing(EmptyList?)), + ListFlatten([[],[]]),ListFlatten([null,null]), + ListFlatten([Just([]),Just([])]), + ListFlatten(Just([[],[]])),ListFlatten(Just([null,null])), + ListFlatten(Just([Just([]),Just([])])); + +select ListFlatten([[1,2],[3,4]]), + ListFlatten([[1,2],null,[3,4]]), + ListFlatten(Just([[1,2],[3,4]])), + ListFlatten(Just([[1,2],null,[3,4]])), + ListFlatten([Just([1,2]),Just([3,4])]), + ListFlatten(Just([Just([1,2]),Just([3,4])]));
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/list_from_range.sql b/yql/essentials/tests/sql/suites/expr/list_from_range.sql new file mode 100644 index 0000000000..fb5d384542 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_from_range.sql @@ -0,0 +1,10 @@ +/* postgres can not */ +select +ListFromRange(3,0,-1), +ListFromRange(3,0), +ListFromRange(0,3,-1), +ListFromRange(0,3), +ListFromRange(0,3,-0.5), +ListFromRange(0,3,+0.5), +ListFromRange(0u,7u,2u), +ListFromRange(7u,0u,-2); diff --git a/yql/essentials/tests/sql/suites/expr/list_from_range_opt.sql b/yql/essentials/tests/sql/suites/expr/list_from_range_opt.sql new file mode 100644 index 0000000000..84907a1664 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_from_range_opt.sql @@ -0,0 +1,26 @@ +SELECT ListFromRange(1, 3), + ListFromRange(1, Just(3)), + ListFromRange(Just(1), Just(3)), + ListFromRange(1, 3, 1), + ListFromRange(1, 3, Just(1)), + ListFromRange(1, 3, Nothing(Int32?)), + ListFromRange(1, Just(3), Just(1)), + ListFromRange(1.2, 3.2), + ListFromRange(1.2, Just(3.2)), + ListFromRange(Just(1.2), Just(3.2)), + ListFromRange(1, Just(3.2)), + ListFromRange(1, Nothing(Uint64?)), + ListFromRange(1.2, Just(3.2), 0.4), + ListFromRange(1.2, 3.2, Just(0.4)), + ListFromRange(Just(1.2), 3.2, Just(0.4)), + ListFromRange(1.2, 3.2, Nothing(Double?)), + ListFromRange(1, Just(1.2), Null), + ListFromRange(Just(1), Null, Null), + ListFromRange(1, 1.2, Null), + ListFromRange(1, Null, Null), + ListFromRange(Null, Null, Null), + ListFromRange(Interval("PT1S"), Interval("PT1S")), + ListFromRange(Interval("PT1M"), Just(Interval("PT1M"))), + ListFromRange(Interval("PT1M"), Interval("PT2M"), Just(Interval("PT10S"))), + ListFromRange(Interval("PT1S"), Interval("PT1M"), Null), + ListFromRange(Interval("PT1S"), Interval("PT4S"), Nothing(Interval?)); diff --git a/yql/essentials/tests/sql/suites/expr/list_from_range_overflow.sql b/yql/essentials/tests/sql/suites/expr/list_from_range_overflow.sql new file mode 100644 index 0000000000..cd08bede81 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_from_range_overflow.sql @@ -0,0 +1,10 @@ +/* postgres can not */ + +$min_tiny = -127t - 1t; + +select + ListFromRange(1ut,255ut,11ut), + ListFromRange(1ut,254ut,11ut), + ListFromRange(127t, $min_tiny, -1t), + ListFromRange(127t, $min_tiny, $min_tiny); + diff --git a/yql/essentials/tests/sql/suites/expr/list_indexof.sql b/yql/essentials/tests/sql/suites/expr/list_indexof.sql new file mode 100644 index 0000000000..47be99e547 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_indexof.sql @@ -0,0 +1,13 @@ +$list = [1, 2, 3]; + +$opt_list = [1, null, 2, 3]; + +select + ListIndexOf($list, 2), + ListIndexOf($list, 100), + ListIndexOf(Just($opt_list), 2), + ListIndexOf(Just($opt_list), 200), + ListIndexOf(Nothing(List<Int32>?), 2), + ListIndexOf([], 'foo'), + ListIndexOf(null, 1.0), +; diff --git a/yql/essentials/tests/sql/suites/expr/list_not_null.sql b/yql/essentials/tests/sql/suites/expr/list_not_null.sql new file mode 100644 index 0000000000..69f2a206fc --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_not_null.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +/* syntax version 1 */ +select ListNotNull(null),ListNotNull([]),ListNotNull([null]),ListNotNull(Just([])), +ListNotNull([1,2]),ListNotNull([1,null,2]), +ListNotNull(Just([1,2])),ListNotNull(Just([1,null,2])), +ListNotNull(Nothing(List<Int32>?)),ListNotNull(Nothing(List<Int32?>?));
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/list_replicate.sql b/yql/essentials/tests/sql/suites/expr/list_replicate.sql new file mode 100644 index 0000000000..d1e42fda27 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_replicate.sql @@ -0,0 +1,6 @@ +/* postgres can not */ +select +ListReplicate(-1,10), +ListReplicate(AsTuple(1,2),3), +ListReplicate("foo",2), +ListReplicate(true,0); diff --git a/yql/essentials/tests/sql/suites/expr/list_replicate_fail.cfg b/yql/essentials/tests/sql/suites/expr/list_replicate_fail.cfg new file mode 100644 index 0000000000..5dae597903 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_replicate_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/list_replicate_fail.sql b/yql/essentials/tests/sql/suites/expr/list_replicate_fail.sql new file mode 100644 index 0000000000..0567764565 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_replicate_fail.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +select +ListReplicate(1, -1); diff --git a/yql/essentials/tests/sql/suites/expr/list_takeskipwhile.sql b/yql/essentials/tests/sql/suites/expr/list_takeskipwhile.sql new file mode 100644 index 0000000000..8101dded6d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_takeskipwhile.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +/* syntax version 1 */ +$a = AsList(1,2,3,1,2,3); + +select + ListTakeWhile($a,($x)->{return $x<3}), + ListSkipWhile($a,($x)->{return $x<3}), + Yql::Collect(YQL::TakeWhile(Yql::Iterator($a,Yql::DependsOn(1)),($x)->{return $x<3})), + Yql::Collect(YQL::SkipWhile(Yql::Iterator($a,Yql::DependsOn(2)),($x)->{return $x<3})), + Yql::TakeWhile(Just(1),($x)->{return $x<3}), + Yql::SkipWhile(Just(1),($x)->{return $x<3}); diff --git a/yql/essentials/tests/sql/suites/expr/list_takeskipwhileinclusive.sql b/yql/essentials/tests/sql/suites/expr/list_takeskipwhileinclusive.sql new file mode 100644 index 0000000000..38159a462d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_takeskipwhileinclusive.sql @@ -0,0 +1,11 @@ +/* postgres can not */ +/* syntax version 1 */ +$a = AsList(1,2,3,1,2,3); + +select + ListTakeWhileInclusive($a,($x)->{return $x<3}), + ListSkipWhileInclusive($a,($x)->{return $x<3}), + Yql::Collect(YQL::TakeWhileInclusive(Yql::Iterator($a,Yql::DependsOn(1)),($x)->{return $x<3})), + Yql::Collect(YQL::SkipWhileInclusive(Yql::Iterator($a,Yql::DependsOn(2)),($x)->{return $x<3})), + Yql::TakeWhileInclusive(Just(1),($x)->{return $x<3}), + Yql::SkipWhileInclusive(Just(1),($x)->{return $x<3}); diff --git a/yql/essentials/tests/sql/suites/expr/list_to_from_tuple.sql b/yql/essentials/tests/sql/suites/expr/list_to_from_tuple.sql new file mode 100644 index 0000000000..4f80b8976e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_to_from_tuple.sql @@ -0,0 +1,11 @@ +select + ListFromTuple(null), + ListFromTuple(()), + ListFromTuple((1,2)), + ListFromTuple(just((3,4))), + ListFromTuple(Nothing(Tuple<Int32,Int32>?)), + ListToTuple(null,10), + ListToTuple([],0), + ListToTuple(ListCreate(Int32),0), + ListToTuple([1,2],2), + ListToTuple(just([3,4]),2); diff --git a/yql/essentials/tests/sql/suites/expr/list_top_sort.sql b/yql/essentials/tests/sql/suites/expr/list_top_sort.sql new file mode 100644 index 0000000000..962b76ae61 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_top_sort.sql @@ -0,0 +1,32 @@ +/* syntax version 1 */ + +$list = [45, 20, 34, 16]; +$empty = []; + +$digit = ($x) -> { return $x % 10; }; + +SELECT + ListTopSort($list, 2), ListTopSort($list, 4), ListTopSort($list, 5), + ListTopSort($list, 2, $digit), + ListTopSort(NULL, 2), ListTopSort(Just($list), 2), ListTopSort($empty, 0); +SELECT + ListTopSortAsc($list, 2), ListTopSortAsc($list, 4), ListTopSortAsc($list, 5), + ListTopSortAsc($list, 2, $digit), + ListTopSortAsc(NULL, 2), ListTopSortAsc(Just($list), 2), ListTopSortAsc($empty, 0); +SELECT + ListTopSortDesc($list, 2), ListTopSortDesc($list, 4), ListTopSortDesc($list, 5), + ListTopSortDesc($list, 2, $digit), + ListTopSortDesc(NULL, 2), ListTopSortDesc(Just($list), 2), ListTopSortDesc($empty, 0); + +SELECT + ListSort(ListTop($list, 2)), ListSort(ListTop($list, 4)), ListSort(ListTop($list, 5)), + ListSort(ListTop($list, 2, $digit)), + ListSort(ListTop(NULL, 2)), ListSort(ListTop(Just($list), 2)), ListSort(ListTop($empty, 0)); +SELECT + ListSort(ListTopAsc($list, 2)), ListSort(ListTopAsc($list, 4)), ListSort(ListTopAsc($list, 5)), + ListSort(ListTopAsc($list, 2, $digit)), + ListSort(ListTopAsc(NULL, 2)), ListSort(ListTopAsc(Just($list), 2)), ListSort(ListTopAsc($empty, 0)); +SELECT + ListSort(ListTopDesc($list, 2)), ListSort(ListTopDesc($list, 4)), ListSort(ListTopDesc($list, 5)), + ListSort(ListTopDesc($list, 2, $digit)), + ListSort(ListTopDesc(NULL, 2)), ListSort(ListTopDesc(Just($list), 2)), ListSort(ListTopDesc($empty, 0));
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/list_uniq.sql b/yql/essentials/tests/sql/suites/expr/list_uniq.sql new file mode 100644 index 0000000000..b27c5413d0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/list_uniq.sql @@ -0,0 +1,8 @@ +SELECT ListSort(ListUniq([1, 2, 1, 3, 4, 2, 4])), + ListSort(ListUniq([1, 2, 3, null, 1, 7, 4, 3])), + ListUniqStable([]), + ListUniqStable([1, 2, 1, 3, 4, 2, 4]), + ListUniqStable([1, 2, 3, null, 1, 7, 4, 3]), + ListUniqStable(["a", "b", "c", "a", "ab", "ac", "ab"]), + ListUniqStable(Just(["a", "b", "c", "a", "ab", "ac", "ab"])), + ListUniqStable(NULL); diff --git a/yql/essentials/tests/sql/suites/expr/literal_list_element.sql b/yql/essentials/tests/sql/suites/expr/literal_list_element.sql new file mode 100644 index 0000000000..70b22d1bbd --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/literal_list_element.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +$list = YQL::@@(AsList (String 'z) (String 'a))@@; +SELECT $list[0]; diff --git a/yql/essentials/tests/sql/suites/expr/literal_strings.sql b/yql/essentials/tests/sql/suites/expr/literal_strings.sql new file mode 100644 index 0000000000..6696be7d80 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/literal_strings.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +select '@@@@', '\'', "@@@@", "\"", @@ '@ @@, @@ +"@ @@@@ +@@, +'"', "'"; diff --git a/yql/essentials/tests/sql/suites/expr/literal_strings_suffix.sql b/yql/essentials/tests/sql/suites/expr/literal_strings_suffix.sql new file mode 100644 index 0000000000..6d63d0f750 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/literal_strings_suffix.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* syntax version 1 */ +select "ab"u, "[1;2]"y, '{"a":null}'j; diff --git a/yql/essentials/tests/sql/suites/expr/literal_struct_member.sql b/yql/essentials/tests/sql/suites/expr/literal_struct_member.sql new file mode 100644 index 0000000000..a53d4b293f --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/literal_struct_member.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +$struct = YQL::@@(AsStruct '('"z z" (String 'a)) '('y (String 'b)))@@; +SELECT $struct.`z z`; diff --git a/yql/essentials/tests/sql/suites/expr/literal_true.sql b/yql/essentials/tests/sql/suites/expr/literal_true.sql new file mode 100644 index 0000000000..38d56a8fe3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/literal_true.sql @@ -0,0 +1,3 @@ +/* postgres can not */ +/* postgres returns "t" as string representation */ +select true;
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/longint_builtins.sql b/yql/essentials/tests/sql/suites/expr/longint_builtins.sql new file mode 100644 index 0000000000..f87d611fc5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/longint_builtins.sql @@ -0,0 +1,22 @@ +/* postgres can not */ +-- not supported on windows +$value = "1000000000000"; +$negative = -1000000000000; +$longint = YQL::StrictFromString($value, AsAtom("Decimal"), AsAtom("32"), AsAtom("0")); +$negative_longint = CAST($negative AS Decimal(32,0)); +$add = $longint + CAST("1111111111111111111111111111111" AS Decimal(32,0)); +$div = $longint / CAST(1111 AS Decimal(32,0)); +$mod = $longint % CAST(1111 AS Decimal(32,0)); +$mul = $longint * CAST(333333333333333333 AS Decimal(32,0)); +$sub = $longint - CAST("1111111111111111111111111111111" AS Decimal(32,0)); +SELECT + $longint AS binary, + YQL::ToString(YQL::Dec($longint)) AS to_string, + CAST(YQL::Inc(Abs($negative_longint)) AS String) AS abs, + CAST(YQL::Minus($add) AS String) AS add, + CAST($div AS String) AS div, + CAST($mod AS String) AS mod, + CAST($mul AS String) AS mul, + CAST($sub AS String) AS sub, + $longint == YQL::Abs($negative_longint) AS eq, + $div <= $longint AS cmp; diff --git a/yql/essentials/tests/sql/suites/expr/many_opt_comp.sql b/yql/essentials/tests/sql/suites/expr/many_opt_comp.sql new file mode 100644 index 0000000000..8062a6b8a1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/many_opt_comp.sql @@ -0,0 +1,104 @@ +/* postgres can not */ +select AsTuple( + + Just(Just(1)) == Just(Just(2u)), + Just(Just(1)) == Just(2u), + Just(1) == Just(Just(2u)), + 1 == Just(Just(2u)), + + Just(Just(1)) == Just(Just(1u)), + Just(Just(1)) == Just(1u), + Just(1) == Just(Just(1u)), + 1 == Just(Just(1u)), + + Just(Just(2)) == Just(Just(1u)), + Just(Just(2)) == Just(1u), + Just(2) == Just(Just(1u)), + 2 == Just(Just(1u)) +); + +select AsTuple( + Just(Just(1)) != Just(Just(2u)), + Just(Just(1)) != Just(2u), + Just(1) != Just(Just(2u)), + 1 != Just(Just(2u)), + + Just(Just(1)) != Just(Just(1u)), + Just(Just(1)) != Just(1u), + Just(1) != Just(Just(1u)), + 1 != Just(Just(1u)), + + Just(Just(2)) != Just(Just(1u)), + Just(Just(2)) != Just(1u), + Just(2) != Just(Just(1u)), + 2 != Just(Just(1u)) +); + + +select AsTuple( + Just(Just(1)) < Just(Just(2u)), + Just(Just(1)) < Just(2u), + Just(1) < Just(Just(2u)), + 1 < Just(Just(2u)), + + Just(Just(1)) < Just(Just(1u)), + Just(Just(1)) < Just(1u), + Just(1) < Just(Just(1u)), + 1 < Just(Just(1u)), + + Just(Just(2)) < Just(Just(1u)), + Just(Just(2)) < Just(1u), + Just(2) < Just(Just(1u)), + 2 < Just(Just(1u)) +); + +select AsTuple( + Just(Just(1)) <= Just(Just(2u)), + Just(Just(1)) <= Just(2u), + Just(1) <= Just(Just(2u)), + 1 <= Just(Just(2u)), + + Just(Just(1)) <= Just(Just(1u)), + Just(Just(1)) <= Just(1u), + Just(1) <= Just(Just(1u)), + 1 <= Just(Just(1u)), + + Just(Just(2)) <= Just(Just(1u)), + Just(Just(2)) <= Just(1u), + Just(2) <= Just(Just(1u)), + 2 <= Just(Just(1u)) +); + +select AsTuple( + Just(Just(1)) > Just(Just(2u)), + Just(Just(1)) > Just(2u), + Just(1) > Just(Just(2u)), + 1 > Just(Just(2u)), + + Just(Just(1)) > Just(Just(1u)), + Just(Just(1)) > Just(1u), + Just(1) > Just(Just(1u)), + 1 > Just(Just(1u)), + + Just(Just(2)) > Just(Just(1u)), + Just(Just(2)) > Just(1u), + Just(2) > Just(Just(1u)), + 2 > Just(Just(1u)) +); + +select AsTuple( + Just(Just(1)) >= Just(Just(2u)), + Just(Just(1)) >= Just(2u), + Just(1) >= Just(Just(2u)), + 1 >= Just(Just(2u)), + + Just(Just(1)) >= Just(Just(1u)), + Just(Just(1)) >= Just(1u), + Just(1) >= Just(Just(1u)), + 1 >= Just(Just(1u)), + + Just(Just(2)) >= Just(Just(1u)), + Just(Just(2)) >= Just(1u), + Just(2) >= Just(Just(1u)), + 2 >= Just(Just(1u)) +); diff --git a/yql/essentials/tests/sql/suites/expr/minmax_for_complex_types.sql b/yql/essentials/tests/sql/suites/expr/minmax_for_complex_types.sql new file mode 100644 index 0000000000..3b08ce957b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/minmax_for_complex_types.sql @@ -0,0 +1,19 @@ +select + max_of(AsTuple(1u, 2), AsTuple(1, 1/0), AsTuple(1, 3)) as max_tuple, + min_of(AsTuple(1u, 2), AsTuple(1, 1/0), AsTuple(1, 3)) as min_tuple, + + min_of(AsTuple(0, 1/0), AsTuple(1, 1/0), AsTuple(2, 1/0)) as min_tuple1, + max_of(AsTuple(0, 1/0), AsTuple(1, 1/0), AsTuple(2, 1/0)) as max_tuple1, + + min_of(AsTuple(1, 1/0), AsTuple(1, 1)) as min_tuple2, + max_of(AsTuple(1, 1/0), AsTuple(1, 1)) as max_tuple2, + + min_of(AsTuple(1, 1/0), AsTuple(1, 1/0)) as min_tuple3, + max_of(AsTuple(1, 1/0), AsTuple(1, 1/0)) as max_tuple3, + + min_of(AsTuple(1, 1/0)) as min_tuple4, + max_of(AsTuple(1, 1/0)) as max_tuple4, + + + min_of(AsList(1, 2, 3), AsList(1, 1)) as min_list, + max_of(AsList(1, 2, 3), AsList(1, 1)) as max_list, diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_column_fail.cfg b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_column_fail.cfg new file mode 100644 index 0000000000..4f32e1efb8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_column_fail.cfg @@ -0,0 +1,2 @@ +xfail +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_column_fail.sql b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_column_fail.sql new file mode 100644 index 0000000000..eae813be7a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_column_fail.sql @@ -0,0 +1,6 @@ +PRAGMA Warning("error", '1104'); + +USE plato; + +SELECT COUNT(*) FROM Input +GROUP BY YQL::NewMTRand(length(value)) as key; diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_having_some_fail.cfg b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_having_some_fail.cfg new file mode 100644 index 0000000000..4f32e1efb8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_having_some_fail.cfg @@ -0,0 +1,2 @@ +xfail +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_having_some_fail.sql b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_having_some_fail.sql new file mode 100644 index 0000000000..3fc1126b89 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_having_some_fail.sql @@ -0,0 +1,9 @@ +PRAGMA Warning("error", '1104'); + +USE plato; + +SELECT + key +FROM Input +GROUP BY key +HAVING Yql::NextMtRand(SOME(YQL::NewMTRand(1))).0 > 100; diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_some_fail.cfg b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_some_fail.cfg new file mode 100644 index 0000000000..4f32e1efb8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_some_fail.cfg @@ -0,0 +1,2 @@ +xfail +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_some_fail.sql b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_some_fail.sql new file mode 100644 index 0000000000..e7b61c9b47 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_group_by_some_fail.sql @@ -0,0 +1,6 @@ +PRAGMA Warning("error", '1104'); + +USE plato; + +SELECT SOME(YQL::NewMTRand(1)) FROM Input +GROUP BY key; diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_inner_select_fail.cfg b/yql/essentials/tests/sql/suites/expr/non_persistable_inner_select_fail.cfg new file mode 100644 index 0000000000..4f32e1efb8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_inner_select_fail.cfg @@ -0,0 +1,2 @@ +xfail +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_inner_select_fail.sql b/yql/essentials/tests/sql/suites/expr/non_persistable_inner_select_fail.sql new file mode 100644 index 0000000000..4f6a600c31 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_inner_select_fail.sql @@ -0,0 +1,9 @@ +PRAGMA Warning("error", '1104'); + +USE plato; + +SELECT 1 FROM ( + SELECT + YQL::NewMTRand(1) as x + FROM Input +) diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_insert_into_fail.cfg b/yql/essentials/tests/sql/suites/expr/non_persistable_insert_into_fail.cfg new file mode 100644 index 0000000000..5625fb3b45 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_insert_into_fail.cfg @@ -0,0 +1,2 @@ +xfail +out Output output.txt diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_insert_into_fail.sql b/yql/essentials/tests/sql/suites/expr/non_persistable_insert_into_fail.sql new file mode 100644 index 0000000000..cd75cf3d25 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_insert_into_fail.sql @@ -0,0 +1,8 @@ +PRAGMA Warning("error", '1104'); + +USE plato; + +INSERT INTO + Output (key, value) +VALUES + ("foo", YQL::NewMTRand(1)); diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_order_by_fail.cfg b/yql/essentials/tests/sql/suites/expr/non_persistable_order_by_fail.cfg new file mode 100644 index 0000000000..4f32e1efb8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_order_by_fail.cfg @@ -0,0 +1,2 @@ +xfail +in Input input.txt diff --git a/yql/essentials/tests/sql/suites/expr/non_persistable_order_by_fail.sql b/yql/essentials/tests/sql/suites/expr/non_persistable_order_by_fail.sql new file mode 100644 index 0000000000..c94d1805ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/non_persistable_order_by_fail.sql @@ -0,0 +1,6 @@ +PRAGMA Warning("error", '1104'); + +USE plato; + +SELECT key, value FROM Input +ORDER BY YQL::NewMTRand(length(value)); diff --git a/yql/essentials/tests/sql/suites/expr/opt_list_map.sql b/yql/essentials/tests/sql/suites/expr/opt_list_map.sql new file mode 100644 index 0000000000..35f0e9d96b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/opt_list_map.sql @@ -0,0 +1,26 @@ +/* postgres can not */ +/* syntax version 1 */ +$l1 = AsList(1,2,3,1,2,3); +$l2 = Just($l1); +$l3 = Nothing(TypeOf($l2)); +select $l1, $l2, $l3; + +$p = ($x) -> { return $x < 3 }; +select ListFilter($l1, $p), ListFilter($l2, $p), ListFilter($l3, $p); + +$m = ($x) -> { return $x * 2 }; +select ListMap($l1, $m), ListMap($l2, $m), ListMap($l3, $m); + +$f = ($x) -> { return AsList($x, $x * 2) }; +select ListFlatMap($l1, $f), ListFlatMap($l2, $f), ListFlatMap($l3, $f); + +select ListSkipWhile($l1, $p), ListSkipWhile($l2, $p), ListSkipWhile($l3, $p); +select ListTakeWhile($l1, $p), ListTakeWhile($l2, $p), ListTakeWhile($l3, $p); + +select ListExtend($l1, $l1), ListExtend($l2, $l2), ListExtend($l2, $l3), ListExtend($l3, $l3); + +$ls1 = AsList(AsStruct(1 as a), AsStruct(2 as a)); +$ls2 = Just($ls1); +$ls3 = Nothing(TypeOf($ls2)); + +select ListUnionAll($ls1, $ls1), ListUnionAll($ls2, $ls2), ListUnionAll($ls2, $ls3), ListUnionAll($ls3, $ls3); diff --git a/yql/essentials/tests/sql/suites/expr/opt_try_member.sql b/yql/essentials/tests/sql/suites/expr/opt_try_member.sql new file mode 100644 index 0000000000..1be2db2401 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/opt_try_member.sql @@ -0,0 +1,23 @@ +/* postgres can not */ +/* kikimr can not */ +/* syntax version 1 */ +select +TryMember(Just(<|x:1|>),"x",0), +TryMember(Just(<|x:1|>),"y",0), +TryMember(Just(<|x:Just(1)|>),"x",Just(0)), +TryMember(Just(<|x:Just(1)|>),"y",Just(0)), +TryMember(Just(<|x:Nothing(Int32?)|>),"x",Just(0)), +TryMember(Just(<|x:Nothing(Int32?)|>),"y",Just(0)), + +TryMember(Just(<|x:1|>),"x",null), +TryMember(Just(<|x:1|>),"y",null), +TryMember(Just(<|x:Just(1)|>),"x",null), +TryMember(Just(<|x:Just(1)|>),"y",null), +TryMember(Just(<|x:Nothing(Int32?)|>),"x",null), +TryMember(Just(<|x:Nothing(Int32?)|>),"y",null), + +TryMember(Nothing(Struct<x:Int32>?),"x",0), +TryMember(Nothing(Struct<x:Int32>?),"x",null), + +TryMember(Nothing(Struct<x:Int32?>?),"x",Just(0)), +TryMember(Nothing(Struct<x:Int32?>?),"x",null); diff --git a/yql/essentials/tests/sql/suites/expr/partial_columns_in_mem_aggr.sql b/yql/essentials/tests/sql/suites/expr/partial_columns_in_mem_aggr.sql new file mode 100644 index 0000000000..7a72357070 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/partial_columns_in_mem_aggr.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +select + Domain, + aggregate_list(AsStruct(DeviceID as DeviceID, DeviceCategory as DeviceCategory)) as Devices +from ( + select 1 as DeviceID, 2 as DeviceCategory, 3 as Domain +) +group by Domain diff --git a/yql/essentials/tests/sql/suites/expr/pg_try_member.cfg b/yql/essentials/tests/sql/suites/expr/pg_try_member.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/pg_try_member.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/expr/pg_try_member.sql b/yql/essentials/tests/sql/suites/expr/pg_try_member.sql new file mode 100644 index 0000000000..66eb44a8f5 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/pg_try_member.sql @@ -0,0 +1,29 @@ +/* postgres can not */ +/* syntax version 1 */ +/* yt can not */ + +$s = <|a:1p|>; +$js = Just($s); +$es = Nothing(Struct<a:PgInt>?); + +-- fully equivalent to <struct>.<name> +select + TryMember($s, "a", null), + TryMember($js, "a", null), + TryMember($es, "a", null), +; + +-- TypeOf TryMember is type of third argument +-- field type should either match third type exactly, or (if the third type is optional) +-- Optional(field) should be equal to third type +select + TryMember($s, "a", 999p), + TryMember($s, "a", Just(999p)), + + TryMember($js, "a", 999p), + TryMember($js, "a", Just(999p)), + + TryMember($es, "a", 999p), + TryMember($es, "a", Just(999p)), +; + diff --git a/yql/essentials/tests/sql/suites/expr/pickle.sql b/yql/essentials/tests/sql/suites/expr/pickle.sql new file mode 100644 index 0000000000..2279d409c0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/pickle.sql @@ -0,0 +1,13 @@ +/* postgres can not */ +/* syntax version 1 */ +$lst = AsList(AsTuple(13,4),AsTuple(11,2),AsTuple(17,8),AsTuple(5,6)); + +$p1 = Pickle(ToDict($lst)); +$p2 = StablePickle(ToDict($lst)); +$p3 = StablePickle(ToSortedDict($lst)); +$p4 = Pickle(ToDict(ListReverse($lst))); +$p5 = StablePickle(ToDict(ListReverse($lst))); +select $p1, Ensure($p2, $p1 != $p2), Ensure($p3, $p2 = $p3), + Ensure($p4, $p4 != $p1), Ensure($p5, $p5 = $p2); +select ListSort(DictItems(Unpickle(TypeOf(ToDict($lst)),$p1))), ListSort(DictItems(Unpickle(TypeOf(ToDict($lst)),$p2))); + diff --git a/yql/essentials/tests/sql/suites/expr/replace_member.sql b/yql/essentials/tests/sql/suites/expr/replace_member.sql new file mode 100644 index 0000000000..1e2e47ed7a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/replace_member.sql @@ -0,0 +1,27 @@ +/* postgres can not */ +/* syntax version 1 */ +$data = <|x: [<|y: 2|>], z: 5|>; + +-- set field function +$F = ($field, $function) -> ( + ($struct) -> ( + ReplaceMember($struct, $field, $function($struct.$field)) + ) +); + +-- set list element function +$E = ($index, $function) -> ( + ($list) -> ( + ListMap(ListEnumerate($list), ($pair) -> ( + IF ($pair.0 = $index, $function($pair.1), $pair.1) + )) + ) +); + +-- set value function +$V = ($value) -> ( + ($_item) -> ($value) +); + +SELECT $F("x", $E(0, $F("y", $V(3))))($data) + diff --git a/yql/essentials/tests/sql/suites/expr/sets.sql b/yql/essentials/tests/sql/suites/expr/sets.sql new file mode 100644 index 0000000000..4984941e5d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/sets.sql @@ -0,0 +1,40 @@ +/* postgres can not */ +$ns = ($set)->{return ListSort(DictKeys($set))}; +$nd = ($dict)->{return ListSort(DictItems($dict), ($z)->{return $z.0})}; + +select $ns(ToSet(AsList(1,2,3))); + +select SetIsDisjoint(ToSet(AsList(1,2,3)), AsList(7,4)); + +select SetIsDisjoint(ToSet(AsList(1,2,3)), AsList(3,4)); + +select SetIsDisjoint(ToSet(AsList(1,2,3)), ToSet(AsList(7,4))); +select SetIsDisjoint(ToSet(AsList(1,2,3)), ToSet(AsList(3,4))); + +select $ns(SetIntersection(ToSet(AsList(1,2,3)), ToSet(AsList(3,4)))); +select $nd(SetIntersection( + AsDict(AsTuple(1,"foo"),AsTuple(3,"bar")), + AsDict(AsTuple(1,"baz"),AsTuple(2,"qwe")), + ($_k, $a, $b)->{ return AsTuple($a, $b) })); + +select SetIncludes(ToSet(AsList(1,2,3)), AsList(3,4)); + +select SetIncludes(ToSet(AsList(1,2,3)), AsList(2,3)); + +select SetIncludes(ToSet(AsList(1,2,3)), ToSet(AsList(3,4))); +select SetIncludes(ToSet(AsList(1,2,3)), ToSet(AsList(2,3))); + +select $ns(SetDifference(ToSet(AsList(1,2,3)), ToSet(AsList(3,4)))); +select $ns(SetDifference(ToSet(AsList(1,2,3)), ToSet(AsList(2,3)))); + +select $ns(SetUnion(ToSet(AsList(1,2,3)), ToSet(AsList(3,4)))); +select $nd(SetUnion( + AsDict(AsTuple(1,"foo"),AsTuple(3,"bar")), + AsDict(AsTuple(1,"baz"),AsTuple(2,"qwe")), + ($_k, $a, $b)->{ return AsTuple($a, $b) })); + +select $ns(SetSymmetricDifference(ToSet(AsList(1,2,3)), ToSet(AsList(3,4)))); +select $nd(SetSymmetricDifference( + AsDict(AsTuple(1,"foo"),AsTuple(3,"bar")), + AsDict(AsTuple(1,"baz"),AsTuple(2,"qwe")), + ($_k, $a, $b)->{ return AsTuple($a, $b) })); diff --git a/yql/essentials/tests/sql/suites/expr/static_fold.sql b/yql/essentials/tests/sql/suites/expr/static_fold.sql new file mode 100644 index 0000000000..9118da0e06 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/static_fold.sql @@ -0,0 +1,21 @@ +/* syntax version 1 */ + +$st_many = <|a: "one", b: "two", c: "three", d: "four"|>; +$st_single = <|a: "zero"|>; +$st_empty = <||>; +$tup = AsTuple("first", "second"); + +$concat = ($item, $state) -> { return $state || $item; }; +$start = ($value) -> { return "(" || $value || ")"; }; + +SELECT + StaticFold($st_many, "->", $concat), + StaticFold($st_single, "->", $concat), + CAST(StaticFold($st_empty, "->", $concat) AS Optional<String>), + StaticFold($tup, "->", $concat); + +SELECT + StaticFold1($st_many, $start, $concat), + StaticFold1($st_single, $start, $concat), + CAST(StaticFold1($st_empty, $start, $concat) AS Optional<String>), + StaticFold1($tup, $start, $concat);
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/static_zip.sql b/yql/essentials/tests/sql/suites/expr/static_zip.sql new file mode 100644 index 0000000000..8cbb9856e6 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/static_zip.sql @@ -0,0 +1,20 @@ +/* postgres can not */ +/* syntax version 1 */ + +$s1 = AsStruct(1 as k1, "2" as k2, ["3", "4"] as k3); +$s2 = AsStruct("10" as k1, [20, 30] as k2, 40 as k3); +$s3 = AsStruct([100, 200] as k1, 300 as k2, "400" as k3); + + +$t1 = AsTuple(1, "2", ["3", "4"]); +$t2 = AsTuple("10", [20, 30], 40); +$t3 = AsTuple([100, 200], 300, "400"); + +SELECT + StaticZip($s1, $s2, $s3) as structs, + StaticZip($t1, $t2, $t3) as tuples, + StaticZip(AsStruct(), AsStruct()) as empty_structs, + StaticZip(AsTuple(), AsTuple()) as empty_tuples, + StaticZip($s1) as single_struct, + StaticZip($t1) as single_tuple, +; diff --git a/yql/essentials/tests/sql/suites/expr/struct_builtins.sql b/yql/essentials/tests/sql/suites/expr/struct_builtins.sql new file mode 100644 index 0000000000..eac0098fa7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/struct_builtins.sql @@ -0,0 +1,16 @@ +/* postgres can not */ +/* kikimr can not */ +/* syntax version 1 */ +$ad = AsStruct(1 AS a, 4 AS d); +$b = AsStruct(2 AS b); +$c_name = 'c'; +SELECT + TryMember($ad, $c_name, "foo") AS c, + TryMember($ad, "d", NULL) AS d, + AddMember($ad, $c_name, 3) AS acd, + ReplaceMember($ad, "a", 5) AS a5, + RemoveMember($ad, 'd') AS a, + ForceRemoveMember($ad, $c_name) AS ad, + ExpandStruct($b, 1 AS a) AS ab, + CombineMembers($ad, $b) AS abd, + FlattenMembers(AsTuple("fo" || "o", $ad), AsTuple("bar", $b)) AS foobar; diff --git a/yql/essentials/tests/sql/suites/expr/struct_gather_spread.sql b/yql/essentials/tests/sql/suites/expr/struct_gather_spread.sql new file mode 100644 index 0000000000..0054d0bc25 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/struct_gather_spread.sql @@ -0,0 +1,29 @@ +/* postgres can not */ +/* syntax version 1 */ +select + StructMembers(<|a:1|>), + StructMembers(Just(<|a:1|>)), + StructMembers(null), + + GatherMembers(<||>), + GatherMembers(<|a:1,b:2|>), + GatherMembers(Just(<|a:1,b:2|>)), + GatherMembers(null), + + RenameMembers(<|a:1,c:2|>,[("a","b")]), + RenameMembers(<|a:1,c:2|>,[("a","b"),("a","d")]), + ForceRenameMembers(<|a:1,c:2|>,[("a","b")]), + ForceRenameMembers(<|a:1,c:2|>,[("d","d")]), + RenameMembers(Just(<|a:1,c:2|>),[("a","b")]), + RenameMembers(null,[("a","b")]), + + SpreadMembers([("a",1)],["a","b"]), + SpreadMembers([("a",1),("b",2)],["a","b"]), + SpreadMembers([("a",Just(1))],["a","b"]), + SpreadMembers([("a",1),("a",2)],["a","b"]), + SpreadMembers([],["a","b"]), + + SpreadMembers(Just([("a",1)]),["a","b"]), + SpreadMembers(null,["a","b"]), + ForceSpreadMembers([("a",1)],["a","b"]), + ForceSpreadMembers([("c",1)],["a","b"]); diff --git a/yql/essentials/tests/sql/suites/expr/struct_literal.cfg b/yql/essentials/tests/sql/suites/expr/struct_literal.cfg new file mode 100644 index 0000000000..f75b50116d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/struct_literal.cfg @@ -0,0 +1,2 @@ +udf string_udf + diff --git a/yql/essentials/tests/sql/suites/expr/struct_literal.sql b/yql/essentials/tests/sql/suites/expr/struct_literal.sql new file mode 100644 index 0000000000..ba0c96cf81 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/struct_literal.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +/* postgres can not */ +select <||>, <|,|>, <| + CAST(Unicode::ToUpper("a"u) AS String):Unicode::ToUpper("b"u), + c:false, + "e":7, + `g`:2.0, + |> diff --git a/yql/essentials/tests/sql/suites/expr/struct_literal_bind.sql b/yql/essentials/tests/sql/suites/expr/struct_literal_bind.sql new file mode 100644 index 0000000000..e9111dce01 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/struct_literal_bind.sql @@ -0,0 +1,5 @@ +/* syntax version 1 */ +/* postgres can not */ +$s = <| x:1, y:"2" |>; + +select $s; diff --git a/yql/essentials/tests/sql/suites/expr/struct_literal_members.sql b/yql/essentials/tests/sql/suites/expr/struct_literal_members.sql new file mode 100644 index 0000000000..174ccacc40 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/struct_literal_members.sql @@ -0,0 +1,5 @@ +/* postgres can not */ +$list = YQL::@@ +(AddMember (AddMember (Struct) 'query (String 'QUERY)) 'result (String 'RESULT)) +@@; +select $list.query as query, $list.result as result; diff --git a/yql/essentials/tests/sql/suites/expr/struct_merge.sql b/yql/essentials/tests/sql/suites/expr/struct_merge.sql new file mode 100644 index 0000000000..3b6fad5e81 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/struct_merge.sql @@ -0,0 +1,14 @@ +/* syntax version 1 */ + +$merge = ($_name, $l, $r) -> { return Coalesce($l, 0) + Coalesce($r, 0); }; +$left = <|a: 1, b: 2, c: 3|>; +$right = <|c: 1, d: 2, e: 3|>; + +SELECT + StructUnion($left, $right), + StructUnion($left, $right, $merge), + StructIntersection($left, $right), + StructIntersection($left, $right, $merge), + StructDifference($left, $right), + StructSymmetricDifference($left, $right) +; diff --git a/yql/essentials/tests/sql/suites/expr/struct_slice.sql b/yql/essentials/tests/sql/suites/expr/struct_slice.sql new file mode 100644 index 0000000000..dd7d418850 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/struct_slice.sql @@ -0,0 +1,9 @@ +/* postgres can not */ +/* syntax version 1 */ +$s = <| a:1, b:2, c:3, d:4 |>; +$list = ["a", "b"]; +select * from (select CombineMembers( + ChooseMembers($s, $list), + <| remain : RemoveMembers($s, $list) |> + )) flatten columns; +
\ No newline at end of file diff --git a/yql/essentials/tests/sql/suites/expr/tagged_runtime.sql b/yql/essentials/tests/sql/suites/expr/tagged_runtime.sql new file mode 100644 index 0000000000..8d185b767e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/tagged_runtime.sql @@ -0,0 +1,12 @@ +/* postgres can not */ +use plato; +insert into @tmp +select Just(( + AsTagged(1,"A"), + AsTagged(just(2),"B"), + AsTagged(null,"C"), + AsTagged(Nothing(Int32?),"D"), + AsTagged(Nothing(pgint4?),"E") + )) as x; +commit; +select x.0, x.1, x.2, x.3, x.4 from @tmp diff --git a/yql/essentials/tests/sql/suites/expr/to_dict_from_nothing.sql b/yql/essentials/tests/sql/suites/expr/to_dict_from_nothing.sql new file mode 100644 index 0000000000..20b61dd510 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_dict_from_nothing.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +SELECT ToSet(Nothing(List<Uint8>?)); diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_dict_dict_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_dict_key.sql new file mode 100644 index 0000000000..0c74dc8dd1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_dict_key.sql @@ -0,0 +1,40 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$i = AsDict(AsTuple(1,"A"),AsTuple(2,"B")); +$j = AsDict(AsTuple(1,"A"),AsTuple(2,"C")); +$k = AsDict(AsTuple(1,"A"),AsTuple(2,"D")); + +$l = AsList( + AsTuple($i,"foo"), + AsTuple($i,"bar"), + AsTuple($j,"baz") +); + +$d = ToDict($l); +select ListSort(ListFlatten(ListMap(DictItems($d), ($x) -> {return ListMap(DictItems($x.0), ($y) -> {return ($y, $x.1)})}))), + ListSort(ListFlatten(ListMap(DictKeys($d), ($x) -> {return DictItems($x)}))), + ListSort(DictPayloads($d)); +select DictLookup($d,$i), DictLookup($d,$k); +select DictContains($d,$i), DictContains($d,$k); + +$d = ToMultiDict($l); +select ListSort(ListFlatten(ListMap(DictItems($d), ($x) -> {return ListMap(DictItems($x.0), ($y) -> {return ($y, $x.1)})}))), + ListSort(ListFlatten(ListMap(DictKeys($d), ($x) -> {return DictItems($x)}))), + ListSort(DictPayloads($d)); +select DictLookup($d,$i), DictLookup($d,$k); +select DictContains($d,$i), DictContains($d,$k); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select ListSort(ListFlatten(ListMap(DictItems($d), ($x) -> {return ListMap(DictItems($x.0), ($y) -> {return ($y, $x.1)})}))), + ListSort(ListFlatten(ListMap(DictKeys($d), ($x) -> {return DictItems($x)}))), + ListSort(DictPayloads($d)); +select DictLookup($d,$i), DictLookup($d,$k); +select DictContains($d,$i), DictContains($d,$k); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("Many"))); +select ListSort(ListFlatten(ListMap(DictItems($d), ($x) -> {return ListMap(DictItems($x.0), ($y) -> {return ($y, $x.1)})}))), + ListSort(ListFlatten(ListMap(DictKeys($d), ($x) -> {return DictItems($x)}))), + ListSort(DictPayloads($d)); +select DictLookup($d,$i), DictLookup($d,$k); diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_dict_list_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_list_key.sql new file mode 100644 index 0000000000..024849b8cc --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_list_key.sql @@ -0,0 +1,29 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$l = AsList( + AsTuple(AsList(1,2,3),"foo"), + AsTuple(AsList(1,2),"bar"), + AsTuple(AsList(1,2),"baz") +); + +$d = ToDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); + +$d = ToMultiDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("Many"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_dict_tuple_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_tuple_key.sql new file mode 100644 index 0000000000..77751c1c97 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_tuple_key.sql @@ -0,0 +1,81 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$l = AsList( + AsTuple(AsTuple(),"foo"), + AsTuple(AsTuple(),"bar"), + AsTuple(AsTuple(),"baz") +); + +$d = ToDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$d = ToMultiDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("Many"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$l = AsList( + AsTuple(AsTuple(1),"foo"), + AsTuple(AsTuple(2),"bar"), + AsTuple(AsTuple(2),"baz") +); + +$d = ToDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$d = ToMultiDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("Many"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$l = AsList( + AsTuple(AsTuple(1,2),"foo"), + AsTuple(AsTuple(1,3),"bar"), + AsTuple(AsTuple(1,3),"baz") +); + +$d = ToDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); + +$d = ToMultiDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("Many"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_dict_varstruct_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_varstruct_key.sql new file mode 100644 index 0000000000..fe80aed5a9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_varstruct_key.sql @@ -0,0 +1,34 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$vt = ParseType("Variant<a:Int32,b:Uint32>"); +$v1 = Variant(1,"a",$vt); +$v2 = Variant(2u,"b",$vt); +$v3 = Variant(2,"a",$vt); + +$l = AsList( + AsTuple($v1,"foo"), + AsTuple($v2,"bar"), + AsTuple($v2,"baz") +); + +$d = ToDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = ToMultiDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("Many"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_dict_vartuple_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_vartuple_key.sql new file mode 100644 index 0000000000..1d2c656ebf --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_dict_vartuple_key.sql @@ -0,0 +1,34 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$vt = ParseType("Variant<Int32,Uint32>"); +$v1 = Variant(1,"0",$vt); +$v2 = Variant(2u,"1",$vt); +$v3 = Variant(2,"0",$vt); + +$l = AsList( + AsTuple($v1,"foo"), + AsTuple($v2,"bar"), + AsTuple($v2,"baz") +); + +$d = ToDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = ToMultiDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("Many"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_set_dict_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_set_dict_key.sql new file mode 100644 index 0000000000..7bd22f103d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_set_dict_key.sql @@ -0,0 +1,28 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$i = AsDict(AsTuple(1,"A"),AsTuple(2,"B")); +$j = AsDict(AsTuple(1,"A"),AsTuple(2,"C")); +$k = AsDict(AsTuple(1,"A"),AsTuple(2,"D")); + +$l = AsList( + AsTuple($i,Void()), + AsTuple($i,Void()), + AsTuple($j,Void()) +); + +$d = ToDict($l); +select ListSort(ListFlatten(ListMap(DictItems($d), ($x) -> {return DictItems($x.0)}))), + ListSort(ListFlatten(ListMap(DictKeys($d), ($x) -> {return DictItems($x)}))), + DictPayloads($d); +select DictLookup($d,$i), DictLookup($d,$k); +select DictContains($d,$i), DictContains($d,$k); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select ListSort(ListFlatten(ListMap(DictItems($d), ($x) -> {return DictItems($x.0)}))), + ListSort(ListFlatten(ListMap(DictKeys($d), ($x) -> {return DictItems($x)}))), + DictPayloads($d); +select DictLookup($d,$i), DictLookup($d,$k); +select DictContains($d,$i), DictContains($d,$k); + diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_set_list_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_set_list_key.sql new file mode 100644 index 0000000000..d511f0a88e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_set_list_key.sql @@ -0,0 +1,19 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$l = AsList( + AsTuple(AsList(1,2,3),Void()), + AsTuple(AsList(1,2),Void()), + AsTuple(AsList(1,2),Void()) +); + +$d = ToDict($l); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select DictKeys($d),DictPayloads($d),DictItems($d); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_set_tuple_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_set_tuple_key.sql new file mode 100644 index 0000000000..4d722ca487 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_set_tuple_key.sql @@ -0,0 +1,52 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$l = AsList( + AsTuple(AsTuple(),Void()), + AsTuple(AsTuple(),Void()), + AsTuple(AsTuple(),Void()) +); + +$d = ToDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$l = AsList( + AsTuple(AsTuple(1),Void()), + AsTuple(AsTuple(2),Void()), + AsTuple(AsTuple(2),Void()) +); + +$d = ToDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$l = AsList( + AsTuple(AsTuple(1,2),Void()), + AsTuple(AsTuple(1,3),Void()), + AsTuple(AsTuple(1,3),Void()) +); + +$d = ToDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); + diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_set_varstruct_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_set_varstruct_key.sql new file mode 100644 index 0000000000..9588c5719f --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_set_varstruct_key.sql @@ -0,0 +1,24 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$vt = ParseType("Variant<a:Int32,b:Uint32>"); +$v1 = Variant(1,"a",$vt); +$v2 = Variant(2u,"b",$vt); +$v3 = Variant(2,"a",$vt); + +$l = AsList( + AsTuple($v1,Void()), + AsTuple($v2,Void()), + AsTuple($v2,Void()) +); + +$d = ToDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); diff --git a/yql/essentials/tests/sql/suites/expr/to_hashed_set_vartuple_key.sql b/yql/essentials/tests/sql/suites/expr/to_hashed_set_vartuple_key.sql new file mode 100644 index 0000000000..e769b4f849 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_hashed_set_vartuple_key.sql @@ -0,0 +1,24 @@ +/* postgres can not */ +$first = ($x)->{return $x.0}; +$second = ($x)->{return $x.1}; + +$vt = ParseType("Variant<Int32,Uint32>"); +$v1 = Variant(1,"0",$vt); +$v2 = Variant(2u,"1",$vt); +$v3 = Variant(2,"0",$vt); + +$l = AsList( + AsTuple($v1,Void()), + AsTuple($v2,Void()), + AsTuple($v2,Void()) +); + +$d = ToDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = Yql::ToDict($l,$first,$second,AsTuple(AsAtom("Compact"),AsAtom("Hashed"),AsAtom("One"))); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); diff --git a/yql/essentials/tests/sql/suites/expr/to_sorted_dict_list_key.sql b/yql/essentials/tests/sql/suites/expr/to_sorted_dict_list_key.sql new file mode 100644 index 0000000000..d1a477381f --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_sorted_dict_list_key.sql @@ -0,0 +1,16 @@ +/* postgres can not */ +$l = AsList( + AsTuple(AsList(1,2,3),"foo"), + AsTuple(AsList(1,2),"bar"), + AsTuple(AsList(1,2),"baz") +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),DictKeys($d),DictPayloads($d); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); + +$d = ToSortedMultiDict($l); +select ListSort(DictItems($d)),DictKeys($d),DictPayloads($d); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); diff --git a/yql/essentials/tests/sql/suites/expr/to_sorted_dict_tuple_key.sql b/yql/essentials/tests/sql/suites/expr/to_sorted_dict_tuple_key.sql new file mode 100644 index 0000000000..c7ab81172e --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_sorted_dict_tuple_key.sql @@ -0,0 +1,50 @@ +/* postgres can not */ + +$l = AsList( + AsTuple(AsTuple(),"foo"), + AsTuple(AsTuple(),"bar"), + AsTuple(AsTuple(),"baz") +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$d = ToSortedMultiDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$l = AsList( + AsTuple(AsTuple(1),"foo"), + AsTuple(AsTuple(2),"bar"), + AsTuple(AsTuple(2),"baz") +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$d = ToSortedMultiDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$l = AsList( + AsTuple(AsTuple(1,2),"foo"), + AsTuple(AsTuple(1,3),"bar"), + AsTuple(AsTuple(1,3),"baz") +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); + +$d = ToSortedMultiDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); + diff --git a/yql/essentials/tests/sql/suites/expr/to_sorted_dict_vartuple_key.sql b/yql/essentials/tests/sql/suites/expr/to_sorted_dict_vartuple_key.sql new file mode 100644 index 0000000000..3581c0963d --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_sorted_dict_vartuple_key.sql @@ -0,0 +1,20 @@ +/* postgres can not */ +$vt = ParseType("Variant<Int32,Uint32>"); +$v1 = Variant(1,"0",$vt); +$v2 = Variant(2u,"1",$vt); +$v3 = Variant(2,"0",$vt); + +$l = AsList( + AsTuple($v1,"foo"), + AsTuple($v2,"bar"), +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); + +$d = ToSortedMultiDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); diff --git a/yql/essentials/tests/sql/suites/expr/to_sorted_set_list_key.sql b/yql/essentials/tests/sql/suites/expr/to_sorted_set_list_key.sql new file mode 100644 index 0000000000..323d74f550 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_sorted_set_list_key.sql @@ -0,0 +1,12 @@ +/* postgres can not */ + +$l = AsList( + AsTuple(AsList(1,2,3),Void()), + AsTuple(AsList(1,2),Void()), + AsTuple(AsList(1,2),Void()) +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsList(1,2)), DictLookup($d,AsList(1,3)); +select DictContains($d,AsList(1,2)), DictContains($d,AsList(1,3)); diff --git a/yql/essentials/tests/sql/suites/expr/to_sorted_set_tuple_key.sql b/yql/essentials/tests/sql/suites/expr/to_sorted_set_tuple_key.sql new file mode 100644 index 0000000000..792ac74533 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_sorted_set_tuple_key.sql @@ -0,0 +1,35 @@ +/* postgres can not */ + +$l = AsList( + AsTuple(AsTuple(),Void()), + AsTuple(AsTuple(),Void()), + AsTuple(AsTuple(),Void()) +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple()); +select DictContains($d,AsTuple()); + +$l = AsList( + AsTuple(AsTuple(1),Void()), + AsTuple(AsTuple(2),Void()), + AsTuple(AsTuple(2),Void()) +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(2)), DictLookup($d,AsTuple(3)); +select DictContains($d,AsTuple(2)), DictContains($d,AsTuple(3)); + +$l = AsList( + AsTuple(AsTuple(1,2),Void()), + AsTuple(AsTuple(1,3),Void()), + AsTuple(AsTuple(1,3),Void()) +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,AsTuple(1,2)), DictLookup($d,AsTuple(1,4)); +select DictContains($d,AsTuple(1,2)), DictContains($d,AsTuple(1,4)); + diff --git a/yql/essentials/tests/sql/suites/expr/to_sorted_set_vartuple_key.sql b/yql/essentials/tests/sql/suites/expr/to_sorted_set_vartuple_key.sql new file mode 100644 index 0000000000..04e42b7cd3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/to_sorted_set_vartuple_key.sql @@ -0,0 +1,15 @@ +/* postgres can not */ +$vt = ParseType("Variant<Int32,Uint32>"); +$v1 = Variant(1,"0",$vt); +$v2 = Variant(2u,"1",$vt); +$v3 = Variant(2,"0",$vt); + +$l = AsList( + AsTuple($v1,Void()), + AsTuple($v2,Void()), +); + +$d = ToSortedDict($l); +select ListSort(DictItems($d)),ListSort(DictKeys($d)),ListSort(DictPayloads($d)); +select DictLookup($d,$v1), DictLookup($d,$v3); +select DictContains($d,$v1), DictContains($d,$v3); diff --git a/yql/essentials/tests/sql/suites/expr/try_member.cfg b/yql/essentials/tests/sql/suites/expr/try_member.cfg new file mode 100644 index 0000000000..bb349dd8ab --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/try_member.cfg @@ -0,0 +1 @@ +providers yt diff --git a/yql/essentials/tests/sql/suites/expr/try_member.sql b/yql/essentials/tests/sql/suites/expr/try_member.sql new file mode 100644 index 0000000000..c543b523c0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/try_member.sql @@ -0,0 +1,53 @@ +/* postgres can not */ +/* syntax version 1 */ +/* yt can not */ + +$s = <|a:1, b:2u/1u, c:Just(Just(1))|>; +$js = Just($s); +$es = Nothing(Struct<a:Int32, b:Uint32?, c:Int32??>?); + +-- result of TryMember() when member is not present is third argument +-- (optional third arg if struct is optional, but third arg is not) +select + TryMember($s, "z", 'qqq'), + TryMember($js, "z", null), + TryMember($es, "z", Just(Just('qqq'))), + TryMember($js, "z", 'zzz'), +; + + +-- fully equivalent to <struct>.<name> +select + TryMember($s, "a", null), + TryMember($s, "b", null), + TryMember($s, "c", null), + + TryMember($js, "a", null), + TryMember($js, "b", null), + TryMember($js, "c", null), + + TryMember($es, "a", null), + TryMember($es, "b", null), + TryMember($es, "c", null), +; + +-- TypeOf TryMember is type of third argument +-- field type should either match third type exactly, or (if the third type is optional) +-- Optional(field) should be equal to third type +select + TryMember($s, "a", 999), + TryMember($s, "a", Just(999)), + TryMember($s, "b", Just(999u)), + TryMember($s, "c", Just(Just(999))), + + TryMember($js, "a", 999), + TryMember($js, "a", Just(999)), + TryMember($js, "b", Just(999u)), + TryMember($js, "c", Just(Just(999))), + + TryMember($es, "a", 999), + TryMember($es, "a", Just(999)), + TryMember($es, "b", Just(999u)), + TryMember($es, "c", Just(Just(999))), +; + diff --git a/yql/essentials/tests/sql/suites/expr/type_as_key.sql b/yql/essentials/tests/sql/suites/expr/type_as_key.sql new file mode 100644 index 0000000000..340489684b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/type_as_key.sql @@ -0,0 +1,3 @@ +/* syntax version 1 */ +/* postgres can not */ +select a.int32 from (select 1 as `int32`) as a diff --git a/yql/essentials/tests/sql/suites/expr/tzdate_result.sql b/yql/essentials/tests/sql/suites/expr/tzdate_result.sql new file mode 100644 index 0000000000..e152e43d04 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/tzdate_result.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +/* postgres can not */ +$parse = DateTime::Parse("%Y-%m-%d,%Z"); + +$date_string = '2020-05-30,Europe/Moscow'; +$date = $parse($date_string); + +select +DateTime::MakeTzDate($date), +DateTime::MakeTzDatetime($date); + +select EvaluateExpr(DateTime::MakeTzDate($date)); + +$parse = DateTime::Parse("%Y-%m-%d,%Z"); + +$date_string = '2020-05-30,America/Los_Angeles'; +$date = $parse($date_string); + +select +DateTime::MakeTzDate($date), +DateTime::MakeTzDatetime($date); + +select EvaluateExpr(DateTime::MakeTzDate($date)); diff --git a/yql/essentials/tests/sql/suites/expr/udaf_with_list_zip.sql b/yql/essentials/tests/sql/suites/expr/udaf_with_list_zip.sql new file mode 100644 index 0000000000..c481de24b7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/udaf_with_list_zip.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +/* postgres can not */ +$lists_2sum = ($l1, $l2) -> ( + ListMap(ListZip($l1, $l2), ($x) -> ($x.0 + $x.1)) +); + +$perelement_sum = AGGREGATION_FACTORY( + "UDAF", + ($item, $_parent) -> ( $item ), + ($state, $item, $_parent) -> ( $lists_2sum($state, $item) ), + ($state1, $state2) -> ( $lists_2sum($state1, $state2) ) +); + +SELECT + AGGREGATE_BY(list_col, $perelement_sum) AS cnt1, +FROM AS_TABLE([ +<|"list_col" : [4, 5, 6]|>, +<|"list_col" : [4, 5, 6]|>, +<|"list_col" : [4, 5, 6]|>, +<|"list_col" : [4, 5, 6]|>, +<|"list_col" : [4, 5, 6]|>, +<|"list_col" : [4, 5, 6]|> +]); diff --git a/yql/essentials/tests/sql/suites/expr/unicode_literals.sql b/yql/essentials/tests/sql/suites/expr/unicode_literals.sql new file mode 100644 index 0000000000..1910693fa8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/unicode_literals.sql @@ -0,0 +1,22 @@ +pragma WarnUntypedStringLiterals; +pragma UnicodeLiterals; +$f = ()->{ + return ( + "a"s, + "b"u, + "c"); +}; + +select $f(); + +pragma DisableWarnUntypedStringLiterals; +pragma DisableUnicodeLiterals; +$g = ()->{ + return ( + "a"s, + "b"u, + "c"); +}; + +select $g(); + diff --git a/yql/essentials/tests/sql/suites/expr/untag.sql b/yql/essentials/tests/sql/suites/expr/untag.sql new file mode 100644 index 0000000000..52b2b85f2b --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/untag.sql @@ -0,0 +1,7 @@ +/* postgres can not */ +select + Untag(AsTagged(1,'a'),'a'), + Untag(Just(AsTagged(1,'a')),'a'), + Untag(Nothing(Tagged<Int32,'a'>?),'a'), + Untag(NULL,'a'); + diff --git a/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.cfg b/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.cfg new file mode 100644 index 0000000000..5dae597903 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.cfg @@ -0,0 +1 @@ +xfail diff --git a/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.sql b/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.sql new file mode 100644 index 0000000000..64c87cd8dd --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/unwrap_runtime_fail.sql @@ -0,0 +1,2 @@ +/* postgres can not */ +select Unwrap(null, "message"); diff --git a/yql/essentials/tests/sql/suites/expr/uuid.sql b/yql/essentials/tests/sql/suites/expr/uuid.sql new file mode 100644 index 0000000000..1edc7dd19a --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/uuid.sql @@ -0,0 +1,10 @@ +select Uuid('550e8400-e29b-41d4-a716-446655440000'); +select cast('550e8400-e29b-41d4-a716-446655440000' as Uuid); +select cast(cast('550e8400-e29b-41d4-a716-446655440000' as Uuid) as varchar); +select cast(cast('550e8400-e29b-41d4-a716-446655440000' as Uuid) as varchar) = '550e8400-e29b-41d4-a716-446655440000'; +select Uuid('550e8400-e29b-41d4-a716-446655440000') = cast('550e8400-e29b-41d4-a716-446655440000' as uuid); +select Uuid('550e8400-e29b-41d4-a716-446655440000') < cast('550e8400-e29b-41d4-a716-446655440000' as uuid); +select Uuid('550e8400-e29b-41d4-a716-446655440000') <= cast('550e8400-e29b-41d4-a716-446655440000' as uuid); +select Uuid('550e8400-e29b-41d4-a716-446655440000') > cast('550e8400-e29b-41d4-a716-446655440000' as uuid); +select Uuid('550e8400-e29b-41d4-a716-446655440000') >= cast('550e8400-e29b-41d4-a716-446655440000' as uuid); +select Uuid('550e8400-e29b-41d4-a716-446655440000') != cast('550e8400-e29b-41d4-a716-446655440000' as uuid); diff --git a/yql/essentials/tests/sql/suites/expr/uuid_order.sql b/yql/essentials/tests/sql/suites/expr/uuid_order.sql new file mode 100644 index 0000000000..d11539d077 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/uuid_order.sql @@ -0,0 +1,36 @@ +/* postgres can not */ +select * from ( +select 1 as a,Uuid('00000000-0000-0000-0000-000000000000') as x +union all +select 2 as a,Uuid('01000000-0000-0000-0000-000000000000') as x +union all +select 3 as a,Uuid('00010000-0000-0000-0000-000000000000') as x +union all +select 4 as a,Uuid('00000100-0000-0000-0000-000000000000') as x +union all +select 5 as a,Uuid('00000001-0000-0000-0000-000000000000') as x +union all +select 6 as a,Uuid('00000000-0100-0000-0000-000000000000') as x +union all +select 7 as a,Uuid('00000000-0001-0000-0000-000000000000') as x +union all +select 8 as a,Uuid('00000000-0000-0100-0000-000000000000') as x +union all +select 9 as a,Uuid('00000000-0000-0001-0000-000000000000') as x +union all +select 10 as a,Uuid('00000000-0000-0000-0100-000000000000') as x +union all +select 11 as a,Uuid('00000000-0000-0000-0001-000000000000') as x +union all +select 12 as a,Uuid('00000000-0000-0000-0000-010000000000') as x +union all +select 13 as a,Uuid('00000000-0000-0000-0000-000100000000') as x +union all +select 14 as a,Uuid('00000000-0000-0000-0000-000001000000') as x +union all +select 15 as a,Uuid('00000000-0000-0000-0000-000000010000') as x +union all +select 16 as a,Uuid('00000000-0000-0000-0000-000000000100') as x +union all +select 17 as a,Uuid('00000000-0000-0000-0000-000000000001') as x +) as s order by x; diff --git a/yql/essentials/tests/sql/suites/expr/variant_list_sort.sql b/yql/essentials/tests/sql/suites/expr/variant_list_sort.sql new file mode 100644 index 0000000000..a8128a4c48 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/variant_list_sort.sql @@ -0,0 +1,4 @@ +/* syntax version 1 */ +/* postgres can not */ +$list = [AsVariant(1, "int"),AsVariant(1.f, "float"),AsVariant(1.0, "double"),AsVariant(1u, "unsigned")]; +SELECT ListSortAsc($list) AS asc, ListSortDesc($list) AS desc; diff --git a/yql/essentials/tests/sql/suites/expr/variant_struct_comp.sql b/yql/essentials/tests/sql/suites/expr/variant_struct_comp.sql new file mode 100644 index 0000000000..f7eb33dc84 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/variant_struct_comp.sql @@ -0,0 +1,63 @@ +/* postgres can not */ +$id = ($x)->{ + $f = Yql::Callable(CallableType(0, TypeOf($x)), ()->{return $x}); + return $f(); +}; + +$vt1 = ParseType("Variant<a:Int32,b:Uint32?>"); +$vt2 = ParseType("Variant<b:Int64,c:Uint32>"); + +select AsTuple( + Variant(1,"a",$vt1) == Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) == Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) == Variant($id(2u),"b",$vt2), + Variant(1,"b",$vt1) == Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) == Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) == Variant($id(1u),"b",$vt2) +); + +select AsTuple( + Variant(1,"a",$vt1) != Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) != Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) != Variant($id(2u),"b",$vt2), + Variant(1,"b",$vt1) != Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) != Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) != Variant($id(1u),"b",$vt2) +); + +select AsTuple( + Variant(1,"a",$vt1) < Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) < Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) < Variant($id(2u),"b",$vt2), + Variant(1,"b",$vt1) < Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) < Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) < Variant($id(1u),"b",$vt2) +); + +select AsTuple( + Variant(1,"a",$vt1) <= Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) <= Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) <= Variant($id(2u),"b",$vt2), + Variant(1,"b",$vt1) <= Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) <= Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) <= Variant($id(1u),"b",$vt2) +); + +select AsTuple( + Variant(1,"a",$vt1) > Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) > Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) > Variant($id(2u),"b",$vt2), + Variant(1,"b",$vt1) > Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) > Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) > Variant($id(1u),"b",$vt2) +); + +select AsTuple( + Variant(1,"a",$vt1) >= Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) >= Variant($id(1u),"b",$vt2), + Variant(1,"b",$vt1) >= Variant($id(2u),"b",$vt2), + Variant(1,"b",$vt1) >= Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) >= Variant($id(1u),"c",$vt2), + Variant(1u/0u,"b",$vt1) >= Variant($id(1u),"b",$vt2) +); + diff --git a/yql/essentials/tests/sql/suites/expr/variant_tuple_comp.sql b/yql/essentials/tests/sql/suites/expr/variant_tuple_comp.sql new file mode 100644 index 0000000000..c6e9d072ac --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/variant_tuple_comp.sql @@ -0,0 +1,74 @@ +/* postgres can not */ +$id = ($x)->{ + $f = Yql::Callable(CallableType(0, TypeOf($x)), ()->{return $x}); + return $f(); +}; + +$vt1 = ParseType("Variant<Int32,Uint32?>"); +$vt2 = ParseType("Variant<Int64,Uint32>"); + +select AsTuple( + Variant(1,"0",$vt1) < Variant($id(1u),"0",$vt2), + Variant(1,"0",$vt1) < Variant(2u,"0",$vt2), + Variant(1,"0",$vt1) < Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) < Variant($id(1u),"1",$vt2), + Variant(1,"1",$vt1) < Variant(2u,"1",$vt2), + Variant(2,"1",$vt1) < Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) < Variant($id(1u),"0",$vt2), + Variant(2u/0u,"1",$vt1) < Variant(1u,"1",$vt2) +); + +select AsTuple( + Variant(1,"0",$vt1) <= Variant($id(1u),"0",$vt2), + Variant(1,"0",$vt1) <= Variant(2u,"0",$vt2), + Variant(1,"0",$vt1) <= Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) <= Variant($id(1u),"1",$vt2), + Variant(1,"1",$vt1) <= Variant(2u,"1",$vt2), + Variant(2,"1",$vt1) <= Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) <= Variant($id(1u),"0",$vt2), + Variant(2u/0u,"1",$vt1) <= Variant(1u,"1",$vt2) +); + +select AsTuple( + Variant(1,"0",$vt1) == Variant($id(1u),"0",$vt2), + Variant(1,"0",$vt1) == Variant(2u,"0",$vt2), + Variant(1,"0",$vt1) == Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) == Variant($id(1u),"1",$vt2), + Variant(1,"1",$vt1) == Variant(2u,"1",$vt2), + Variant(2,"1",$vt1) == Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) == Variant($id(1u),"0",$vt2), + Variant(2u/0u,"1",$vt1) == Variant(1u,"1",$vt2) +); + +select AsTuple( + Variant(1,"0",$vt1) != Variant($id(1u),"0",$vt2), + Variant(1,"0",$vt1) != Variant(2u,"0",$vt2), + Variant(1,"0",$vt1) != Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) != Variant($id(1u),"1",$vt2), + Variant(1,"1",$vt1) != Variant(2u,"1",$vt2), + Variant(2,"1",$vt1) != Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) != Variant($id(1u),"0",$vt2), + Variant(2u/0u,"1",$vt1) != Variant(1u,"1",$vt2) +); + +select AsTuple( + Variant(1,"0",$vt1) > Variant($id(1u),"0",$vt2), + Variant(1,"0",$vt1) > Variant(2u,"0",$vt2), + Variant(1,"0",$vt1) > Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) > Variant($id(1u),"1",$vt2), + Variant(1,"1",$vt1) > Variant(2u,"1",$vt2), + Variant(2,"1",$vt1) > Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) > Variant($id(1u),"0",$vt2), + Variant(2u/0u,"1",$vt1) > Variant(1u,"1",$vt2) +); + +select AsTuple( + Variant(1,"0",$vt1) >= Variant($id(1u),"0",$vt2), + Variant(1,"0",$vt1) >= Variant(2u,"0",$vt2), + Variant(1,"0",$vt1) >= Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) >= Variant($id(1u),"1",$vt2), + Variant(1,"1",$vt1) >= Variant(2u,"1",$vt2), + Variant(2,"1",$vt1) >= Variant(1u,"1",$vt2), + Variant(1,"1",$vt1) >= Variant($id(1u),"0",$vt2), + Variant(2u/0u,"1",$vt1) >= Variant(1u,"1",$vt2) +); diff --git a/yql/essentials/tests/sql/suites/expr/with_optional_args.sql b/yql/essentials/tests/sql/suites/expr/with_optional_args.sql new file mode 100644 index 0000000000..5fe5dc6813 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/with_optional_args.sql @@ -0,0 +1,8 @@ +/* syntax version 1 */ +/* postgres can not */ +$f = Yql::WithOptionalArgs(($x,$y,$z)->($x + ($y ?? 0) + ($z ?? 0)), AsAtom("2")); +select $f(1),$f(2,3),$f(4,5,6), + Yql::NamedApply($f,(1,),<||>), + Yql::NamedApply($f,(1,2),<||>), + Yql::NamedApply($f,(1,2,3),<||>), + Yql::NamedApply($f,(1,2,3,4),<||>); diff --git a/yql/essentials/tests/sql/suites/expr/xor.sql b/yql/essentials/tests/sql/suites/expr/xor.sql new file mode 100644 index 0000000000..3799af4276 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/xor.sql @@ -0,0 +1,17 @@ +/* postgres can not */ +SELECT + o1 XOR o2 AS xor, + o1 XOR o1 AS xor_same_o1, + o2 XOR o2 AS xor_same_o2, + o1 XOR o2 XOR o1 XOR o2 XOR o1 XOR o2 AS xor_triple_dups, + o1 XOR o2 XOR Unwrap(o1) XOR Unwrap(o2) AS xor_with_unwraps, +FROM AS_TABLE([ + <|o1: FALSE, o2: FALSE|>, + <|o1: TRUE, o2: TRUE|>, + <|o1: TRUE, o2: FALSE|>, + <|o1: FALSE, o2: TRUE|>, + <|o1: TRUE, o2: NULL|>, + <|o1: FALSE, o2: NULL|>, + <|o1: NULL, o2: TRUE|>, + <|o1: NULL, o2: FALSE|> +]); diff --git a/yql/essentials/tests/sql/suites/expr/yql-10180.sql b/yql/essentials/tests/sql/suites/expr/yql-10180.sql new file mode 100644 index 0000000000..9fa1d68e31 --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/yql-10180.sql @@ -0,0 +1,23 @@ +/* syntax version 1 */ +USE plato; + +$logs_path = "//logs/antirobot-daemon-log2/1d"; +$results_path = "//home/antispam/antirobot/sharding_daily"; +$logs_per_run = 10; + +DEFINE SUBQUERY $last_tables($path, $limit) AS + SELECT AGGREGATE_LIST(Name) + FROM ( + SELECT ListLast(String::SplitToList(Path, "/")) as Name + FROM FOLDER($path) + WHERE Type = "table" + ORDER BY Name DESC + LIMIT $limit + ) +END DEFINE; + + +$logs = (SELECT * FROM $last_tables($logs_path, $logs_per_run)); +$processed_logs = (SELECT * FROM $last_tables($results_path, $logs_per_run)); + +SELECT SetDifference(ToSet($logs), ToSet($processed_logs)) diff --git a/yql/essentials/tests/sql/suites/expr/yql-15485.sql b/yql/essentials/tests/sql/suites/expr/yql-15485.sql new file mode 100644 index 0000000000..b93cdc8e7c --- /dev/null +++ b/yql/essentials/tests/sql/suites/expr/yql-15485.sql @@ -0,0 +1,86 @@ +/* syntax version 1 */ +/* postgres can not */ +$ages = [ + <|suffix: "0-0.5"u, begin: 0.f, end: 0.5f|>, + <|suffix: "0.5-1"u, begin: 0.5f, end: 1.f|>, + <|suffix: "1-3"u , begin: 1.f, end: 3.f|>, + <|suffix: "3-5"u , begin: 3.f, end: 5.f|>, + <|suffix: "5-7"u , begin: 5.f, end: 7.f|>, + <|suffix: "7-9"u , begin: 7.f, end: 9.f|>, + <|suffix: "9-12"u , begin: 9.f, end: 12.f|>, + <|suffix: "12-14"u, begin: 12.f, end: 14.f|>, + <|suffix: "14-16"u, begin: 14.f, end: 16.f|>, + <|suffix: "16+"u , begin: 16.f, end: 18.f|>, +]; + +$interval_fits_in = ($interval, $other) -> { + $length = $interval.end - $interval.begin; + RETURN IF( + $interval.end <= $other.begin OR $interval.begin >= $other.end, + 0.f, + IF( + $interval.begin >= $other.begin AND $interval.end <= $other.end, -- interval is completely within other + 1.f, + IF( + $interval.begin <= $other.begin AND $interval.end >= $other.end, -- other is completely within the interval + ($other.end - $other.begin) / $length, + IF( + $interval.begin < $other.begin, + ($interval.end - $other.begin) / $length, + ($other.end - $interval.begin) / $length + ) + ) + ) + ); +}; + +$age_suffixes = ($interval, $age_segments) -> { + RETURN IF( + $interval.end - $interval.begin > 10.f OR $interval.end - $interval.begin < 1e-4f, + [NULL], + ListFilter( + ListMap( + $age_segments, + ($i) -> { + RETURN <|age_suffix: ":Age:"u || $i.suffix, age_weight: $interval_fits_in($interval, $i)|> + } + ), + ($i) -> { + RETURN $i.age_weight > 1e-4f; + } + ) + ); +}; + +$data = ( + SELECT + * + FROM + ( + SELECT + puid, + ts, + boys ?? False AS boys, + girls ?? False AS girls, + min_age ?? 0.f AS min_age, + max_age ?? 18.f AS max_age + FROM + + AS_TABLE([ + <|puid: 1, ts: 123, boys: True, girls: False, min_age: 1.f, max_age: 2.f|>, + <|puid: 2, ts: 123, boys: True, girls: False, min_age: NULL, max_age: NULL|>, + <|puid: 3, ts: 123, boys: NULL, girls: NULL, min_age: 1.f, max_age: 2.f|>, + <|puid: 4, ts: 123, boys: True, girls: True, min_age: 1.f, max_age: 2.f|>, + <|puid: 5, ts: 123, boys: True, girls: True, min_age: 1.f, max_age: 5.f|>, + <|puid: 6, ts: 123, boys: True, girls: False, min_age: 1.f, max_age: 2.f|>, + ]) + + ) + WHERE boys OR girls OR min_age > 0.f OR max_age < 18.f +); + +SELECT + puid, + $age_suffixes(<|begin: min_age, end: max_age|>, $ages) AS age_suffixes, + <|begin: min_age, end: max_age|> as interval +FROM $data; |