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 <stanilov...@gmail.com> 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