diff options
| author | vitya-smirnov <[email protected]> | 2025-10-01 20:51:41 +0300 |
|---|---|---|
| committer | vitya-smirnov <[email protected]> | 2025-10-01 21:25:50 +0300 |
| commit | f3f7b33a285e94cb3e27aaa4d9b23b543ece4106 (patch) | |
| tree | 89740eeddb803123f1ed3d6b6f7be2bcd865f68c /yql/essentials/tests/sql | |
| parent | 5cfb01d33937d2b2e038abf3b50e04136dcf7496 (diff) | |
YQL-20307: Support inline subqueries
- Alter grammar to support inline subqueries.
- Support inline subqueries in `sql/v1` (translator).
- Introduce `sql/v1/proto_ast/parse_tree.h` for reusable parse tree predicates.
- Support inline subqueries in `sql/v1/format`.
- Support inline subqueries in `sql/v1/complete`.
- Add some SQL-tests.
- Pass all tests.
commit_hash:075b2240778d071e1c7542f912d3cc83019ef849
Diffstat (limited to 'yql/essentials/tests/sql')
40 files changed, 1661 insertions, 3 deletions
diff --git a/yql/essentials/tests/sql/minirun/part0/canondata/result.json b/yql/essentials/tests/sql/minirun/part0/canondata/result.json index 5e3880d6769..92663561e1b 100644 --- a/yql/essentials/tests/sql/minirun/part0/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part0/canondata/result.json @@ -1611,6 +1611,48 @@ "uri": "https://{canondata_backend}/1937367/129cce9c3960b60552c52a20100a5a905edd36fc/resource.tar.gz#test.test_side_effects-logical_bools_skipped-default.txt-Results_/results.txt" } ], + "test.test[subselect-aggregate_over-default.txt-Debug]": [ + { + "checksum": "606386651aefb1eabfe8751229b18525", + "size": 3416, + "uri": "https://{canondata_backend}/1784826/add1c2b07fe81b8c1e813913fe820100c8e6eb4e/resource.tar.gz#test.test_subselect-aggregate_over-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-aggregate_over-default.txt-Results]": [ + { + "checksum": "4464d680db00468935deb5adb2b69e58", + "size": 5176, + "uri": "https://{canondata_backend}/1784826/add1c2b07fe81b8c1e813913fe820100c8e6eb4e/resource.tar.gz#test.test_subselect-aggregate_over-default.txt-Results_/results.txt" + } + ], + "test.test[subselect-having-default.txt-Debug]": [ + { + "checksum": "bc154073eca159d685bbb12c255dacd5", + "size": 2436, + "uri": "https://{canondata_backend}/1925821/04cf2caa9b448793f65bdc6e39aad672f543f8be/resource.tar.gz#test.test_subselect-having-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-having-default.txt-Results]": [ + { + "checksum": "222f12bc7e743b3fd54853e408ac2a24", + "size": 3251, + "uri": "https://{canondata_backend}/1925821/04cf2caa9b448793f65bdc6e39aad672f543f8be/resource.tar.gz#test.test_subselect-having-default.txt-Results_/results.txt" + } + ], + "test.test[subselect-where_named_node-default.txt-Debug]": [ + { + "checksum": "defa97dd1b2b7ea02420da3d7694d377", + "size": 1477, + "uri": "https://{canondata_backend}/1936842/147b7134c23d5bb546271f2abe563dfa97d08781/resource.tar.gz#test.test_subselect-where_named_node-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-where_named_node-default.txt-Results]": [ + { + "checksum": "d04cff341495a7c63a3bc924ad55726c", + "size": 3230, + "uri": "https://{canondata_backend}/1936842/147b7134c23d5bb546271f2abe563dfa97d08781/resource.tar.gz#test.test_subselect-where_named_node-default.txt-Results_/results.txt" + } + ], "test.test[udf-udf_with_settings--Debug]": [ { "checksum": "a7b364b692c35af7b468f42ab6a7b43f", diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json index 90e86721537..6994021845f 100644 --- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json @@ -955,7 +955,7 @@ { "checksum": "a6083e95a2a0ef0db3022a6481b79ab2", "size": 374, - "uri": "https://{canondata_backend}/1130705/0f717596bbab92f11dfd78d28fde4a2785f033ef/resource.tar.gz#test.test_linear-mutdict_copy-default.txt-Debug_/opt.yql" + "uri": "https://{canondata_backend}/1923547/0f717596bbab92f11dfd78d28fde4a2785f033ef/resource.tar.gz#test.test_linear-mutdict_copy-default.txt-Debug_/opt.yql" } ], "test.test[linear-mutdict_copy-default.txt-Results]": [ @@ -1531,6 +1531,20 @@ "uri": "https://{canondata_backend}/1936842/f457790bc6a954bcb4a6a17ab144af05c57a7754/resource.tar.gz#test.test_select-digits--Results_/results.txt" } ], + "test.test[select_op-select_op_precedence_named_node-default.txt-Debug]": [ + { + "checksum": "3cc5644e63d6d4d4601132f97c177a71", + "size": 2350, + "uri": "https://{canondata_backend}/1775319/60b6d8d1eea951b7a0bb833ba189ada82576641b/resource.tar.gz#test.test_select_op-select_op_precedence_named_node-default.txt-Debug_/opt.yql" + } + ], + "test.test[select_op-select_op_precedence_named_node-default.txt-Results]": [ + { + "checksum": "c0efe7c81a642f898ef8a9edaa9744eb", + "size": 2355, + "uri": "https://{canondata_backend}/1775319/60b6d8d1eea951b7a0bb833ba189ada82576641b/resource.tar.gz#test.test_select_op-select_op_precedence_named_node-default.txt-Results_/results.txt" + } + ], "test.test[seq_mode-shared_named_expr-default.txt-Debug]": [ { "checksum": "6de9752b20a85eb7d5c4b1508dfd607d", diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/result.json b/yql/essentials/tests/sql/minirun/part2/canondata/result.json index 43ad8e8356b..22871af88c9 100644 --- a/yql/essentials/tests/sql/minirun/part2/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part2/canondata/result.json @@ -1555,6 +1555,20 @@ "uri": "https://{canondata_backend}/1903885/cded149c62f5a6fd23e6f6d1687363c458e88b9b/resource.tar.gz#test.test_select-unary_op_interval-default.txt-Results_/results.txt" } ], + "test.test[subselect-aggregate-default.txt-Debug]": [ + { + "checksum": "940a87f9014f6f29192592e003a9e1bd", + "size": 2594, + "uri": "https://{canondata_backend}/1775059/f3a7cf026cbcbe29d7b02e81e0dd213db47fb2b4/resource.tar.gz#test.test_subselect-aggregate-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-aggregate-default.txt-Results]": [ + { + "checksum": "91560dcf35e4c471d19f5a80d4ad07bb", + "size": 3524, + "uri": "https://{canondata_backend}/1775059/f3a7cf026cbcbe29d7b02e81e0dd213db47fb2b4/resource.tar.gz#test.test_subselect-aggregate-default.txt-Results_/results.txt" + } + ], "test.test[udf-complex_return_type--Debug]": [ { "checksum": "b1dc51e1c2d1b8005c435896dc18fabe", diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json index 120546d662c..b412c35851f 100644 --- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json @@ -1295,6 +1295,48 @@ "uri": "https://{canondata_backend}/1946324/99e40d03148e2f5db80e78803a41f37aeb697296/resource.tar.gz#test.test_select-result_label-default.txt-Results_/results.txt" } ], + "test.test[subselect-partition_by-default.txt-Debug]": [ + { + "checksum": "3db193f3cc8ff114a4ba987cf4ce4a2f", + "size": 2779, + "uri": "https://{canondata_backend}/1937001/1a8479476498108a8ea141894a0e564a74ce8ff3/resource.tar.gz#test.test_subselect-partition_by-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-partition_by-default.txt-Results]": [ + { + "checksum": "51d305defc4153343d3bae685cce9712", + "size": 3922, + "uri": "https://{canondata_backend}/1937001/1a8479476498108a8ea141894a0e564a74ce8ff3/resource.tar.gz#test.test_subselect-partition_by-default.txt-Results_/results.txt" + } + ], + "test.test[subselect-projection-default.txt-Debug]": [ + { + "checksum": "56f1254308c9c748e85a56f8dcd32b44", + "size": 1360, + "uri": "https://{canondata_backend}/1942278/2f6c3bc0f22706ca3ce400fde2891be367a1c1c0/resource.tar.gz#test.test_subselect-projection-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-projection-default.txt-Results]": [ + { + "checksum": "9e682a9fa784c6f2dc5cb2cdc861b95c", + "size": 2641, + "uri": "https://{canondata_backend}/1942278/2f6c3bc0f22706ca3ce400fde2891be367a1c1c0/resource.tar.gz#test.test_subselect-projection-default.txt-Results_/results.txt" + } + ], + "test.test[subselect-where-default.txt-Debug]": [ + { + "checksum": "0871dde5618bcfcd782de5385357d6ae", + "size": 1863, + "uri": "https://{canondata_backend}/1942278/2f6c3bc0f22706ca3ce400fde2891be367a1c1c0/resource.tar.gz#test.test_subselect-where-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-where-default.txt-Results]": [ + { + "checksum": "d04cff341495a7c63a3bc924ad55726c", + "size": 3230, + "uri": "https://{canondata_backend}/1942278/2f6c3bc0f22706ca3ce400fde2891be367a1c1c0/resource.tar.gz#test.test_subselect-where-default.txt-Results_/results.txt" + } + ], "test.test[union-union_mix-default.txt-Debug]": [ { "checksum": "f98189d19a514e38c537ad2190945414", diff --git a/yql/essentials/tests/sql/minirun/part5/canondata/result.json b/yql/essentials/tests/sql/minirun/part5/canondata/result.json index 67996d579cf..25d56e4e99a 100644 --- a/yql/essentials/tests/sql/minirun/part5/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part5/canondata/result.json @@ -1943,6 +1943,34 @@ "uri": "https://{canondata_backend}/1900335/2e7a3a0284dd25621ff685fc5c99cf0aa70fd6a5/resource.tar.gz#test.test_side_effects-x_not_x-default.txt-Results_/results.txt" } ], + "test.test[subselect-limit-default.txt-Debug]": [ + { + "checksum": "de86d4e45aba6e09a5162523125ace0b", + "size": 1813, + "uri": "https://{canondata_backend}/1871182/4bf1926730e8ab6d22db16bd5d230ab2169a7e96/resource.tar.gz#test.test_subselect-limit-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-limit-default.txt-Results]": [ + { + "checksum": "8d7e718bf99968831d164e7607e8a3fd", + "size": 4714, + "uri": "https://{canondata_backend}/1871182/4bf1926730e8ab6d22db16bd5d230ab2169a7e96/resource.tar.gz#test.test_subselect-limit-default.txt-Results_/results.txt" + } + ], + "test.test[subselect-union-default.txt-Debug]": [ + { + "checksum": "def1a0d99f02961b9f368ea19575da7f", + "size": 1686, + "uri": "https://{canondata_backend}/1781765/6864694787646cf22ed1568cf6a00daadd1d8609/resource.tar.gz#test.test_subselect-union-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-union-default.txt-Results]": [ + { + "checksum": "dccec048b5008170c8a678a3def94f7a", + "size": 4536, + "uri": "https://{canondata_backend}/1781765/6864694787646cf22ed1568cf6a00daadd1d8609/resource.tar.gz#test.test_subselect-union-default.txt-Results_/results.txt" + } + ], "test.test[udf-depends2--Debug]": [ { "checksum": "a4995c8a32627863503a6786da4f0d19", diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/result.json b/yql/essentials/tests/sql/minirun/part7/canondata/result.json index 3d6311a2592..aa432ef0181 100644 --- a/yql/essentials/tests/sql/minirun/part7/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part7/canondata/result.json @@ -1440,6 +1440,23 @@ "uri": "https://{canondata_backend}/1937367/671e3ad35d46785ff29a521b44f889ed7a487cc7/resource.tar.gz#test.test_side_effects-logical_bools_unreachable-default.txt-Results_/results.txt" } ], + "test.test[subselect-flatten_by-default.txt-Debug]": [ + { + "checksum": "fedad718a56608b20b47747f0ac927c1", + "size": 2342, + "uri": "https://{canondata_backend}/1871102/53657df02c932e68f816dd09e0a344dbb19d715d/resource.tar.gz#test.test_subselect-flatten_by-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-flatten_by-default.txt-Results]": [ + { + "checksum": "1bc9f8373b6e7b0b8166bda77d801ede", + "size": 9530, + "uri": "https://{canondata_backend}/1871102/53657df02c932e68f816dd09e0a344dbb19d715d/resource.tar.gz#test.test_subselect-flatten_by-default.txt-Results_/results.txt" + }, + { + "uri": "file://test.test_subselect-flatten_by-default.txt-Results_/extracted" + } + ], "test.test[union-union_positional_mix-default.txt-Debug]": [ { "checksum": "35839a396fdaff3806b0a6117135e8b7", diff --git a/yql/essentials/tests/sql/minirun/part7/canondata/test.test_subselect-flatten_by-default.txt-Results_/extracted b/yql/essentials/tests/sql/minirun/part7/canondata/test.test_subselect-flatten_by-default.txt-Results_/extracted new file mode 100644 index 00000000000..1aca4843f0a --- /dev/null +++ b/yql/essentials/tests/sql/minirun/part7/canondata/test.test_subselect-flatten_by-default.txt-Results_/extracted @@ -0,0 +1,29 @@ +<tmp_path>/program.sql:<main>: Warning: Type annotation + + <tmp_path>/program.sql:<main>:11:1: Warning: At function: RemovePrefixMembers, At function: Unordered, At function: RemoveSystemMembers, At function: PersistableRepr, At function: OrderedSqlProject, At function: OrderedFlatMap, At lambda + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT 3)) AS x); + ^ + <tmp_path>/program.sql:<main>:11:15: Warning: At function: FlattenByColumns + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT 3)) AS x); + ^ + <tmp_path>/program.sql:<main>:11:1: Warning: Ambiguous FLATTEN BY statement, please choose FLATTEN LIST BY or FLATTEN OPTIONAL BY + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT 3)) AS x); + ^ + <tmp_path>/program.sql:<main>:13:1: Warning: At function: RemovePrefixMembers, At function: Unordered, At function: RemoveSystemMembers, At function: PersistableRepr, At function: OrderedSqlProject, At function: OrderedFlatMap, At lambda + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input)) AS x); + ^ + <tmp_path>/program.sql:<main>:13:15: Warning: At function: FlattenByColumns + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input)) AS x); + ^ + <tmp_path>/program.sql:<main>:13:1: Warning: Ambiguous FLATTEN BY statement, please choose FLATTEN LIST BY or FLATTEN OPTIONAL BY + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input)) AS x); + ^ + <tmp_path>/program.sql:<main>:15:1: Warning: At function: RemovePrefixMembers, At function: Unordered, At function: RemoveSystemMembers, At function: PersistableRepr, At function: OrderedSqlProject, At function: OrderedFlatMap, At lambda + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input WHERE v == 3)) AS x); + ^ + <tmp_path>/program.sql:<main>:15:15: Warning: At function: FlattenByColumns + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input WHERE v == 3)) AS x); + ^ + <tmp_path>/program.sql:<main>:15:1: Warning: Ambiguous FLATTEN BY statement, please choose FLATTEN LIST BY or FLATTEN OPTIONAL BY + SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input WHERE v == 3)) AS x); + ^
\ No newline at end of file diff --git a/yql/essentials/tests/sql/minirun/part9/canondata/result.json b/yql/essentials/tests/sql/minirun/part9/canondata/result.json index 30c20fc7032..9557d452799 100644 --- a/yql/essentials/tests/sql/minirun/part9/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part9/canondata/result.json @@ -1552,6 +1552,20 @@ "uri": "https://{canondata_backend}/1937429/18d53b6e51c2d67a4139b1541925383ccf907eb1/resource.tar.gz#test.test_select_op-select_op_order_by-default.txt-Results_/results.txt" } ], + "test.test[subselect-offset-default.txt-Debug]": [ + { + "checksum": "407648bc04bab290eacbdbee1b1eeb91", + "size": 1848, + "uri": "https://{canondata_backend}/1936997/40b40d8aaac6174752a8f2949ccda8f4542f16d3/resource.tar.gz#test.test_subselect-offset-default.txt-Debug_/opt.yql" + } + ], + "test.test[subselect-offset-default.txt-Results]": [ + { + "checksum": "a7c83fed96588848d85fe7013b9bfcc8", + "size": 4186, + "uri": "https://{canondata_backend}/1936997/40b40d8aaac6174752a8f2949ccda8f4542f16d3/resource.tar.gz#test.test_subselect-offset-default.txt-Results_/results.txt" + } + ], "test.test[udf-udaf_default-default.txt-Debug]": [ { "checksum": "8f8b1454954834dfed9debc99927346a", diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json index 9994a22961d..5653adcf262 100644 --- a/yql/essentials/tests/sql/sql2yql/canondata/result.json +++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json @@ -5099,7 +5099,7 @@ { "checksum": "707b29e9ab12763d6c8a135a571bc120", "size": 1205, - "uri": "https://{canondata_backend}/1942671/d721729a70844cec9425cad0a129e47d493b2668/resource.tar.gz#test_sql2yql.test_linear-mutdict_copy_/sql.yql" + "uri": "https://{canondata_backend}/1031349/d721729a70844cec9425cad0a129e47d493b2668/resource.tar.gz#test_sql2yql.test_linear-mutdict_copy_/sql.yql" } ], "test_sql2yql.test[linear-mutdict_empty]": [ @@ -8140,6 +8140,13 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql2yql.test_select_op-select_op_precedence_/sql.yql" } ], + "test_sql2yql.test[select_op-select_op_precedence_named_node]": [ + { + "checksum": "56daeccbda41fa7744c105f671fa9dbd", + "size": 9595, + "uri": "https://{canondata_backend}/1942671/82ed7d067ef141d1fcef204dc3b89bf4e1c70c57/resource.tar.gz#test_sql2yql.test_select_op-select_op_precedence_named_node_/sql.yql" + } + ], "test_sql2yql.test[seq_mode-shared_named_expr]": [ { "checksum": "1bc276a9cb08e31273e24da9ad90582d", @@ -8322,6 +8329,83 @@ "uri": "https://{canondata_backend}/1775059/be020ceb210e118c9d8f06ae73e0f97466c49478/resource.tar.gz#test_sql2yql.test_side_effects-x_not_x_fail_/sql.yql" } ], + "test_sql2yql.test[subselect-aggregate]": [ + { + "checksum": "f948f4ad0c25bd1bf52f63dc1ba367e7", + "size": 7018, + "uri": "https://{canondata_backend}/1936842/befed0b346683be8225cf804762adb36685c15a1/resource.tar.gz#test_sql2yql.test_subselect-aggregate_/sql.yql" + } + ], + "test_sql2yql.test[subselect-aggregate_over]": [ + { + "checksum": "358b518ab969fee7019a1255fa3d89a5", + "size": 7950, + "uri": "https://{canondata_backend}/1689644/0989c91e43a233bd2d2fb3ce8744224bfdd8a98c/resource.tar.gz#test_sql2yql.test_subselect-aggregate_over_/sql.yql" + } + ], + "test_sql2yql.test[subselect-flatten_by]": [ + { + "checksum": "8b2779a99abfdff442a129c801a4df87", + "size": 7562, + "uri": "https://{canondata_backend}/1871102/5cbff22143d7f6b1bacea6231269c3ce9cc755ce/resource.tar.gz#test_sql2yql.test_subselect-flatten_by_/sql.yql" + } + ], + "test_sql2yql.test[subselect-having]": [ + { + "checksum": "c687c5999ef3ef849b792bd1af66fa58", + "size": 7572, + "uri": "https://{canondata_backend}/1937429/cde37b81316143769e7c3cb819c2d6539106c0f2/resource.tar.gz#test_sql2yql.test_subselect-having_/sql.yql" + } + ], + "test_sql2yql.test[subselect-limit]": [ + { + "checksum": "0c74577c4404a48838bbb3c9d4599d28", + "size": 6692, + "uri": "https://{canondata_backend}/1936997/8a972c55862e0eb44e30da5b57454d9d28c36af5/resource.tar.gz#test_sql2yql.test_subselect-limit_/sql.yql" + } + ], + "test_sql2yql.test[subselect-offset]": [ + { + "checksum": "bcffd7739b8bff115c15488f267c12fc", + "size": 7032, + "uri": "https://{canondata_backend}/1871182/5d02a77ae9eea603f089879e9ca07bcdd644314c/resource.tar.gz#test_sql2yql.test_subselect-offset_/sql.yql" + } + ], + "test_sql2yql.test[subselect-partition_by]": [ + { + "checksum": "d6fcf13e209c8ae6e4fb77fd8a4a056b", + "size": 7950, + "uri": "https://{canondata_backend}/1937424/d07f8632fc7a29aabb6b585dfce3b46993c21722/resource.tar.gz#test_sql2yql.test_subselect-partition_by_/sql.yql" + } + ], + "test_sql2yql.test[subselect-projection]": [ + { + "checksum": "0115369c6c6f8693e1a957fab28df068", + "size": 5702, + "uri": "https://{canondata_backend}/1942278/29cbf9b34bce7d402d33a3942ea29f5167d37a1d/resource.tar.gz#test_sql2yql.test_subselect-projection_/sql.yql" + } + ], + "test_sql2yql.test[subselect-union]": [ + { + "checksum": "43d04e67d2f8629c531561e34aa41d90", + "size": 20115, + "uri": "https://{canondata_backend}/1781765/53187e0fae3b9cd8c495fb9f0a200c799399c140/resource.tar.gz#test_sql2yql.test_subselect-union_/sql.yql" + } + ], + "test_sql2yql.test[subselect-where]": [ + { + "checksum": "865b800246b8889f3938d93da6ba9f85", + "size": 5912, + "uri": "https://{canondata_backend}/1942278/29cbf9b34bce7d402d33a3942ea29f5167d37a1d/resource.tar.gz#test_sql2yql.test_subselect-where_/sql.yql" + } + ], + "test_sql2yql.test[subselect-where_named_node]": [ + { + "checksum": "dc8f25aaf267d2dcea6724a6df940b43", + "size": 4700, + "uri": "https://{canondata_backend}/1946324/adec6e8449b8734f0882dc7b5266ea4af0cf09c4/resource.tar.gz#test_sql2yql.test_subselect-where_named_node_/sql.yql" + } + ], "test_sql2yql.test[type_literal-evaluate]": [ { "checksum": "710dafde8986d41d985bd47be65641ab", @@ -13053,6 +13137,11 @@ "uri": "file://test_sql_format.test_select_op-select_op_precedence_/formatted.sql" } ], + "test_sql_format.test[select_op-select_op_precedence_named_node]": [ + { + "uri": "file://test_sql_format.test_select_op-select_op_precedence_named_node_/formatted.sql" + } + ], "test_sql_format.test[seq_mode-shared_named_expr]": [ { "uri": "file://test_sql_format.test_seq_mode-shared_named_expr_/formatted.sql" @@ -13183,6 +13272,61 @@ "uri": "file://test_sql_format.test_side_effects-x_not_x_fail_/formatted.sql" } ], + "test_sql_format.test[subselect-aggregate]": [ + { + "uri": "file://test_sql_format.test_subselect-aggregate_/formatted.sql" + } + ], + "test_sql_format.test[subselect-aggregate_over]": [ + { + "uri": "file://test_sql_format.test_subselect-aggregate_over_/formatted.sql" + } + ], + "test_sql_format.test[subselect-flatten_by]": [ + { + "uri": "file://test_sql_format.test_subselect-flatten_by_/formatted.sql" + } + ], + "test_sql_format.test[subselect-having]": [ + { + "uri": "file://test_sql_format.test_subselect-having_/formatted.sql" + } + ], + "test_sql_format.test[subselect-limit]": [ + { + "uri": "file://test_sql_format.test_subselect-limit_/formatted.sql" + } + ], + "test_sql_format.test[subselect-offset]": [ + { + "uri": "file://test_sql_format.test_subselect-offset_/formatted.sql" + } + ], + "test_sql_format.test[subselect-partition_by]": [ + { + "uri": "file://test_sql_format.test_subselect-partition_by_/formatted.sql" + } + ], + "test_sql_format.test[subselect-projection]": [ + { + "uri": "file://test_sql_format.test_subselect-projection_/formatted.sql" + } + ], + "test_sql_format.test[subselect-union]": [ + { + "uri": "file://test_sql_format.test_subselect-union_/formatted.sql" + } + ], + "test_sql_format.test[subselect-where]": [ + { + "uri": "file://test_sql_format.test_subselect-where_/formatted.sql" + } + ], + "test_sql_format.test[subselect-where_named_node]": [ + { + "uri": "file://test_sql_format.test_subselect-where_named_node_/formatted.sql" + } + ], "test_sql_format.test[type_literal-evaluate]": [ { "uri": "file://test_sql_format.test_type_literal-evaluate_/formatted.sql" @@ -13641,7 +13785,7 @@ { "checksum": "5362cc94a32ae614cc3808035e96d473", "size": 388, - "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql_negative.test_flatten_by-struct_with_wrong_correlation-default.txt_/err_file.out" + "uri": "https://{canondata_backend}/1889210/f76d30924636e40d4d8bbcffdf8ec586290b624e/resource.tar.gz#test_sql_negative.test_flatten_by-struct_with_wrong_correlation-default.txt_/err_file.out" } ], "test_sql_negative.test[flatten_by-table_funcs_spec_flatten_by-default.txt]": [ @@ -13805,6 +13949,48 @@ "uri": "https://{canondata_backend}/1937150/ec0019724df75083b0e89cab22f57e10ef36744e/resource.tar.gz#test_sql_negative.test_select-use_subrequest_as_table-default.txt_/err_file.out" } ], + "test_sql_negative.test[subselect-group_by_no_source-default.txt]": [ + { + "checksum": "89df5ece669c02f9a18c773bda291633", + "size": 183, + "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-group_by_no_source-default.txt_/err_file.out" + } + ], + "test_sql_negative.test[subselect-group_by_source-default.txt]": [ + { + "checksum": "803a3c4118ec1171dcccf83358e9364e", + "size": 204, + "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-group_by_source-default.txt_/err_file.out" + } + ], + "test_sql_negative.test[subselect-group_by_source_filter-default.txt]": [ + { + "checksum": "2c653e2e3eabdf4881fe2b71486e8108", + "size": 217, + "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-group_by_source_filter-default.txt_/err_file.out" + } + ], + "test_sql_negative.test[subselect-order_by_no_source-default.txt]": [ + { + "checksum": "96b93e9696061443a0bf95d2234aed49", + "size": 168, + "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-order_by_no_source-default.txt_/err_file.out" + } + ], + "test_sql_negative.test[subselect-order_by_source-default.txt]": [ + { + "checksum": "f9b87f51cdd9dbb9bdc6ac499727ba55", + "size": 189, + "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-order_by_source-default.txt_/err_file.out" + } + ], + "test_sql_negative.test[subselect-order_by_source_filter-default.txt]": [ + { + "checksum": "7cd36602aca05ed3e3f516bc1bb295ef", + "size": 202, + "uri": "https://{canondata_backend}/1847551/94ec1bb7188d22f702c7e9004e1fd04d56e74a2c/resource.tar.gz#test_sql_negative.test_subselect-order_by_source_filter-default.txt_/err_file.out" + } + ], "test_sql_negative.test[udf-named_args_for_script_with_wrong_order-default.txt]": [ { "checksum": "e366faf0a12a73e0040753ae74cf0a55", diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_op-select_op_precedence_named_node_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_op-select_op_precedence_named_node_/formatted.sql new file mode 100644 index 00000000000..0a94efda1ca --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_select_op-select_op_precedence_named_node_/formatted.sql @@ -0,0 +1,159 @@ +$t1 = ( + ( + SELECT + * + FROM ( + VALUES + (1), + (2) + ) AS t ( + x + ) + ) + UNION + ( + SELECT + * + FROM ( + VALUES + (2), + (3) + ) AS t ( + x + ) + ) + INTERSECT + ( + SELECT + * + FROM ( + VALUES + (3) + ) AS t ( + x + ) + ) +); + +$t2 = ( + ( + SELECT + * + FROM ( + VALUES + (3) + ) AS t ( + x + ) + ) + UNION + ( + SELECT + * + FROM ( + VALUES + (2) + ) AS t ( + x + ) + ) + EXCEPT + ( + SELECT + * + FROM ( + VALUES + (3) + ) AS t ( + x + ) + ) +); + +$t3 = ( + ( + SELECT + * + FROM ( + VALUES + (1) + ) AS t ( + x + ) + ) + UNION + ( + SELECT + * + FROM ( + VALUES + (2) + ) AS t ( + x + ) + ) + INTERSECT + ( + SELECT + * + FROM ( + VALUES + (2), + (3) + ) AS t ( + x + ) + ) + EXCEPT + ( + SELECT + * + FROM ( + VALUES + (3) + ) AS t ( + x + ) + ) + UNION + ( + SELECT + * + FROM ( + VALUES + (4), + (3) + ) AS t ( + x + ) + ) + EXCEPT + ( + SELECT + * + FROM ( + VALUES + (4) + ) AS t ( + x + ) + ) +); + +SELECT + * +FROM + $t1 +; + +SELECT + * +FROM + $t2 +; + +SELECT + * +FROM + $t3 +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_/formatted.sql new file mode 100644 index 00000000000..09ae6730441 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_/formatted.sql @@ -0,0 +1,57 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +SELECT + Sum(3) AS x +FROM + $input +; + +SELECT + Sum( + ( + SELECT + 3 + ) + ) AS x +FROM + $input +; + +SELECT + Sum( + ( + SELECT + Avg(v) + FROM + $input + ) + ) AS x +FROM + $input +; + +SELECT + Sum( + ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) + ) AS x +FROM + $input +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_over_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_over_/formatted.sql new file mode 100644 index 00000000000..a6399abcec2 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-aggregate_over_/formatted.sql @@ -0,0 +1,85 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +SELECT + Sum(3) OVER w AS x +FROM + $input +WINDOW + w AS ( + PARTITION BY + 3 + ORDER BY + k + ) +; + +SELECT + Sum( + ( + SELECT + 3 + ) + ) OVER w AS x +FROM + $input +WINDOW + w AS ( + PARTITION BY + 3 + ORDER BY + k + ) +; + +SELECT + Sum( + ( + SELECT + Avg(v) + FROM + $input + ) + ) OVER w AS x +FROM + $input +WINDOW + w AS ( + PARTITION BY + 3 + ORDER BY + k + ) +; + +SELECT + Sum( + ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) + ) OVER w AS x +FROM + $input +WINDOW + w AS ( + PARTITION BY + 3 + ORDER BY + k + ) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-flatten_by_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-flatten_by_/formatted.sql new file mode 100644 index 00000000000..b52ae89010c --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-flatten_by_/formatted.sql @@ -0,0 +1,69 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 1, v: 2|>, + <|k: 1, v: 3|>, + <|k: 1, v: 4|>, + <|k: 1, v: 5|>, + ]) +); + +SELECT + * +FROM + $input + FLATTEN BY ( + ListFromRange(1, 3) AS x + ) +; + +SELECT + * +FROM + $input + FLATTEN BY ( + ListFromRange( + 1, ( + SELECT + 3 + ) + ) AS x + ) +; + +SELECT + * +FROM + $input + FLATTEN BY ( + ListFromRange( + 1, ( + SELECT + Avg(v) + FROM + $input + ) + ) AS x + ) +; + +SELECT + * +FROM + $input + FLATTEN BY ( + ListFromRange( + 1, ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) + ) AS x + ) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-having_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-having_/formatted.sql new file mode 100644 index 00000000000..ef64fe8521d --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-having_/formatted.sql @@ -0,0 +1,60 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, <|k: 2, v: 1|>, + <|k: 1, v: 2|>, <|k: 2, v: 2|>, + <|k: 1, v: 3|>, <|k: 2, v: 3|>, + <|k: 1, v: 4|>, <|k: 2, v: 4|>, + <|k: 1, v: 5|>, <|k: 2, v: 5|>, + ]) +); + +SELECT + k, + Avg(v) +FROM + $input +GROUP BY + k +HAVING + Avg(v) == ( + SELECT + 3 + ) +; + +SELECT + k, + Avg(v) +FROM + $input +GROUP BY + k +HAVING + Avg(v) == ( + SELECT + Avg(v) + FROM + $input + ) +; + +SELECT + k, + Avg(v) +FROM + $input +GROUP BY + k +HAVING + Avg(v) == ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-limit_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-limit_/formatted.sql new file mode 100644 index 00000000000..353ef99580c --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-limit_/formatted.sql @@ -0,0 +1,57 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +SELECT + * +FROM + $input +LIMIT CAST(3 AS UInt64); + +SELECT + * +FROM + $input +LIMIT CAST( + ( + SELECT + 3 + ) AS UInt64 +); + +SELECT + * +FROM + $input +LIMIT CAST( + ( + SELECT + Avg(v) + FROM + $input + ) AS UInt64 +); + +SELECT + * +FROM + $input +LIMIT CAST( + ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) AS UInt64 +); diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-offset_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-offset_/formatted.sql new file mode 100644 index 00000000000..df19827c782 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-offset_/formatted.sql @@ -0,0 +1,65 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +SELECT + * +FROM + $input +ORDER BY + k +LIMIT NULL OFFSET CAST(3 AS Uint64); + +SELECT + * +FROM + $input +ORDER BY + k +LIMIT NULL OFFSET CAST( + ( + SELECT + 3 + ) AS Uint64 +); + +SELECT + * +FROM + $input +ORDER BY + k +LIMIT NULL OFFSET CAST( + ( + SELECT + Avg(v) + FROM + $input + ) AS Uint64 +); + +SELECT + * +FROM + $input +ORDER BY + k +LIMIT NULL OFFSET CAST( + ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) AS Uint64 +); diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-partition_by_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-partition_by_/formatted.sql new file mode 100644 index 00000000000..cc34fcfe5b2 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-partition_by_/formatted.sql @@ -0,0 +1,79 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +SELECT + Count(*) OVER w AS x +FROM + $input +WINDOW + w AS ( + PARTITION BY + 3 + ORDER BY + k + ) +; + +SELECT + Count(*) OVER w AS x +FROM + $input +WINDOW + w AS ( + PARTITION BY + ( + SELECT + 3 + ) + ORDER BY + k + ) +; + +SELECT + Count(*) OVER w AS x +FROM + $input +WINDOW + w AS ( + PARTITION BY + ( + SELECT + Avg(v) + FROM + $input + ) + ORDER BY + k + ) +; + +SELECT + Count(*) OVER w AS x +FROM + $input +WINDOW + w AS ( + PARTITION BY + ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) + ORDER BY + k + ) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-projection_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-projection_/formatted.sql new file mode 100644 index 00000000000..6c66310617e --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-projection_/formatted.sql @@ -0,0 +1,39 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +SELECT + ( + SELECT + 3 + ) AS x +; + +SELECT + ( + SELECT + Avg(v) + FROM + $input + ) AS x +; + +SELECT + ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) AS x +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-union_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-union_/formatted.sql new file mode 100644 index 00000000000..9a7b16eb0ec --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-union_/formatted.sql @@ -0,0 +1,217 @@ +$t1 = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +$t2 = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 6, v: 6|>, + <|k: 7, v: 7|>, + <|k: 8, v: 8|>, + <|k: 9, v: 9|>, + <|k: 10, v: 10|>, + ]) +); + +$t3 = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 11, v: 11|>, + <|k: 12, v: 12|>, + <|k: 13, v: 13|>, + <|k: 14, v: 14|>, + <|k: 15, v: 15|>, + ]) +); + +SELECT + 1 IN ( + SELECT + k + FROM + $t1 + ), + 1 IN ( + ( + SELECT + k + FROM + $t1 + ) + ), + 1 IN ( + ( + ( + SELECT + k + FROM + $t1 + ) + ) + ), + 1 IN ( + SELECT + k + FROM + $t1 + UNION + SELECT + k + FROM + $t2 + ), + 6 IN ( + SELECT + k + FROM + $t1 + UNION + SELECT + k + FROM + $t2 + ), + 1 IN ( + SELECT + k + FROM + $t1 + UNION + ( + SELECT + k + FROM + $t2 + ) + ), + 6 IN ( + SELECT + k + FROM + $t1 + UNION + ( + SELECT + k + FROM + $t2 + ) + ), + 1 IN ( + ( + SELECT + k + FROM + $t1 + ) + UNION + SELECT + k + FROM + $t2 + ), + 6 IN ( + ( + SELECT + k + FROM + $t1 + ) + UNION + SELECT + k + FROM + $t2 + ), + 1 IN ( + ( + SELECT + k + FROM + $t1 + ) + UNION + ( + SELECT + k + FROM + $t2 + ) + ), + 6 IN ( + ( + SELECT + k + FROM + $t1 + ) + UNION + ( + SELECT + k + FROM + $t2 + ) + ), + 1 IN ( + SELECT + k + FROM + $t1 + UNION + SELECT + k + FROM + $t2 + UNION + SELECT + k + FROM + $t3 + ), + 6 IN ( + SELECT + k + FROM + $t1 + UNION + SELECT + k + FROM + $t2 + UNION + SELECT + k + FROM + $t3 + ), + 11 IN ( + SELECT + k + FROM + $t1 + UNION + SELECT + k + FROM + $t2 + UNION + SELECT + k + FROM + $t3 + ), +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_/formatted.sql new file mode 100644 index 00000000000..1fae3b6f54f --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_/formatted.sql @@ -0,0 +1,51 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +SELECT + * +FROM + $input +WHERE + v < ( + SELECT + 3 + ) +; + +SELECT + * +FROM + $input +WHERE + v < ( + SELECT + Avg(v) + FROM + $input + ) +; + +SELECT + * +FROM + $input +WHERE + v < ( + SELECT + Avg(v) + FROM + $input + WHERE + v == 3 + ) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_named_node_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_named_node_/formatted.sql new file mode 100644 index 00000000000..56aaaebf0af --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_subselect-where_named_node_/formatted.sql @@ -0,0 +1,43 @@ +$input = ( + SELECT + * + FROM + AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, + ]) +); + +$x = ( + SELECT + Avg(v) + FROM + $input +); + +SELECT + * +FROM + $input +WHERE + v < $x +; + +SELECT + * +FROM + $input +WHERE + v < ($x) +; + +SELECT + * +FROM + $input +WHERE + v < (($x)) +; diff --git a/yql/essentials/tests/sql/suites/select_op/select_op_precedence_named_node.yql b/yql/essentials/tests/sql/suites/select_op/select_op_precedence_named_node.yql new file mode 100644 index 00000000000..06b36ee3b3f --- /dev/null +++ b/yql/essentials/tests/sql/suites/select_op/select_op_precedence_named_node.yql @@ -0,0 +1,34 @@ +$t1 = ( + (SELECT * FROM (VALUES (1), (2)) AS t (x)) +UNION + (SELECT * FROM (VALUES (2), (3)) AS t (x)) + INTERSECT + (SELECT * FROM (VALUES (3)) AS t (x)) +); + + +$t2 = ( +(SELECT * FROM (VALUES (3)) AS t (x)) +UNION +(SELECT * FROM (VALUES (2)) AS t (x)) +EXCEPT +(SELECT * FROM (VALUES (3)) AS t (x)) +); + +$t3 = ( + (SELECT * FROM (VALUES (1)) AS t (x)) +UNION + (SELECT * FROM (VALUES (2)) AS t (x)) + INTERSECT + (SELECT * FROM (VALUES (2), (3)) AS t (x)) +EXCEPT + (SELECT * FROM (VALUES (3)) AS t (x)) +UNION + (SELECT * FROM (VALUES (4), (3)) AS t (x)) +EXCEPT + (SELECT * FROM (VALUES (4)) AS t (x)) +); + +SELECT * FROM $t1; +SELECT * FROM $t2; +SELECT * FROM $t3; diff --git a/yql/essentials/tests/sql/suites/subselect/aggregate.yql b/yql/essentials/tests/sql/suites/subselect/aggregate.yql new file mode 100644 index 00000000000..50f529522f3 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/aggregate.yql @@ -0,0 +1,15 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +SELECT Sum(3) AS x FROM $input; + +SELECT Sum((SELECT 3)) AS x FROM $input; + +SELECT Sum((SELECT Avg(v) FROM $input)) AS x FROM $input; + +SELECT Sum((SELECT Avg(v) FROM $input WHERE v == 3)) AS x FROM $input; diff --git a/yql/essentials/tests/sql/suites/subselect/aggregate_over.yql b/yql/essentials/tests/sql/suites/subselect/aggregate_over.yql new file mode 100644 index 00000000000..7e81e3a08f0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/aggregate_over.yql @@ -0,0 +1,19 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +SELECT Sum(3) OVER w AS x FROM $input +WINDOW w AS (PARTITION BY 3 ORDER BY k); + +SELECT Sum((SELECT 3)) OVER w AS x FROM $input +WINDOW w AS (PARTITION BY 3 ORDER BY k); + +SELECT Sum((SELECT Avg(v) FROM $input)) OVER w AS x FROM $input +WINDOW w AS (PARTITION BY 3 ORDER BY k); + +SELECT Sum((SELECT Avg(v) FROM $input WHERE v == 3)) OVER w AS x FROM $input +WINDOW w AS (PARTITION BY 3 ORDER BY k); diff --git a/yql/essentials/tests/sql/suites/subselect/default.cfg b/yql/essentials/tests/sql/suites/subselect/default.cfg new file mode 100644 index 00000000000..617474f8d63 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/default.cfg @@ -0,0 +1 @@ +langver 2025.04 diff --git a/yql/essentials/tests/sql/suites/subselect/flatten_by.yql b/yql/essentials/tests/sql/suites/subselect/flatten_by.yql new file mode 100644 index 00000000000..e38cefffb2e --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/flatten_by.yql @@ -0,0 +1,15 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 1, v: 2|>, + <|k: 1, v: 3|>, + <|k: 1, v: 4|>, + <|k: 1, v: 5|>, +]); + +SELECT * FROM $input FLATTEN BY (ListFromRange(1, 3) AS x); + +SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT 3)) AS x); + +SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input)) AS x); + +SELECT * FROM $input FLATTEN BY (ListFromRange(1, (SELECT Avg(v) FROM $input WHERE v == 3)) AS x); diff --git a/yql/essentials/tests/sql/suites/subselect/group_by_no_source.sqlx b/yql/essentials/tests/sql/suites/subselect/group_by_no_source.sqlx new file mode 100644 index 00000000000..ceb278835b2 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/group_by_no_source.sqlx @@ -0,0 +1,9 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, <|k: 2, v: 1|>, + <|k: 1, v: 2|>, <|k: 2, v: 2|>, + <|k: 1, v: 3|>, <|k: 2, v: 3|>, + <|k: 1, v: 4|>, <|k: 2, v: 4|>, + <|k: 1, v: 5|>, <|k: 2, v: 5|>, +]); + +SELECT k, Avg(v) FROM $input GROUP BY (SELECT 3) AS k; diff --git a/yql/essentials/tests/sql/suites/subselect/group_by_source.sqlx b/yql/essentials/tests/sql/suites/subselect/group_by_source.sqlx new file mode 100644 index 00000000000..021948f79b1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/group_by_source.sqlx @@ -0,0 +1,9 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, <|k: 2, v: 1|>, + <|k: 1, v: 2|>, <|k: 2, v: 2|>, + <|k: 1, v: 3|>, <|k: 2, v: 3|>, + <|k: 1, v: 4|>, <|k: 2, v: 4|>, + <|k: 1, v: 5|>, <|k: 2, v: 5|>, +]); + +SELECT k, Avg(v) FROM $input GROUP BY (SELECT Avg(v) FROM $input) AS k; diff --git a/yql/essentials/tests/sql/suites/subselect/group_by_source_filter.sqlx b/yql/essentials/tests/sql/suites/subselect/group_by_source_filter.sqlx new file mode 100644 index 00000000000..20fe7ca9696 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/group_by_source_filter.sqlx @@ -0,0 +1,9 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, <|k: 2, v: 1|>, + <|k: 1, v: 2|>, <|k: 2, v: 2|>, + <|k: 1, v: 3|>, <|k: 2, v: 3|>, + <|k: 1, v: 4|>, <|k: 2, v: 4|>, + <|k: 1, v: 5|>, <|k: 2, v: 5|>, +]); + +SELECT k, Avg(v) FROM $input GROUP BY (SELECT Avg(v) FROM $input WHERE v == 3) AS k; diff --git a/yql/essentials/tests/sql/suites/subselect/having.yql b/yql/essentials/tests/sql/suites/subselect/having.yql new file mode 100644 index 00000000000..a041651aa6e --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/having.yql @@ -0,0 +1,13 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, <|k: 2, v: 1|>, + <|k: 1, v: 2|>, <|k: 2, v: 2|>, + <|k: 1, v: 3|>, <|k: 2, v: 3|>, + <|k: 1, v: 4|>, <|k: 2, v: 4|>, + <|k: 1, v: 5|>, <|k: 2, v: 5|>, +]); + +SELECT k, Avg(v) FROM $input GROUP BY k HAVING Avg(v) == (SELECT 3); + +SELECT k, Avg(v) FROM $input GROUP BY k HAVING Avg(v) == (SELECT Avg(v) FROM $input); + +SELECT k, Avg(v) FROM $input GROUP BY k HAVING Avg(v) == (SELECT Avg(v) FROM $input WHERE v == 3); diff --git a/yql/essentials/tests/sql/suites/subselect/limit.yql b/yql/essentials/tests/sql/suites/subselect/limit.yql new file mode 100644 index 00000000000..ae8c84cc068 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/limit.yql @@ -0,0 +1,12 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +SELECT * FROM $input LIMIT CAST(3 AS UInt64); +SELECT * FROM $input LIMIT CAST((SELECT 3) AS UInt64); +SELECT * FROM $input LIMIT CAST((SELECT Avg(v) FROM $input) AS UInt64); +SELECT * FROM $input LIMIT CAST((SELECT Avg(v) FROM $input WHERE v == 3) AS UInt64); diff --git a/yql/essentials/tests/sql/suites/subselect/offset.yql b/yql/essentials/tests/sql/suites/subselect/offset.yql new file mode 100644 index 00000000000..9a6dd2300f9 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/offset.yql @@ -0,0 +1,15 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +SELECT * FROM $input ORDER BY k LIMIT NULL OFFSET CAST(3 AS Uint64); + +SELECT * FROM $input ORDER BY k LIMIT NULL OFFSET CAST((SELECT 3) AS Uint64); + +SELECT * FROM $input ORDER BY k LIMIT NULL OFFSET CAST((SELECT Avg(v) FROM $input) AS Uint64); + +SELECT * FROM $input ORDER BY k LIMIT NULL OFFSET CAST((SELECT Avg(v) FROM $input WHERE v == 3) AS Uint64); diff --git a/yql/essentials/tests/sql/suites/subselect/order_by_no_source.sqlx b/yql/essentials/tests/sql/suites/subselect/order_by_no_source.sqlx new file mode 100644 index 00000000000..cd3e4824fd7 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/order_by_no_source.sqlx @@ -0,0 +1,9 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 1, v: 2|>, + <|k: 1, v: 3|>, + <|k: 1, v: 4|>, + <|k: 1, v: 5|>, +]); + +SELECT * FROM $input ORDER BY (SELECT 3), k, v; diff --git a/yql/essentials/tests/sql/suites/subselect/order_by_source.sqlx b/yql/essentials/tests/sql/suites/subselect/order_by_source.sqlx new file mode 100644 index 00000000000..9f88c26badd --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/order_by_source.sqlx @@ -0,0 +1,9 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 1, v: 2|>, + <|k: 1, v: 3|>, + <|k: 1, v: 4|>, + <|k: 1, v: 5|>, +]); + +SELECT * FROM $input ORDER BY (SELECT Avg(v) FROM $input), k, v; diff --git a/yql/essentials/tests/sql/suites/subselect/order_by_source_filter.sqlx b/yql/essentials/tests/sql/suites/subselect/order_by_source_filter.sqlx new file mode 100644 index 00000000000..44aef1291f0 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/order_by_source_filter.sqlx @@ -0,0 +1,9 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 1, v: 2|>, + <|k: 1, v: 3|>, + <|k: 1, v: 4|>, + <|k: 1, v: 5|>, +]); + +SELECT * FROM $input ORDER BY (SELECT Avg(v) FROM $input WHERE v == 3), k, v; diff --git a/yql/essentials/tests/sql/suites/subselect/partition_by.yql b/yql/essentials/tests/sql/suites/subselect/partition_by.yql new file mode 100644 index 00000000000..2e228bb2ccc --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/partition_by.yql @@ -0,0 +1,32 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +SELECT Count(*) OVER w AS x FROM $input +WINDOW w AS ( + PARTITION BY 3 + ORDER BY k +); + +SELECT Count(*) OVER w AS x FROM $input +WINDOW w AS ( + PARTITION BY (SELECT 3) + ORDER BY k +); + +SELECT Count(*) OVER w AS x FROM $input +WINDOW w AS ( + PARTITION BY (SELECT Avg(v) FROM $input) + ORDER BY k +); + +SELECT Count(*) OVER w AS x FROM $input +WINDOW w AS ( + PARTITION BY (SELECT Avg(v) FROM $input WHERE v == 3) + ORDER BY k +); + diff --git a/yql/essentials/tests/sql/suites/subselect/projection.yql b/yql/essentials/tests/sql/suites/subselect/projection.yql new file mode 100644 index 00000000000..f7012ec923e --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/projection.yql @@ -0,0 +1,13 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +SELECT (SELECT 3) AS x; + +SELECT (SELECT Avg(v) FROM $input) AS x; + +SELECT (SELECT Avg(v) FROM $input WHERE v == 3) AS x; diff --git a/yql/essentials/tests/sql/suites/subselect/union.yql b/yql/essentials/tests/sql/suites/subselect/union.yql new file mode 100644 index 00000000000..7b11993a149 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/union.yql @@ -0,0 +1,42 @@ +$t1 = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +$t2 = SELECT * FROM AS_TABLE([ + <|k: 6, v: 6|>, + <|k: 7, v: 7|>, + <|k: 8, v: 8|>, + <|k: 9, v: 9|>, + <|k: 10, v: 10|>, +]); + +$t3 = SELECT * FROM AS_TABLE([ + <|k: 11, v: 11|>, + <|k: 12, v: 12|>, + <|k: 13, v: 13|>, + <|k: 14, v: 14|>, + <|k: 15, v: 15|>, +]); + +SELECT + 1 IN ( SELECT k FROM $t1 ), + 1 IN (( SELECT k FROM $t1 )), + 1 IN (((SELECT k FROM $t1))), + + 1 IN ( SELECT k FROM $t1 UNION SELECT k FROM $t2 ), + 6 IN ( SELECT k FROM $t1 UNION SELECT k FROM $t2 ), + 1 IN ( SELECT k FROM $t1 UNION (SELECT k FROM $t2)), + 6 IN ( SELECT k FROM $t1 UNION (SELECT k FROM $t2)), + 1 IN ((SELECT k FROM $t1) UNION SELECT k FROM $t2 ), + 6 IN ((SELECT k FROM $t1) UNION SELECT k FROM $t2 ), + 1 IN ((SELECT k FROM $t1) UNION (SELECT k FROM $t2)), + 6 IN ((SELECT k FROM $t1) UNION (SELECT k FROM $t2)), + + 1 IN (SELECT k FROM $t1 UNION SELECT k FROM $t2 UNION SELECT k FROM $t3), + 6 IN (SELECT k FROM $t1 UNION SELECT k FROM $t2 UNION SELECT k FROM $t3), + 11 IN (SELECT k FROM $t1 UNION SELECT k FROM $t2 UNION SELECT k FROM $t3), +; diff --git a/yql/essentials/tests/sql/suites/subselect/where.yql b/yql/essentials/tests/sql/suites/subselect/where.yql new file mode 100644 index 00000000000..01a51f00240 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/where.yql @@ -0,0 +1,13 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +SELECT * FROM $input WHERE v < (SELECT 3); + +SELECT * FROM $input WHERE v < (SELECT Avg(v) FROM $input); + +SELECT * FROM $input WHERE v < (SELECT Avg(v) FROM $input WHERE v == 3); diff --git a/yql/essentials/tests/sql/suites/subselect/where_named_node.yql b/yql/essentials/tests/sql/suites/subselect/where_named_node.yql new file mode 100644 index 00000000000..bb607a82cb1 --- /dev/null +++ b/yql/essentials/tests/sql/suites/subselect/where_named_node.yql @@ -0,0 +1,13 @@ +$input = SELECT * FROM AS_TABLE([ + <|k: 1, v: 1|>, + <|k: 2, v: 2|>, + <|k: 3, v: 3|>, + <|k: 4, v: 4|>, + <|k: 5, v: 5|>, +]); + +$x = SELECT Avg(v) FROM $input; + +SELECT * FROM $input WHERE v < $x; +SELECT * FROM $input WHERE v < ($x); +SELECT * FROM $input WHERE v < (($x)); |