Merge branch 'rightlib' into mergelibs-241120-1113

25 files changed, 217 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/union_all/default.cfg b/yql/essentials/tests/sql/suites/union_all/default.cfg
new file mode 100644
index 0000000000..58878f8945
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/default.cfg
@@ -0,0 +1 @@
+in Input input.txt
diff --git a/yql/essentials/tests/sql/suites/union_all/infer_3.sql b/yql/essentials/tests/sql/suites/union_all/infer_3.sql
new file mode 100644
index 0000000000..ba01618d8a
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/infer_3.sql
@@ -0,0 +1,8 @@
+/* postgres can not */
+use plato;
+
+select Just(1) as x,1 as y
+union all
+select Just(1l) as x, 2 as y
+union all
+select 3 as y;
diff --git a/yql/essentials/tests/sql/suites/union_all/inner_union_all_with_limits.sql b/yql/essentials/tests/sql/suites/union_all/inner_union_all_with_limits.sql
new file mode 100644
index 0000000000..07ec3df9ba
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/inner_union_all_with_limits.sql
@@ -0,0 +1,21 @@
+USE plato;
+
+SELECT
+    key,
+    value
+FROM (
+    (select * from Input limit 3)
+    union all
+    (select * from Input limit 2)
+)
+WHERE key < "100";
+
+SELECT
+    key,
+    value
+FROM (
+    (select * from Input limit 3)
+    union all
+    select * from Input
+)
+WHERE key < "200";
diff --git a/yql/essentials/tests/sql/suites/union_all/input.txt b/yql/essentials/tests/sql/suites/union_all/input.txt
new file mode 100644
index 0000000000..65949ea745
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/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/union_all/input2.txt b/yql/essentials/tests/sql/suites/union_all/input2.txt
new file mode 100644
index 0000000000..5c939cf453
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/input2.txt
@@ -0,0 +1,4 @@
+{"key"="1";"subkey"="10";"value"="FOO"};
+{"key"="1";"subkey"="20";"value"="BAR"};
+{"key"="1";"subkey"="30";"value"="BAR"};
+{"key"="0";"subkey"="40";"value"="FOO"};
diff --git a/yql/essentials/tests/sql/suites/union_all/input_infer.txt b/yql/essentials/tests/sql/suites/union_all/input_infer.txt
new file mode 100644
index 0000000000..65949ea745
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/input_infer.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/union_all/input_infer.txt.attr b/yql/essentials/tests/sql/suites/union_all/input_infer.txt.attr
new file mode 100644
index 0000000000..ada1f836f8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/input_infer.txt.attr
@@ -0,0 +1,3 @@
+{
+    "infer_schema"=%true
+}
diff --git a/yql/essentials/tests/sql/suites/union_all/mix_map_and_project-trivial_map.cfg b/yql/essentials/tests/sql/suites/union_all/mix_map_and_project-trivial_map.cfg
new file mode 100644
index 0000000000..c557142061
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/mix_map_and_project-trivial_map.cfg
@@ -0,0 +1,2 @@
+in Input input.txt
+in Input2 input_infer.txt
diff --git a/yql/essentials/tests/sql/suites/union_all/mix_map_and_project.cfg b/yql/essentials/tests/sql/suites/union_all/mix_map_and_project.cfg
new file mode 100644
index 0000000000..0e44b7c5ba
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/mix_map_and_project.cfg
@@ -0,0 +1,2 @@
+in Input input.txt
+in Input2 input.txt
diff --git a/yql/essentials/tests/sql/suites/union_all/mix_map_and_project.sql b/yql/essentials/tests/sql/suites/union_all/mix_map_and_project.sql
new file mode 100644
index 0000000000..60d039f9a5
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/mix_map_and_project.sql
@@ -0,0 +1,13 @@
+/* postgres can not */
+/* kikimr can not */
+PRAGMA yt.InferSchema;
+
+SELECT * FROM (
+    SELECT key, '' as value FROM plato.Input
+    UNION ALL
+    SELECT key, value from plato.Input2
+    UNION ALL
+    SELECT '' as key, value from plato.Input
+)
+ORDER BY key, value
+;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/union_all/mix_map_and_read.sql b/yql/essentials/tests/sql/suites/union_all/mix_map_and_read.sql
new file mode 100644
index 0000000000..43f7bcef57
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/mix_map_and_read.sql
@@ -0,0 +1,9 @@
+SELECT * FROM (
+    SELECT key, subkey, '' as value FROM plato.Input
+    UNION ALL
+    SELECT * from plato.Input
+    UNION ALL
+    SELECT '' as key, subkey, value from plato.Input
+)
+ORDER BY key, subkey, value
+;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/union_all/path_and_record.sql b/yql/essentials/tests/sql/suites/union_all/path_and_record.sql
new file mode 100644
index 0000000000..9aa42057eb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/path_and_record.sql
@@ -0,0 +1,11 @@
+/* syntax version 1 */
+/* postgres can not */
+USE plato;
+
+SELECT * FROM (
+    select key, value, TablePath() as path, TableRecordIndex() as record from Input
+    union all
+    select key, value, "" as path, TableRecordIndex() as record from Input
+)
+ORDER BY key, path, record
+;
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_fields.sql b/yql/essentials/tests/sql/suites/union_all/union_all_fields.sql
new file mode 100644
index 0000000000..4d313c3659
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_fields.sql
@@ -0,0 +1,7 @@
+SELECT * FROM (
+    SELECT CAST(key AS int) as key, '' as value FROM plato.Input
+    UNION ALL
+    SELECT 0 as key, value from plato.Input
+)
+ORDER BY key, value
+;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_incompatible.sql b/yql/essentials/tests/sql/suites/union_all/union_all_incompatible.sql
new file mode 100644
index 0000000000..b506121c94
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_incompatible.sql
@@ -0,0 +1,26 @@
+select key from
+(
+    select key, subkey from
+    (
+        select 1 as key, "foo" as subkey
+        union all
+        select 2 as key, "bar" as subkey
+        union all
+        select 3 as key, 123 as subkey
+    )
+)
+order by key;
+
+
+select key from
+(
+    select * from
+    (
+        select 4 as key, "baz" as subkey
+        union all
+        select 5 as key, "goo" as subkey
+        union all
+        select 6 as key, 456 as subkey
+    )
+)
+order by key;
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_multiin.cfg b/yql/essentials/tests/sql/suites/union_all/union_all_multiin.cfg
new file mode 100644
index 0000000000..8d2f379d10
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_multiin.cfg
@@ -0,0 +1,2 @@
+in Input input.txt
+in Input2 input2.txt
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_multiin.sql b/yql/essentials/tests/sql/suites/union_all/union_all_multiin.sql
new file mode 100644
index 0000000000..284528b526
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_multiin.sql
@@ -0,0 +1,9 @@
+USE plato;
+
+SELECT * FROM (
+    SELECT key, value from Input
+    UNION ALL
+    SELECT subkey as key, value from Input2
+)
+ORDER BY key, value
+;
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_multiple.sql b/yql/essentials/tests/sql/suites/union_all/union_all_multiple.sql
new file mode 100644
index 0000000000..d5fc95436f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_multiple.sql
@@ -0,0 +1,9 @@
+SELECT * FROM (
+    SELECT CAST(key AS int) as key, '' as subkey, '' as value FROM plato.Input
+    UNION ALL
+    SELECT 1 as key, subkey, '' as value from plato.Input
+    UNION ALL
+    SELECT 1 as key, '' as subkey, value from plato.Input
+)
+ORDER BY key, subkey, value
+;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_null.sql b/yql/essentials/tests/sql/suites/union_all/union_all_null.sql
new file mode 100644
index 0000000000..960390755e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_null.sql
@@ -0,0 +1,11 @@
+SELECT
+    1/2 as jx,
+    'a' as x,
+    NULL as jy,
+    NULL as y
+UNION ALL
+SELECT
+    NULL as jx,
+    NULL as x,
+    2/1 as jy,
+    'b' as y;
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_subexpr.sql b/yql/essentials/tests/sql/suites/union_all/union_all_subexpr.sql
new file mode 100644
index 0000000000..9f7f840ce9
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_subexpr.sql
@@ -0,0 +1,7 @@
+SELECT *
+FROM (
+    SELECT key, CAST(subkey AS int) as subkey, NULL as value FROM plato.Input
+    UNION ALL
+    SELECT key, NULL as subkey, value from plato.Input
+) as x
+ORDER BY key, subkey, value;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_trivial.sql b/yql/essentials/tests/sql/suites/union_all/union_all_trivial.sql
new file mode 100644
index 0000000000..6ba8ed0790
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_trivial.sql
@@ -0,0 +1,3 @@
+SELECT * FROM plato.Input
+UNION ALL
+SELECT * FROM plato.Input;
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_with_discard_into_result_ansi.sql b/yql/essentials/tests/sql/suites/union_all/union_all_with_discard_into_result_ansi.sql
new file mode 100644
index 0000000000..d3d7107e79
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_with_discard_into_result_ansi.sql
@@ -0,0 +1,14 @@
+/* syntax version 1 */
+/* postgres can not */
+
+use plato;
+pragma AnsiOrderByLimitInUnionAll;
+
+select * from Input
+union all
+select * from Input into result aaa;
+
+discard
+select * from Input
+union all
+select * from Input;
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_with_limits.sql b/yql/essentials/tests/sql/suites/union_all/union_all_with_limits.sql
new file mode 100644
index 0000000000..ad00f72d99
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_with_limits.sql
@@ -0,0 +1,3 @@
+(SELECT * FROM plato.Input WHERE key < "100" LIMIT 2)
+UNION ALL
+(SELECT * FROM plato.Input WHERE key > "100" LIMIT 2);
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_with_parenthesis.sql b/yql/essentials/tests/sql/suites/union_all/union_all_with_parenthesis.sql
new file mode 100644
index 0000000000..b782887269
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_with_parenthesis.sql
@@ -0,0 +1,4 @@
+(SELECT * FROM plato.Input)
+UNION ALL
+(SELECT * FROM plato.Input)
+
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_with_top_level_limits.sqlx b/yql/essentials/tests/sql/suites/union_all/union_all_with_top_level_limits.sqlx
new file mode 100644
index 0000000000..261bf97a83
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_with_top_level_limits.sqlx
@@ -0,0 +1,12 @@
+/* syntax version 1 */
+/* postgres can not */
+
+use plato;
+pragma DisableAnsiOrderByLimitInUnionAll;
+
+$foo =
+select * from Input
+union all
+select * from Input limit 2;
+
+select * from $foo order by subkey;
diff --git a/yql/essentials/tests/sql/suites/union_all/union_all_with_top_level_limits_ansi.sql b/yql/essentials/tests/sql/suites/union_all/union_all_with_top_level_limits_ansi.sql
new file mode 100644
index 0000000000..304bf54999
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/union_all/union_all_with_top_level_limits_ansi.sql
@@ -0,0 +1,28 @@
+/* syntax version 1 */
+/* postgres can not */
+
+use plato;
+pragma AnsiOrderByLimitInUnionAll;
+
+$foo =
+select * from Input
+union all
+select * from Input limit 2;
+
+$bar =
+select * from Input
+union all
+(select * from Input limit 2);
+
+
+select * from $foo order by subkey;
+select * from $bar order by subkey;
+
+select 1 as key
+union all
+select 2 as key assume order by key into result aaa;
+
+discard
+select 1 as key
+union all
+select 2 as key assume order by key;