Merge pull request #11788 from ydb-platform/mergelibs-241120-1113

Library import 241120-1113

30 files changed, 359 insertions, 0 deletions

diff --git a/yql/essentials/tests/sql/suites/simple_columns/default.cfg b/yql/essentials/tests/sql/suites/simple_columns/default.cfg
new file mode 100644
index 0000000000..58878f8945
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/default.cfg
@@ -0,0 +1 @@
+in Input input.txt
diff --git a/yql/essentials/tests/sql/suites/simple_columns/input.txt b/yql/essentials/tests/sql/suites/simple_columns/input.txt
new file mode 100644
index 0000000000..65949ea745
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/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/simple_columns/no_simple_columns_tablerow.sql b/yql/essentials/tests/sql/suites/simple_columns/no_simple_columns_tablerow.sql
new file mode 100644
index 0000000000..231e9d743e
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/no_simple_columns_tablerow.sql
@@ -0,0 +1,17 @@
+/* syntax version 1 */
+PRAGMA DisableSimpleColumns;
+
+SELECT 100500 as magic, TableRow() AS tr FROM plato.Input AS t;
+SELECT 100500 as magic, t.*              FROM plato.Input AS t;
+
+SELECT
+TableRow() AS tr
+FROM (SELECT Just(1ul) AS k, 1 AS v1) AS a
+JOIN (SELECT 1         AS k, 2 AS v2) AS b
+ON a.k = b.k;
+
+SELECT
+*
+FROM (SELECT Just(1ul) AS k, 1 AS v1) AS a
+JOIN (SELECT 1         AS k, 2 AS v2) AS b
+ON a.k = b.k;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base.sql
new file mode 100644
index 0000000000..2d050f1024
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base.sql
@@ -0,0 +1,4 @@
+PRAGMA SimpleColumns;
+
+--INSERT INTO plato.Output
+SELECT 100500 as magic, t.* FROM plato.Input as t
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base_fail.cfg b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base_fail.cfg
new file mode 100644
index 0000000000..9217c920f8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base_fail.cfg
@@ -0,0 +1,2 @@
+xfail
+in Input input.txt
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base_fail.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base_fail.sql
new file mode 100644
index 0000000000..6930913766
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_base_fail.sql
@@ -0,0 +1,13 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+
+USE plato;
+
+$req = (SELECT 100500 as magic, t.* FROM Input as t);
+
+--INSERT INTO Output
+SELECT 
+  ff.*,
+  subkey as magic, -- 'magic' is exist from ff.magic
+  value as val
+FROM $req as ff ORDER BY sk
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_all.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_all.sql
new file mode 100644
index 0000000000..8fbba96abe
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_all.sql
@@ -0,0 +1,12 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT key as kk, subkey as sk, value as val FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  *
+FROM Input JOIN $data as d ON Input.subkey = cast(cast(d.kk as uint32)/100 as string)
+ORDER BY key, val
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_all_1.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_all_1.sql
new file mode 100644
index 0000000000..dbb7d0ba60
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_all_1.sql
@@ -0,0 +1,14 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+pragma DisableCoalesceJoinKeysOnQualifiedAll;
+
+$foo = select 1 as key, 1 as value1;
+$bar = select 1l as key, 2 as value2;
+
+select * from $foo as foo 
+join $bar as bar on foo.key = bar.key;
+
+-- output key has type Int64
+
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_all_2.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_all_2.sql
new file mode 100644
index 0000000000..4037bdd680
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_all_2.sql
@@ -0,0 +1,14 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+pragma CoalesceJoinKeysOnQualifiedAll;
+
+$foo = select 1 as key, 1 as value1;
+$bar = select 1l as key, 2 as value2;
+
+select * from $foo as foo 
+join $bar as bar on foo.key = bar.key;
+
+-- output key has type Int64
+
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_bug8923.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_bug8923.sql
new file mode 100644
index 0000000000..a4d816e21a
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_bug8923.sql
@@ -0,0 +1,16 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+-- fails with CoalesceJoinKeysOnQualifiedAll
+pragma DisableCoalesceJoinKeysOnQualifiedAll;
+
+$foo = select 1 as key, 1 as value1;
+$bar = select 1l as key, 2 as value2;
+$baz = select 1l as key, 2 as value3;
+
+
+select foo.* from $foo as foo 
+join $bar as bar on cast(foo.key as Int32) = bar.key
+join $baz as baz on bar.key = baz.key
+
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_qualified_all_disable.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_qualified_all_disable.sql
new file mode 100644
index 0000000000..633c62c500
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_qualified_all_disable.sql
@@ -0,0 +1,14 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+pragma DisableCoalesceJoinKeysOnQualifiedAll;
+
+$foo = select 1 as key, 1 as value1;
+$bar = select 1l as key, 2 as value2;
+
+select foo.* from $foo as foo 
+join $bar as bar on foo.key = bar.key;
+
+-- output key has type Int32
+
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_qualified_all_enable.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_qualified_all_enable.sql
new file mode 100644
index 0000000000..9d55cd5cb4
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_qualified_all_enable.sql
@@ -0,0 +1,14 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+pragma CoalesceJoinKeysOnQualifiedAll;
+
+$foo = select 1 as key, 1 as value1;
+$bar = select 1l as key, 2 as value2;
+
+select foo.* from $foo as foo 
+join $bar as bar on foo.key = bar.key;
+
+-- output key has type Int64
+
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_1.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_1.sql
new file mode 100644
index 0000000000..d3933f7860
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_1.sql
@@ -0,0 +1,18 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+pragma DisableCoalesceJoinKeysOnQualifiedAll;
+
+select 
+    b.* without b.x
+from (select * from (select AsList(1, 2, 3) as x, AsList(1, 2) as y) flatten by (x, y)) as a
+join (select * from (select AsList(1, 2, 3) as x, AsList(2, 3) as y) flatten by (x, y)) as b
+on a.x == b.x and a.y == b.y;
+
+select 
+    * without b.x
+from (select * from (select AsList(1, 2, 3) as x, AsList(1, 2) as y) flatten by (x, y)) as a
+join (select * from (select AsList(1, 2, 3) as x, AsList(2, 3) as y) flatten by (x, y)) as b
+on a.x == b.x and a.y == b.y
+
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_2.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_2.sql
new file mode 100644
index 0000000000..ff33a478ab
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_2.sql
@@ -0,0 +1,18 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+pragma CoalesceJoinKeysOnQualifiedAll;
+
+select 
+    b.* without b.x
+from (select * from (select AsList(1, 2, 3) as x, AsList(1, 2) as y) flatten by (x, y)) as a
+join (select * from (select AsList(1, 2, 3) as x, AsList(2, 3) as y) flatten by (x, y)) as b
+on a.x == b.x and a.y == b.y;
+
+select 
+    * without b.x
+from (select * from (select AsList(1, 2, 3) as x, AsList(1, 2) as y) flatten by (x, y)) as a
+join (select * from (select AsList(1, 2, 3) as x, AsList(2, 3) as y) flatten by (x, y)) as b
+on a.x == b.x and a.y == b.y
+
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_left_semi_1.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_left_semi_1.sql
new file mode 100644
index 0000000000..f2e3ca2c12
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_left_semi_1.sql
@@ -0,0 +1,8 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+pragma DisableCoalesceJoinKeysOnQualifiedAll;
+
+select a.* without a.key, a.value from Input as a left semi join Input as b using(key) order by subkey;
+select * without a.key, a.value from Input as a left semi join Input as b using(key) order by subkey;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_left_semi_2.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_left_semi_2.sql
new file mode 100644
index 0000000000..fc85664b2c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_coalesce_without_left_semi_2.sql
@@ -0,0 +1,8 @@
+/* syntax version 1 */
+USE plato;
+
+pragma SimpleColumns;
+pragma CoalesceJoinKeysOnQualifiedAll;
+
+select a.* without a.key, a.value from Input as a left semi join Input as b using(key) order by subkey;
+select * without a.key, a.value from Input as a left semi join Input as b using(key) order by subkey;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_fail.cfg b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_fail.cfg
new file mode 100644
index 0000000000..9217c920f8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_fail.cfg
@@ -0,0 +1,2 @@
+xfail
+in Input input.txt
\ No newline at end of file
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_fail.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_fail.sql
new file mode 100644
index 0000000000..7728a22ba1
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_fail.sql
@@ -0,0 +1,13 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT key as kk, subkey as sk, value as val FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  d.*,
+  Input.key as kk -- 'kk' is exist from d.kk
+FROM Input JOIN $data as d ON Input.subkey = cast(cast(d.kk as uint32)/100 as string)
+ORDER BY key, val
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_qualified.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_qualified.sql
new file mode 100644
index 0000000000..b2cac15f8f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_qualified.sql
@@ -0,0 +1,13 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT key as kk, subkey as sk, value as val FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  d.*,
+  key
+FROM Input JOIN $data as d ON Input.subkey = cast(cast(d.kk as uint32)/100 as string)
+ORDER BY key, val
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_all_key_without.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_all_key_without.sql
new file mode 100644
index 0000000000..51b49cdb29
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_all_key_without.sql
@@ -0,0 +1,14 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT key as kk, subkey as sk, value FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  *
+WITHOUT
+  Input.value
+FROM Input JOIN $data as d ON Input.subkey = cast(cast(d.kk as uint32)/100 as string)
+ORDER BY key, value
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key.sql
new file mode 100644
index 0000000000..5318d327bb
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key.sql
@@ -0,0 +1,13 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT key, subkey as sk, value FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  d.*,
+  subkey
+FROM Input JOIN $data as d ON Input.key = d.key and Input.value == d.value
+ORDER BY key, value
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key_by_all.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key_by_all.sql
new file mode 100644
index 0000000000..e75b01c64a
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key_by_all.sql
@@ -0,0 +1,12 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT key, subkey as sk, value FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  *
+FROM Input JOIN $data as d ON Input.key = d.key and Input.value == d.value
+ORDER BY key, value
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key_without.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key_without.sql
new file mode 100644
index 0000000000..a99ae21f79
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_subreq_same_key_without.sql
@@ -0,0 +1,15 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT key as kk, subkey as sk, value FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  Input.*,
+  d.value as val
+WITHOUT
+  Input.subkey
+FROM Input JOIN $data as d ON Input.subkey = cast(cast(d.kk as uint32)/100 as string)
+ORDER BY key, value
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_without_resolve_dublicates.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_without_resolve_dublicates.sql
new file mode 100644
index 0000000000..686bb52cbe
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_without_resolve_dublicates.sql
@@ -0,0 +1,16 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT key as kk, subkey as sk, "data: " || value as value FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  Input.*,
+  d.*,
+  Input.value as valueFromInput
+WITHOUT
+  Input.value
+FROM Input JOIN $data as d ON Input.subkey = cast(cast(d.kk as uint32)/100 as string)
+ORDER BY key, value
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_without_resolve_dublicates_mult.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_without_resolve_dublicates_mult.sql
new file mode 100644
index 0000000000..b262fcf84b
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_join_without_resolve_dublicates_mult.sql
@@ -0,0 +1,17 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+USE plato;
+
+$data = (SELECT cast(cast(key as uint32)/100 as string) as key, key as kk, cast(subkey as uint32) * 10 as subkey, "data: " || value as value FROM Input WHERE cast(key as uint32)/100 < 5);
+
+--INSERT INTO Output
+SELECT
+  Input.*,
+  d.*,
+  Input.value as valueFromInput,
+  d.subkey as subkeyFromD
+WITHOUT
+  Input.value, d.subkey, d.key
+FROM Input JOIN $data as d ON Input.subkey = d.key
+ORDER BY key, value
+;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_qualified_all_and_group_by.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_qualified_all_and_group_by.sql
new file mode 100644
index 0000000000..e3cdad7350
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_qualified_all_and_group_by.sql
@@ -0,0 +1,14 @@
+/* postgres can not */
+USE plato;
+PRAGMA SimpleColumns;
+
+--INSERT INTO Output
+SELECT
+    hundred_keys, sum(cast(subkey as uint32))
+FROM (
+    SELECT data.key as dkey, data.*
+    FROM Input as data
+) as middle
+GROUP BY
+    cast(middle.dkey as uint32)/100 as hundred_keys
+ORDER BY hundred_keys
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_subreq.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_subreq.sql
new file mode 100644
index 0000000000..07417c5874
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_subreq.sql
@@ -0,0 +1,9 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+
+USE plato;
+
+$req = (SELECT 100500 as magic, t.* FROM Input as t);
+
+--INSERT INTO Output
+SELECT subkey as sk, value as val FROM $req ORDER BY sk
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_subreq_all.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_subreq_all.sql
new file mode 100644
index 0000000000..8f62e71cfa
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_subreq_all.sql
@@ -0,0 +1,9 @@
+/* postgres can not */
+PRAGMA SimpleColumns;
+
+USE plato;
+
+$req = (SELECT 100500 as magic, t.* FROM Input as t);
+
+--INSERT INTO Output
+SELECT ff.*, subkey as sk, value as val FROM $req as ff ORDER BY sk
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_tablerow.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_tablerow.sql
new file mode 100644
index 0000000000..f9cdb31af7
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_tablerow.sql
@@ -0,0 +1,17 @@
+/* syntax version 1 */
+PRAGMA SimpleColumns;
+
+SELECT 100500 as magic, TableRow() AS tr FROM plato.Input AS t;
+SELECT 100500 as magic, t.*              FROM plato.Input AS t;
+
+SELECT
+TableRow() AS tr
+FROM (SELECT Just(1ul) AS k, 1 AS v1) AS a
+JOIN (SELECT 1         AS k, 2 AS v2) AS b
+ON a.k = b.k;
+
+SELECT
+*
+FROM (SELECT Just(1ul) AS k, 1 AS v1) AS a
+JOIN (SELECT 1         AS k, 2 AS v2) AS b
+ON a.k = b.k;
diff --git a/yql/essentials/tests/sql/suites/simple_columns/simple_columns_union_all_qualified_star.sql b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_union_all_qualified_star.sql
new file mode 100644
index 0000000000..48b00df4ca
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/simple_columns/simple_columns_union_all_qualified_star.sql
@@ -0,0 +1,18 @@
+/* postgres can not */
+use plato;
+PRAGMA simplecolumns;
+
+insert into @A (key, value) values
+('x', 1),
+('y', 2);
+
+insert into @B (key, value) values
+('y', 3),
+('z', 4);
+commit;
+
+select A.* from @A AS A LEFT ONLY JOIN @B AS B ON A.key = B.key
+UNION ALL
+select B.* from @A AS A RIGHT ONLY JOIN @B AS B ON A.key = B.key
+
+