diff options
author | kndrvt <[email protected]> | 2025-06-20 15:36:52 +0300 |
---|---|---|
committer | kndrvt <[email protected]> | 2025-06-20 16:07:13 +0300 |
commit | 935e53d2a9ee4b43cc0548ba836b23c003c8078e (patch) | |
tree | 99fa1a3b091548f8f2d974f443b45847b9e66a1b /yql/essentials/tests | |
parent | 532fb55c30629ecb7b2720b02d446ea89ac76917 (diff) |
YQL-17269: support INTERSECT and EXCEPT without PositionalUnionAll
commit_hash:632e24794e8bcf6ef0502b7e8c031e964d28d36a
Diffstat (limited to 'yql/essentials/tests')
26 files changed, 885 insertions, 10 deletions
diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json index 021ebed645a..27aab7bf175 100644 --- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json @@ -426,6 +426,20 @@ "uri": "https://{canondata_backend}/1903885/f19a3c78c5968d4ae7992179e15806b818abce10/resource.tar.gz#test.test_datetime-date_convert-default.txt-Results_/results.txt" } ], + "test.test[except-except_all-default.txt-Debug]": [ + { + "checksum": "1bdaf8e6cdeeba7ccdbec393da4facda", + "size": 2108, + "uri": "https://{canondata_backend}/1881367/453c70881991a95154014d718c871d0afdcbf15e/resource.tar.gz#test.test_except-except_all-default.txt-Debug_/opt.yql" + } + ], + "test.test[except-except_all-default.txt-Results]": [ + { + "checksum": "1c2de5c252530ae81b0c46bd53f8ef65", + "size": 3437, + "uri": "https://{canondata_backend}/1881367/453c70881991a95154014d718c871d0afdcbf15e/resource.tar.gz#test.test_except-except_all-default.txt-Results_/results.txt" + } + ], "test.test[expr-as_table_emptylist2-default.txt-Debug]": [ { "checksum": "6424bb71484d775e2e9f4abcc3b852e6", diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/result.json b/yql/essentials/tests/sql/minirun/part2/canondata/result.json index 9ab45a645fc..28b1ecec99c 100644 --- a/yql/essentials/tests/sql/minirun/part2/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part2/canondata/result.json @@ -461,6 +461,20 @@ "uri": "https://{canondata_backend}/1917492/e1446481f72d95d231bd730cb8c596af631640ea/resource.tar.gz#test.test_datetime-date_types-default.txt-Results_/results.txt" } ], + "test.test[except-except_all_empty-default.txt-Debug]": [ + { + "checksum": "e46b9bf056a427b8de96acf38e6fdc56", + "size": 348, + "uri": "https://{canondata_backend}/1942278/736d75cf62b2e931435e1042d99607804c1fe8a7/resource.tar.gz#test.test_except-except_all_empty-default.txt-Debug_/opt.yql" + } + ], + "test.test[except-except_all_empty-default.txt-Results]": [ + { + "checksum": "d2e2d092c3bb9360e458b8d8e66baa1d", + "size": 1474, + "uri": "https://{canondata_backend}/1942278/736d75cf62b2e931435e1042d99607804c1fe8a7/resource.tar.gz#test.test_except-except_all_empty-default.txt-Results_/results.txt" + } + ], "test.test[expr-cast_dynumber-default.txt-Debug]": [ { "checksum": "114987035ac6e54e07a57e3019c0e192", diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json index baf4cd34051..a62c1531662 100644 --- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json @@ -704,6 +704,20 @@ "uri": "https://{canondata_backend}/1936273/60c880bb0a569b92424a6d5d2554b715fd342146/resource.tar.gz#test.test_in-in_with_tuple_simple-default.txt-Results_/results.txt" } ], + "test.test[intersect-intersect_distinct_empty-default.txt-Debug]": [ + { + "checksum": "de3495bdc43ec7707722e1b9585237d0", + "size": 298, + "uri": "https://{canondata_backend}/1917492/587e362e11daf3cf997a345b018213c9e8643313/resource.tar.gz#test.test_intersect-intersect_distinct_empty-default.txt-Debug_/opt.yql" + } + ], + "test.test[intersect-intersect_distinct_empty-default.txt-Results]": [ + { + "checksum": "36e87fce680f9d5c30c1e656e6be934b", + "size": 749, + "uri": "https://{canondata_backend}/1917492/587e362e11daf3cf997a345b018213c9e8643313/resource.tar.gz#test.test_intersect-intersect_distinct_empty-default.txt-Results_/results.txt" + } + ], "test.test[join-convert_check_key_mem-default.txt-Debug]": [ { "checksum": "98813e43dffe60bd82a78af8e4a992ba", diff --git a/yql/essentials/tests/sql/minirun/part4/canondata/result.json b/yql/essentials/tests/sql/minirun/part4/canondata/result.json index 214ceb66734..653bc9a7089 100644 --- a/yql/essentials/tests/sql/minirun/part4/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part4/canondata/result.json @@ -814,6 +814,20 @@ "uri": "https://{canondata_backend}/1847551/2d49108793659545d13c6e0b843e2c017302ad43/resource.tar.gz#test.test_in-in_with_nulls_and_optionals_extra-default.txt-Results_/results.txt" } ], + "test.test[intersect-intersect_all_empty-default.txt-Debug]": [ + { + "checksum": "de3495bdc43ec7707722e1b9585237d0", + "size": 298, + "uri": "https://{canondata_backend}/1937150/92cf5fc8848ad59407c2744be1538c731bcb8de4/resource.tar.gz#test.test_intersect-intersect_all_empty-default.txt-Debug_/opt.yql" + } + ], + "test.test[intersect-intersect_all_empty-default.txt-Results]": [ + { + "checksum": "36e87fce680f9d5c30c1e656e6be934b", + "size": 749, + "uri": "https://{canondata_backend}/1937150/92cf5fc8848ad59407c2744be1538c731bcb8de4/resource.tar.gz#test.test_intersect-intersect_all_empty-default.txt-Results_/results.txt" + } + ], "test.test[join-cross_join_with_lazy_list-default.txt-Debug]": [ { "checksum": "8687ef86fa2f673b9760d9595f475ece", diff --git a/yql/essentials/tests/sql/minirun/part6/canondata/result.json b/yql/essentials/tests/sql/minirun/part6/canondata/result.json index b31a0804b64..c5d1ec58cb3 100644 --- a/yql/essentials/tests/sql/minirun/part6/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part6/canondata/result.json @@ -558,6 +558,20 @@ "uri": "https://{canondata_backend}/1775059/8f98a745728bc42ccc40bc5c65b23d499853793a/resource.tar.gz#test.test_distinct-distinct_star_inmem-default.txt-Results_/results.txt" } ], + "test.test[except-except_distinct_empty-default.txt-Debug]": [ + { + "checksum": "e46b9bf056a427b8de96acf38e6fdc56", + "size": 348, + "uri": "https://{canondata_backend}/1942278/fc503167a0e9dd2e6465bc29c2b2299043a83a6c/resource.tar.gz#test.test_except-except_distinct_empty-default.txt-Debug_/opt.yql" + } + ], + "test.test[except-except_distinct_empty-default.txt-Results]": [ + { + "checksum": "d2e2d092c3bb9360e458b8d8e66baa1d", + "size": 1474, + "uri": "https://{canondata_backend}/1942278/fc503167a0e9dd2e6465bc29c2b2299043a83a6c/resource.tar.gz#test.test_except-except_distinct_empty-default.txt-Results_/results.txt" + } + ], "test.test[expr-as_table-default.txt-Debug]": [ { "checksum": "8799d51aa9290bc664b44e0945b559da", @@ -824,6 +838,20 @@ "uri": "https://{canondata_backend}/1031349/7264f379f5ed6029cd16e136c979509c14985388/resource.tar.gz#test.test_in-large_in_YQL-19183-ansi-Results_/results.txt" } ], + "test.test[intersect-intersect_distinct-default.txt-Debug]": [ + { + "checksum": "1528f6b2213ece6a1f4a8f32465e8dea", + "size": 2226, + "uri": "https://{canondata_backend}/1937424/51d6348179b23fc8977fc8de7b08323f6c5603f5/resource.tar.gz#test.test_intersect-intersect_distinct-default.txt-Debug_/opt.yql" + } + ], + "test.test[intersect-intersect_distinct-default.txt-Results]": [ + { + "checksum": "3f4b92b2d6a3a7a9f18273430118c7b2", + "size": 2617, + "uri": "https://{canondata_backend}/1937424/51d6348179b23fc8977fc8de7b08323f6c5603f5/resource.tar.gz#test.test_intersect-intersect_distinct-default.txt-Results_/results.txt" + } + ], "test.test[json-combination/nested-default.txt-Debug]": [ { "checksum": "31d64ac4278d43e44b6d9c979b636ce6", diff --git a/yql/essentials/tests/sql/minirun/part8/canondata/result.json b/yql/essentials/tests/sql/minirun/part8/canondata/result.json index b47658a25bd..45f139b3e50 100644 --- a/yql/essentials/tests/sql/minirun/part8/canondata/result.json +++ b/yql/essentials/tests/sql/minirun/part8/canondata/result.json @@ -639,6 +639,20 @@ "uri": "https://{canondata_backend}/1903885/a240e9eb95f33f5b5688d6770bbb18aa5d19778a/resource.tar.gz#test.test_datetime-date_tz_scale-default.txt-Results_/results.txt" } ], + "test.test[except-except_distinct-default.txt-Debug]": [ + { + "checksum": "f0505afaff2f21cf0f9bdab5842fd369", + "size": 2112, + "uri": "https://{canondata_backend}/1942278/3c1745dc69d236a46dcb2ea6952e67da86aa3e9d/resource.tar.gz#test.test_except-except_distinct-default.txt-Debug_/opt.yql" + } + ], + "test.test[except-except_distinct-default.txt-Results]": [ + { + "checksum": "35ad547f6b9d1506b0e4a5ec10f52154", + "size": 2929, + "uri": "https://{canondata_backend}/1942278/3c1745dc69d236a46dcb2ea6952e67da86aa3e9d/resource.tar.gz#test.test_except-except_distinct-default.txt-Results_/results.txt" + } + ], "test.test[expr-cast_longint-default.txt-Debug]": [ { "checksum": "852e514a4bf927cd6c59acf27e3cc716", @@ -964,6 +978,20 @@ "uri": "https://{canondata_backend}/1871182/8488be1009a783ec149801679b1d381d33cbeb2f/resource.tar.gz#test.test_in-YQL-18950-default.txt-Results_/results.txt" } ], + "test.test[intersect-intersect_all-default.txt-Debug]": [ + { + "checksum": "4d081baeeaa6382eb5b8cde859c96164", + "size": 2068, + "uri": "https://{canondata_backend}/1775059/2b5e7aa24664d04b90cf055001d40e858a3f4532/resource.tar.gz#test.test_intersect-intersect_all-default.txt-Debug_/opt.yql" + } + ], + "test.test[intersect-intersect_all-default.txt-Results]": [ + { + "checksum": "cbcec9c2bbeaa07a3789ddff3f12ad54", + "size": 3125, + "uri": "https://{canondata_backend}/1775059/2b5e7aa24664d04b90cf055001d40e858a3f4532/resource.tar.gz#test.test_intersect-intersect_all-default.txt-Results_/results.txt" + } + ], "test.test[join-left_join_predicate_push_parent_cross_join-default.txt-Debug]": [ { "checksum": "23ca906191920493283354f7d5513c7a", diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json index 2ddd8201e80..d533cfec5ce 100644 --- a/yql/essentials/tests/sql/sql2yql/canondata/result.json +++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json @@ -953,9 +953,9 @@ ], "test_sql2yql.test[aggregate-group_by_expr_after_where_ver]": [ { - "checksum": "d7d81aab522ef18bb07e619c426594c5", + "checksum": "4799645fef77850f5f5f07de2d1b8bc2", "size": 1762, - "uri": "https://{canondata_backend}/1597364/99b2cf59a9975dbc2994ead01aa9dcbd784b5279/resource.tar.gz#test_sql2yql.test_aggregate-group_by_expr_after_where_ver_/sql.yql" + "uri": "https://{canondata_backend}/1937027/20da4ae4ed8ef86446356b94288d2f22b76637b5/resource.tar.gz#test_sql2yql.test_aggregate-group_by_expr_after_where_ver_/sql.yql" } ], "test_sql2yql.test[aggregate-group_by_rollup_rename]": [ @@ -2337,6 +2337,34 @@ "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_distinct-distinct_star_inmem_/sql.yql" } ], + "test_sql2yql.test[except-except_all]": [ + { + "checksum": "432071f0d21d01d9bd0b792c77db417e", + "size": 3566, + "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_all_/sql.yql" + } + ], + "test_sql2yql.test[except-except_all_empty]": [ + { + "checksum": "40a32c1c8fb5a979dea406ffe7963eeb", + "size": 4163, + "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_all_empty_/sql.yql" + } + ], + "test_sql2yql.test[except-except_distinct]": [ + { + "checksum": "a73b03f897702173faee14fdea46dd85", + "size": 3560, + "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_distinct_/sql.yql" + } + ], + "test_sql2yql.test[except-except_distinct_empty]": [ + { + "checksum": "601b8022980473e8eafdf9365597cf1e", + "size": 4154, + "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_distinct_empty_/sql.yql" + } + ], "test_sql2yql.test[expr-as_dict_dict_key]": [ { "checksum": "9dd554b71081bd23a148f6d34f15d7ca", @@ -3891,6 +3919,34 @@ "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_in-small_in_YQL-19183_/sql.yql" } ], + "test_sql2yql.test[intersect-intersect_all]": [ + { + "checksum": "24cbce9982a475e5a7cb746228ef18f7", + "size": 3572, + "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_all_/sql.yql" + } + ], + "test_sql2yql.test[intersect-intersect_all_empty]": [ + { + "checksum": "6ea841d5596fd7d558edf69fe1107a25", + "size": 4172, + "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_all_empty_/sql.yql" + } + ], + "test_sql2yql.test[intersect-intersect_distinct]": [ + { + "checksum": "47b396e6add2c3a8d1e2efc3015a261f", + "size": 3566, + "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_distinct_/sql.yql" + } + ], + "test_sql2yql.test[intersect-intersect_distinct_empty]": [ + { + "checksum": "8d722656304be05a941f2adfd4f32405", + "size": 4163, + "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_distinct_empty_/sql.yql" + } + ], "test_sql2yql.test[join-convert_check_key_mem2]": [ { "checksum": "e1f4ff23a4f2c04036a52d4a5b25529c", @@ -4010,13 +4066,6 @@ "uri": "https://{canondata_backend}/1942525/94a477066ea16f69d4848bbe524485fc029978b8/resource.tar.gz#test_sql2yql.test_join-prune_keys_YQL-19979_/sql.yql" } ], - "test_sql2yql.test[join-prune_keys_YQL-19979]": [ - { - "checksum": "0dad5d395f90148805e893a30f0b4963", - "size": 3845, - "uri": "https://{canondata_backend}/1942525/94a477066ea16f69d4848bbe524485fc029978b8/resource.tar.gz#test_sql2yql.test_join-prune_keys_YQL-19979_/sql.yql" - } - ], "test_sql2yql.test[join-yql-19192]": [ { "checksum": "fffdf1cbb40643da9daf9bdf3edec121", @@ -9222,6 +9271,26 @@ "uri": "file://test_sql_format.test_distinct-distinct_star_inmem_/formatted.sql" } ], + "test_sql_format.test[except-except_all]": [ + { + "uri": "file://test_sql_format.test_except-except_all_/formatted.sql" + } + ], + "test_sql_format.test[except-except_all_empty]": [ + { + "uri": "file://test_sql_format.test_except-except_all_empty_/formatted.sql" + } + ], + "test_sql_format.test[except-except_distinct]": [ + { + "uri": "file://test_sql_format.test_except-except_distinct_/formatted.sql" + } + ], + "test_sql_format.test[except-except_distinct_empty]": [ + { + "uri": "file://test_sql_format.test_except-except_distinct_empty_/formatted.sql" + } + ], "test_sql_format.test[expr-as_dict_dict_key]": [ { "uri": "file://test_sql_format.test_expr-as_dict_dict_key_/formatted.sql" @@ -10332,6 +10401,26 @@ "uri": "file://test_sql_format.test_in-small_in_YQL-19183_/formatted.sql" } ], + "test_sql_format.test[intersect-intersect_all]": [ + { + "uri": "file://test_sql_format.test_intersect-intersect_all_/formatted.sql" + } + ], + "test_sql_format.test[intersect-intersect_all_empty]": [ + { + "uri": "file://test_sql_format.test_intersect-intersect_all_empty_/formatted.sql" + } + ], + "test_sql_format.test[intersect-intersect_distinct]": [ + { + "uri": "file://test_sql_format.test_intersect-intersect_distinct_/formatted.sql" + } + ], + "test_sql_format.test[intersect-intersect_distinct_empty]": [ + { + "uri": "file://test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql" + } + ], "test_sql_format.test[join-convert_check_key_mem2]": [ { "uri": "file://test_sql_format.test_join-convert_check_key_mem2_/formatted.sql" diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql new file mode 100644 index 00000000000..917e3d4a593 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql @@ -0,0 +1,51 @@ +SELECT + * +FROM ( + VALUES + (1), + (1), + (1), + (2), + (NULL), + (NULL), + (NULL) +) AS t ( + x +) +EXCEPT ALL +SELECT + * +FROM ( + VALUES + (1), + (NULL) +) AS t ( + x +); + +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (1, NULL), + (NULL, 2), + (NULL, NULL), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + y +) +EXCEPT ALL +SELECT + * +FROM ( + VALUES + (1, NULL), + (NULL, NULL) +) AS t ( + x, + z +); diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql new file mode 100644 index 00000000000..3a516c473a8 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql @@ -0,0 +1,30 @@ +SELECT + * +FROM + as_table([]) +EXCEPT ALL +SELECT + * +FROM + as_table([]) +; + +SELECT + * +FROM + as_table([]) +EXCEPT ALL +SELECT + 1 AS x, + 2 AS y +; + +SELECT + 1 AS x, + 2 AS y +EXCEPT ALL +SELECT + * +FROM + as_table([]) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql new file mode 100644 index 00000000000..f1aacddbb1c --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql @@ -0,0 +1,51 @@ +SELECT + * +FROM ( + VALUES + (1), + (1), + (1), + (2), + (NULL), + (NULL), + (NULL) +) AS t ( + x +) +EXCEPT DISTINCT +SELECT + * +FROM ( + VALUES + (1), + (NULL) +) AS t ( + x +); + +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (1, NULL), + (NULL, 2), + (NULL, NULL), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + y +) +EXCEPT DISTINCT +SELECT + * +FROM ( + VALUES + (1, NULL), + (NULL, NULL) +) AS t ( + x, + z +); diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql new file mode 100644 index 00000000000..d5ffcca0bcf --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql @@ -0,0 +1,30 @@ +SELECT + * +FROM + as_table([]) +EXCEPT DISTINCT +SELECT + * +FROM + as_table([]) +; + +SELECT + * +FROM + as_table([]) +EXCEPT DISTINCT +SELECT + 1 AS x, + 2 AS y +; + +SELECT + 1 AS x, + 2 AS y +EXCEPT DISTINCT +SELECT + * +FROM + as_table([]) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql new file mode 100644 index 00000000000..a76f042e494 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql @@ -0,0 +1,51 @@ +SELECT + * +FROM ( + VALUES + (1), + (1), + (2), + (NULL), + (NULL) +) AS t ( + x +) +INTERSECT ALL +SELECT + * +FROM ( + VALUES + (1), + (1), + (NULL), + (NULL) +) AS t ( + x +); + +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (NULL, 2), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + y +) +INTERSECT ALL +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + z +); diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql new file mode 100644 index 00000000000..c89a93a9243 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql @@ -0,0 +1,30 @@ +SELECT + * +FROM + as_table([]) +INTERSECT ALL +SELECT + * +FROM + as_table([]) +; + +SELECT + * +FROM + as_table([]) +INTERSECT ALL +SELECT + 1 AS x, + 2 AS y +; + +SELECT + 1 AS x, + 2 AS y +INTERSECT ALL +SELECT + * +FROM + as_table([]) +; diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql new file mode 100644 index 00000000000..5b7d85a1f46 --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql @@ -0,0 +1,51 @@ +SELECT + * +FROM ( + VALUES + (1), + (1), + (2), + (NULL), + (NULL) +) AS t ( + x +) +INTERSECT DISTINCT +SELECT + * +FROM ( + VALUES + (1), + (1), + (NULL), + (NULL) +) AS t ( + x +); + +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (NULL, 2), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + y +) +INTERSECT DISTINCT +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + z +); diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql new file mode 100644 index 00000000000..16623be2f3f --- /dev/null +++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql @@ -0,0 +1,30 @@ +SELECT + * +FROM + as_table([]) +INTERSECT DISTINCT +SELECT + * +FROM + as_table([]) +; + +SELECT + * +FROM + as_table([]) +INTERSECT DISTINCT +SELECT + 1 AS x, + 2 AS y +; + +SELECT + 1 AS x, + 2 AS y +INTERSECT DISTINCT +SELECT + * +FROM + as_table([]) +; diff --git a/yql/essentials/tests/sql/sql2yql/test_sql2yql.py b/yql/essentials/tests/sql/sql2yql/test_sql2yql.py index 924d5f4181d..c8f5bbcd845 100644 --- a/yql/essentials/tests/sql/sql2yql/test_sql2yql.py +++ b/yql/essentials/tests/sql/sql2yql/test_sql2yql.py @@ -2,8 +2,11 @@ import os import yatest.common -from test_utils import pytest_generate_tests_by_template, SQLRUN_PATH, SQL_FLAGS +from test_utils import get_config, pytest_generate_tests_by_template, SQLRUN_PATH, SQL_FLAGS +from yql_utils import get_langver + +DEFAULT_LANG_VER = '2025.01' DATA_PATH = yatest.common.source_path('yql/essentials/tests/sql/suites') @@ -11,6 +14,25 @@ def pytest_generate_tests(metafunc): return pytest_generate_tests_by_template('.sql', metafunc, data_path=DATA_PATH) +def _get_cfg_path(suite, case, data_path): + cfg_path = os.path.join(data_path, suite, case) + if os.path.exists(cfg_path + '.cfg'): + return "" + else: + return "default.txt" + + +def _get_langver(suite, case, data_path): + cfg_path = _get_cfg_path(suite, case, data_path) + config = get_config(suite, case, cfg_path, data_path=DATA_PATH) + + langver = get_langver(config) + if langver is None: + langver = DEFAULT_LANG_VER + + return langver + + def get_sql2yql_cmd(suite, case, case_file, out_dir, ansi_lexer, test_format, test_double_format): cmd = [ SQLRUN_PATH, @@ -50,6 +72,8 @@ def get_sql2yql_cmd(suite, case, case_file, out_dir, ansi_lexer, test_format, te if SQL_FLAGS: cmd.append('--flags=%s' % ','.join(SQL_FLAGS)) + cmd.append('--langver=%s' % _get_langver(suite, case, data_path=DATA_PATH)) + return cmd diff --git a/yql/essentials/tests/sql/suites/except/default.cfg b/yql/essentials/tests/sql/suites/except/default.cfg new file mode 100644 index 00000000000..cd964fef9e8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/except/default.cfg @@ -0,0 +1 @@ +langver 2025.03 diff --git a/yql/essentials/tests/sql/suites/except/except_all.sql b/yql/essentials/tests/sql/suites/except/except_all.sql new file mode 100644 index 00000000000..917e3d4a593 --- /dev/null +++ b/yql/essentials/tests/sql/suites/except/except_all.sql @@ -0,0 +1,51 @@ +SELECT + * +FROM ( + VALUES + (1), + (1), + (1), + (2), + (NULL), + (NULL), + (NULL) +) AS t ( + x +) +EXCEPT ALL +SELECT + * +FROM ( + VALUES + (1), + (NULL) +) AS t ( + x +); + +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (1, NULL), + (NULL, 2), + (NULL, NULL), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + y +) +EXCEPT ALL +SELECT + * +FROM ( + VALUES + (1, NULL), + (NULL, NULL) +) AS t ( + x, + z +); diff --git a/yql/essentials/tests/sql/suites/except/except_all_empty.sql b/yql/essentials/tests/sql/suites/except/except_all_empty.sql new file mode 100644 index 00000000000..3a516c473a8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/except/except_all_empty.sql @@ -0,0 +1,30 @@ +SELECT + * +FROM + as_table([]) +EXCEPT ALL +SELECT + * +FROM + as_table([]) +; + +SELECT + * +FROM + as_table([]) +EXCEPT ALL +SELECT + 1 AS x, + 2 AS y +; + +SELECT + 1 AS x, + 2 AS y +EXCEPT ALL +SELECT + * +FROM + as_table([]) +; diff --git a/yql/essentials/tests/sql/suites/except/except_distinct.sql b/yql/essentials/tests/sql/suites/except/except_distinct.sql new file mode 100644 index 00000000000..f1aacddbb1c --- /dev/null +++ b/yql/essentials/tests/sql/suites/except/except_distinct.sql @@ -0,0 +1,51 @@ +SELECT + * +FROM ( + VALUES + (1), + (1), + (1), + (2), + (NULL), + (NULL), + (NULL) +) AS t ( + x +) +EXCEPT DISTINCT +SELECT + * +FROM ( + VALUES + (1), + (NULL) +) AS t ( + x +); + +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (1, NULL), + (NULL, 2), + (NULL, NULL), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + y +) +EXCEPT DISTINCT +SELECT + * +FROM ( + VALUES + (1, NULL), + (NULL, NULL) +) AS t ( + x, + z +); diff --git a/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql b/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql new file mode 100644 index 00000000000..d5ffcca0bcf --- /dev/null +++ b/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql @@ -0,0 +1,30 @@ +SELECT + * +FROM + as_table([]) +EXCEPT DISTINCT +SELECT + * +FROM + as_table([]) +; + +SELECT + * +FROM + as_table([]) +EXCEPT DISTINCT +SELECT + 1 AS x, + 2 AS y +; + +SELECT + 1 AS x, + 2 AS y +EXCEPT DISTINCT +SELECT + * +FROM + as_table([]) +; diff --git a/yql/essentials/tests/sql/suites/intersect/default.cfg b/yql/essentials/tests/sql/suites/intersect/default.cfg new file mode 100644 index 00000000000..cd964fef9e8 --- /dev/null +++ b/yql/essentials/tests/sql/suites/intersect/default.cfg @@ -0,0 +1 @@ +langver 2025.03 diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_all.sql b/yql/essentials/tests/sql/suites/intersect/intersect_all.sql new file mode 100644 index 00000000000..a76f042e494 --- /dev/null +++ b/yql/essentials/tests/sql/suites/intersect/intersect_all.sql @@ -0,0 +1,51 @@ +SELECT + * +FROM ( + VALUES + (1), + (1), + (2), + (NULL), + (NULL) +) AS t ( + x +) +INTERSECT ALL +SELECT + * +FROM ( + VALUES + (1), + (1), + (NULL), + (NULL) +) AS t ( + x +); + +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (NULL, 2), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + y +) +INTERSECT ALL +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + z +); diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql b/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql new file mode 100644 index 00000000000..c89a93a9243 --- /dev/null +++ b/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql @@ -0,0 +1,30 @@ +SELECT + * +FROM + as_table([]) +INTERSECT ALL +SELECT + * +FROM + as_table([]) +; + +SELECT + * +FROM + as_table([]) +INTERSECT ALL +SELECT + 1 AS x, + 2 AS y +; + +SELECT + 1 AS x, + 2 AS y +INTERSECT ALL +SELECT + * +FROM + as_table([]) +; diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql b/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql new file mode 100644 index 00000000000..5b7d85a1f46 --- /dev/null +++ b/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql @@ -0,0 +1,51 @@ +SELECT + * +FROM ( + VALUES + (1), + (1), + (2), + (NULL), + (NULL) +) AS t ( + x +) +INTERSECT DISTINCT +SELECT + * +FROM ( + VALUES + (1), + (1), + (NULL), + (NULL) +) AS t ( + x +); + +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (NULL, 2), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + y +) +INTERSECT DISTINCT +SELECT + * +FROM ( + VALUES + (1, NULL), + (1, NULL), + (NULL, NULL), + (NULL, NULL) +) AS t ( + x, + z +); diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql b/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql new file mode 100644 index 00000000000..16623be2f3f --- /dev/null +++ b/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql @@ -0,0 +1,30 @@ +SELECT + * +FROM + as_table([]) +INTERSECT DISTINCT +SELECT + * +FROM + as_table([]) +; + +SELECT + * +FROM + as_table([]) +INTERSECT DISTINCT +SELECT + 1 AS x, + 2 AS y +; + +SELECT + 1 AS x, + 2 AS y +INTERSECT DISTINCT +SELECT + * +FROM + as_table([]) +; |