This is an automated email from the ASF dual-hosted git repository.
zstan pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new bb7e31f6c7 IGNITE-21938 Sql. Cover SQL F041-07 feature by tests (#3642)
bb7e31f6c7 is described below
commit bb7e31f6c72a678473f6276334b9bbece852498e
Author: Evgeniy Stanilovskiy <[email protected]>
AuthorDate: Fri Apr 26 12:34:58 2024 +0300
IGNITE-21938 Sql. Cover SQL F041-07 feature by tests (#3642)
---
.../test_table_from_outer_join_used_in_inner.test | 85 ++++++++++++++++++++++
1 file changed, 85 insertions(+)
diff --git
a/modules/sql-engine/src/integrationTest/sql/join/inner/test_table_from_outer_join_used_in_inner.test
b/modules/sql-engine/src/integrationTest/sql/join/inner/test_table_from_outer_join_used_in_inner.test
new file mode 100644
index 0000000000..dcd781c00e
--- /dev/null
+++
b/modules/sql-engine/src/integrationTest/sql/join/inner/test_table_from_outer_join_used_in_inner.test
@@ -0,0 +1,85 @@
+# name: sql/join/inner/test_table_from_outer_join_used_in_inner.test
+# description: SQL feature F041-07 (The inner table in a left or right outer
join can also be used in an inner join)
+# group: [Basic joined table]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE t1(c11 INTEGER, c12 INTEGER, c13 CHAR);
+
+statement ok
+INSERT INTO t1 VALUES (1, 2, 'a'), (2, 3, 'b'), (3, 4, 'c')
+
+statement ok
+CREATE TABLE t2 (c21 INTEGER, c22 INTEGER, c23 CHAR);
+
+statement ok
+INSERT INTO t2 VALUES (2, 3, 'a'), (3, 4, 'b'), (4, 3, 'c')
+
+query II rowsort
+select j.c21, j.c22 from (SELECT c21, c22 from t2 LEFT OUTER JOIN t1 ON
(t2.c21 = t1.c11)) j INNER JOIN t2 t22 ON (t22.c21 = j.c22);
+----
+2 3
+3 4
+4 3
+
+query II rowsort
+select c11, j.c22 from (SELECT c21, c22 from t2 LEFT OUTER JOIN t1 ON (t2.c21
= t1.c11)) j INNER JOIN t1 t11 ON (t11.c12 = j.c22);
+----
+2 3
+2 3
+3 4
+
+query II rowsort
+select t1.c11, t2.c22 from (t2 LEFT OUTER JOIN t1 ON (t2.c21 = t1.c11)) INNER
JOIN t2 t22 ON (t22.c21 = c11);
+----
+2 3
+3 4
+
+query II rowsort
+select c21, t11.c12 from (t2 LEFT OUTER JOIN t1 ON (t2.c21 = t1.c11)) INNER
JOIN t1 t11 ON (t11.c11 = t2.c22);
+----
+2 4
+4 4
+
+query II rowsort
+select t1.c11, t2.c21 from (t2 RIGHT OUTER JOIN t1 ON (t2.c21 = t1.c11)) INNER
JOIN t2 t22 ON (t22.c21 = c12);
+----
+1 null
+2 2
+3 3
+
+query II rowsort
+select t11.c11, t2.c21 from (t2 RIGHT OUTER JOIN t1 ON (t2.c21 = t1.c11))
INNER JOIN t1 t11 ON (t11.c12 = t2.c22);
+----
+2 2
+3 3
+
+query II rowsort
+select t1.c11, t2.c21 from (t2 FULL OUTER JOIN t1 ON (t2.c21 = t1.c11)) INNER
JOIN t2 t22 ON (t22.c21 = c12);
+----
+1 null
+2 2
+3 3
+
+query II rowsort
+select t1.c11, t2.c21 from (t2 FULL OUTER JOIN t1 ON (t2.c21 = t1.c11)) INNER
JOIN t1 t11 ON (t11.c12 = t2.c22);
+----
+null 4
+2 2
+3 3
+
+query III rowsort
+select t2.c21, t2.c22, t2.c23 from (t2 LEFT OUTER JOIN t1 ON (t2.c21 =
t1.c11)) INNER JOIN t2 t22 ON (t22.c23 = 'a');
+----
+2 3 a
+3 4 b
+4 3 c
+
+query III rowsort
+select t1.c11, t1.c12, t1.c13 from (t2 LEFT OUTER JOIN t1 ON (t2.c21 =
t1.c11)) INNER JOIN t1 t11 ON (t11.c13 = 'a');
+----
+null null null
+2 3 b
+3 4 c
