summaryrefslogtreecommitdiffstats
path: root/test/join9.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/join9.test')
-rw-r--r--test/join9.test565
1 files changed, 565 insertions, 0 deletions
diff --git a/test/join9.test b/test/join9.test
new file mode 100644
index 0000000..e547d4c
--- /dev/null
+++ b/test/join9.test
@@ -0,0 +1,565 @@
+# 2022-04-16
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+# This file implements tests for RIGHT and FULL OUTER JOINs.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+foreach {id schema} {
+ 1 {
+ CREATE TABLE t3(id INTEGER PRIMARY KEY, w TEXT);
+ CREATE TABLE t4(id INTEGER PRIMARY KEY, x TEXT);
+ CREATE TABLE t5(id INTEGER PRIMARY KEY, y TEXT);
+ CREATE TABLE t6(id INTEGER PRIMARY KEY, z INT);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
+ INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
+ INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
+ (5,'blue');
+ INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
+ }
+ 2 {
+ CREATE TABLE t3(id INT PRIMARY KEY, w TEXT) WITHOUT ROWID;
+ CREATE TABLE t4(id INT PRIMARY KEY, x TEXT) WITHOUT ROWID;
+ CREATE TABLE t5(id INT PRIMARY KEY, y TEXT) WITHOUT ROWID;
+ CREATE TABLE t6(id INT PRIMARY KEY, z INT) WITHOUT ROWID;
+ CREATE TABLE dual(dummy TEXT);
+ INSERT INTO dual(dummy) VALUES('x');
+ INSERT INTO t3(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
+ INSERT INTO t4(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
+ INSERT INTO t5(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
+ (5,'blue');
+ INSERT INTO t6(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
+ }
+ 3 {
+ CREATE TABLE t3x(id INTEGER PRIMARY KEY, w TEXT);
+ CREATE TABLE t4x(id INTEGER PRIMARY KEY, x TEXT);
+ CREATE TABLE t5x(id INTEGER PRIMARY KEY, y TEXT);
+ CREATE TABLE t6x(id INTEGER PRIMARY KEY, z INT);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ INSERT INTO t3x(id,w) VALUES(2,'two'),(3,'three'),(6,'six'),(7,'seven');
+ INSERT INTO t4x(id,x) VALUES(2,'alice'),(4,'bob'),(6,'cindy'),(8,'dave');
+ INSERT INTO t5x(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow'),(4,'green'),
+ (5,'blue');
+ INSERT INTO t6x(id,z) VALUES(3,333),(4,444),(5,555),(0,1000),(9,999);
+ CREATE VIEW t3 AS SELECT * FROM t3x LIMIT 1000;
+ CREATE VIEW t4 AS SELECT * FROM t4x LIMIT 1000;
+ CREATE VIEW t5 AS SELECT * FROM t5x LIMIT 1000;
+ CREATE VIEW t6 AS SELECT * FROM t6x LIMIT 1000;
+ }
+ 4 {
+ CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT);
+ CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT);
+ CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT);
+ CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT);
+ CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT);
+ CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT);
+ CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT);
+ CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three');
+ INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven');
+ INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob');
+ INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave');
+ INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow');
+ INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue');
+ INSERT INTO t6a(id,z) VALUES(3,333),(4,444);
+ INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999);
+ CREATE VIEW t3 AS SELECT * FROM t3a UNION ALL SELECT * FROM t3b;
+ CREATE VIEW t4 AS SELECT * FROM t4a UNION ALL SELECT * FROM t4b;
+ CREATE VIEW t5 AS SELECT * FROM t5a UNION ALL SELECT * FROM t5b;
+ CREATE VIEW t6 AS SELECT * FROM t6a UNION ALL SELECT * FROM t6b;
+ }
+ 5 {
+ CREATE TABLE t3a(id INTEGER PRIMARY KEY, w TEXT) WITHOUT ROWID;
+ CREATE TABLE t3b(id INTEGER PRIMARY KEY, w TEXT);
+ CREATE TABLE t4a(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID;
+ CREATE TABLE t4b(id INTEGER PRIMARY KEY, x TEXT) WITHOUT ROWID;
+ CREATE TABLE t5a(id INTEGER PRIMARY KEY, y TEXT);
+ CREATE TABLE t5b(id INTEGER PRIMARY KEY, y TEXT) WITHOUT ROWID;
+ CREATE TABLE t6a(id INTEGER PRIMARY KEY, z INT);
+ CREATE TABLE t6b(id INTEGER PRIMARY KEY, z INT);
+ CREATE VIEW dual(dummy) AS VALUES('x');
+ INSERT INTO t3a(id,w) VALUES(2,'two'),(3,'three');
+ INSERT INTO t3b(id,w) VALUES(6,'six'),(7,'seven');
+ INSERT INTO t4a(id,x) VALUES(2,'alice'),(4,'bob');
+ INSERT INTO t4b(id,x) VALUES(6,'cindy'),(8,'dave');
+ INSERT INTO t5a(id,y) VALUES(1,'red'),(2,'orange'),(3,'yellow');
+ INSERT INTO t5b(id,y) VALUES(4,'green'),(5,'blue');
+ INSERT INTO t6a(id,z) VALUES(3,333),(4,444);
+ INSERT INTO t6b(id,z) VALUES(5,555),(0,1000),(9,999);
+ CREATE VIEW t3 AS SELECT * FROM t3a UNION ALL SELECT * FROM t3b;
+ CREATE VIEW t4 AS SELECT * FROM t4a UNION ALL SELECT * FROM t4b LIMIT 50;
+ CREATE VIEW t5 AS SELECT * FROM t5a UNION ALL SELECT * FROM t5b LIMIT 100;
+ CREATE VIEW t6 AS SELECT * FROM t6a UNION ALL SELECT * FROM t6b;
+ }
+} {
+ reset_db
+ db nullvalue -
+ do_execsql_test join9-$id.setup $schema {}
+
+ # Verifid by PG-14 for case 1
+ do_execsql_test join9-$id.100 {
+ SELECT *, t4.id, t5.id, t6.id
+ FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6
+ ORDER BY 1;
+ } {
+ 2 alice orange - 2 2 -
+ 4 bob green 444 4 4 4
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ }
+
+ do_execsql_test join9-$id.101 {
+ SELECT *, t4.id, t5.id, t6.id
+ FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6
+ ORDER BY id;
+ } {
+ 2 alice orange - 2 2 -
+ 4 bob green 444 4 4 4
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ }
+ do_execsql_test join9-$id.102 {
+ SELECT *, t4.id, t5.id, t6.id
+ FROM t4 LEFT JOIN t5 USING(id) LEFT JOIN t6 USING(id)
+ ORDER BY id;
+ } {
+ 2 alice orange - 2 2 -
+ 4 bob green 444 4 4 4
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ }
+
+ # Verifid by PG-14 using case 1
+ do_execsql_test join9-$id.200 {
+ SELECT id, x, y, z, t4.id, t5.id, t6.id
+ FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6
+ ORDER BY 1;
+ } {
+ 2 alice orange - 2 2 -
+ 4 bob green 444 4 4 4
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ }
+
+ do_execsql_test join9-$id.201 {
+ SELECT id, x, y, z, t4.id, t5.id, t6.id
+ FROM t5 NATURAL RIGHT JOIN t4 NATURAL LEFT JOIN t6
+ ORDER BY id;
+ } {
+ 2 alice orange - 2 2 -
+ 4 bob green 444 4 4 4
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ }
+
+ # Verified by PG-14 using case 1
+ do_execsql_test join9-$id.300 {
+ SELECT *, t4.id, t5.id, t6.id
+ FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6
+ ORDER BY 1;
+ } {
+ 0 - - 1000 - - 0
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 9 - - 999 - - 9
+ }
+
+ do_execsql_test join9-$id.301 {
+ SELECT *, t4.id, t5.id, t6.id
+ FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6
+ ORDER BY id;
+ } {
+ 0 - - 1000 - - 0
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 9 - - 999 - - 9
+ }
+
+ # Verified by PG-14 for case 1
+ do_execsql_test join9-$id.400 {
+ SELECT *, t4.id, t5.id, t6.id
+ FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6
+ ORDER BY 1;
+ } {
+ 0 - - 1000 - - 0
+ 1 - red - - 1 -
+ 2 alice orange - 2 2 -
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ 9 - - 999 - - 9
+ }
+
+ do_execsql_test join9-$id.401 {
+ SELECT *, t4.id, t5.id, t6.id
+ FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6
+ ORDER BY id;
+ } {
+ 0 - - 1000 - - 0
+ 1 - red - - 1 -
+ 2 alice orange - 2 2 -
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ 9 - - 999 - - 9
+ }
+ do_execsql_test join9-$id.402 {
+ SELECT id, x, y, z, t4.id, t5.id, t6.id
+ FROM t4 NATURAL FULL JOIN t6 NATURAL FULL JOIN t5
+ ORDER BY id;
+ } {
+ 0 - - 1000 - - 0
+ 1 - red - - 1 -
+ 2 alice orange - 2 2 -
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ 9 - - 999 - - 9
+ }
+ do_execsql_test join9-$id.403 {
+ SELECT id, x, y, z, t4.id, t5.id, t6.id
+ FROM t5 NATURAL FULL JOIN t4 NATURAL FULL JOIN t6
+ ORDER BY id;
+ } {
+ 0 - - 1000 - - 0
+ 1 - red - - 1 -
+ 2 alice orange - 2 2 -
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ 9 - - 999 - - 9
+ }
+ do_execsql_test join9-$id.404 {
+ SELECT id, x, y, z, t4.id, t5.id, t6.id
+ FROM t5 NATURAL FULL JOIN t6 NATURAL FULL JOIN t4
+ ORDER BY id;
+ } {
+ 0 - - 1000 - - 0
+ 1 - red - - 1 -
+ 2 alice orange - 2 2 -
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ 9 - - 999 - - 9
+ }
+ do_execsql_test join9-$id.405 {
+ SELECT id, x, y, z, t4.id, t5.id, t6.id
+ FROM t6 NATURAL FULL JOIN t4 NATURAL FULL JOIN t5
+ ORDER BY id;
+ } {
+ 0 - - 1000 - - 0
+ 1 - red - - 1 -
+ 2 alice orange - 2 2 -
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ 9 - - 999 - - 9
+ }
+ do_execsql_test join9-$id.406 {
+ SELECT id, x, y, z, t4.id, t5.id, t6.id
+ FROM t6 NATURAL FULL JOIN t5 NATURAL FULL JOIN t4
+ ORDER BY id;
+ } {
+ 0 - - 1000 - - 0
+ 1 - red - - 1 -
+ 2 alice orange - 2 2 -
+ 3 - yellow 333 - 3 3
+ 4 bob green 444 4 4 4
+ 5 - blue 555 - 5 5
+ 6 cindy - - 6 - -
+ 8 dave - - 8 - -
+ 9 - - 999 - - 9
+ }
+
+ # Verified by PG-14 using case 1
+ do_execsql_test join9-$id.500 {
+ SELECT id, w, x, y, z
+ FROM t3 FULL JOIN t4 USING(id)
+ NATURAL FULL JOIN t5
+ FULL JOIN t6 USING(id)
+ ORDER BY 1;
+ } {
+ 0 - - - 1000
+ 1 - - red -
+ 2 two alice orange -
+ 3 three - yellow 333
+ 4 - bob green 444
+ 5 - - blue 555
+ 6 six cindy - -
+ 7 seven - - -
+ 8 - dave - -
+ 9 - - - 999
+ }
+
+ # Verified by PG-14 using case 1
+ do_execsql_test join9-$id.600 {
+ SELECT id, w, x, y, z
+ FROM t3 JOIN dual AS d1 ON true
+ FULL JOIN t4 USING(id)
+ JOIN dual AS d2 ON true
+ NATURAL FULL JOIN t5
+ JOIN dual AS d3 ON true
+ FULL JOIN t6 USING(id)
+ CROSS JOIN dual AS d4
+ ORDER BY 1;
+ } {
+ 0 - - - 1000
+ 1 - - red -
+ 2 two alice orange -
+ 3 three - yellow 333
+ 4 - bob green 444
+ 5 - - blue 555
+ 6 six cindy - -
+ 7 seven - - -
+ 8 - dave - -
+ 9 - - - 999
+ }
+
+ # Verified by PG-14 using case 1
+ do_execsql_test join9-$id.700 {
+ SELECT id, w, x, y, z
+ FROM t3 JOIN dual AS d1 ON true
+ FULL JOIN t4 USING(id)
+ JOIN dual AS d2 ON true
+ NATURAL FULL JOIN t5
+ JOIN dual AS d3 ON true
+ FULL JOIN t6 USING(id)
+ CROSS JOIN dual AS d4
+ WHERE x<>'bob' OR x IS NULL
+ ORDER BY 1;
+ } {
+ 0 - - - 1000
+ 1 - - red -
+ 2 two alice orange -
+ 3 three - yellow 333
+ 5 - - blue 555
+ 6 six cindy - -
+ 7 seven - - -
+ 8 - dave - -
+ 9 - - - 999
+ }
+
+ # Verified by PG-14 using case 1
+ do_execsql_test join9-$id.800 {
+ WITH t7(id,a) AS MATERIALIZED (SELECT * FROM t4 WHERE false)
+ SELECT *
+ FROM t7
+ JOIN t7 AS t7b USING(id)
+ FULL JOIN t3 USING(id);
+ } {
+ 2 - - two
+ 3 - - three
+ 6 - - six
+ 7 - - seven
+ }
+
+ # Verified by PG-14
+ do_execsql_test join9-$id.900 {
+ SELECT *
+ FROM (t3 NATURAL FULL JOIN t4)
+ NATURAL FULL JOIN
+ (t5 NATURAL FULL JOIN t6)
+ ORDER BY 1;
+ } {
+ 0 - - - 1000
+ 1 - - red -
+ 2 two alice orange -
+ 3 three - yellow 333
+ 4 - bob green 444
+ 5 - - blue 555
+ 6 six cindy - -
+ 7 seven - - -
+ 8 - dave - -
+ 9 - - - 999
+ }
+ do_execsql_test join9-$id.910 {
+ SELECT *
+ FROM t3 NATURAL FULL JOIN
+ (t4 NATURAL FULL JOIN
+ (t5 NATURAL FULL JOIN t6))
+ ORDER BY 1;
+ } {
+ 0 - - - 1000
+ 1 - - red -
+ 2 two alice orange -
+ 3 three - yellow 333
+ 4 - bob green 444
+ 5 - - blue 555
+ 6 six cindy - -
+ 7 seven - - -
+ 8 - dave - -
+ 9 - - - 999
+ }
+ do_execsql_test join9-$id.920 {
+ SELECT *
+ FROM t3 FULL JOIN (
+ t4 FULL JOIN (
+ t5 FULL JOIN t6 USING (id)
+ ) USING(id)
+ ) USING(id)
+ ORDER BY 1;
+ } {
+ 0 - - - 1000
+ 1 - - red -
+ 2 two alice orange -
+ 3 three - yellow 333
+ 4 - bob green 444
+ 5 - - blue 555
+ 6 six cindy - -
+ 7 seven - - -
+ 8 - dave - -
+ 9 - - - 999
+ }
+ do_execsql_test join9-$id.920 {
+ SELECT *
+ FROM t3 FULL JOIN (
+ t4 FULL JOIN (
+ t5 FULL JOIN t6 USING (id)
+ ) USING(id)
+ ) USING(id)
+ ORDER BY 1;
+ } {
+ 0 - - - 1000
+ 1 - - red -
+ 2 two alice orange -
+ 3 three - yellow 333
+ 4 - bob green 444
+ 5 - - blue 555
+ 6 six cindy - -
+ 7 seven - - -
+ 8 - dave - -
+ 9 - - - 999
+ }
+
+ # Verified by PG-14
+ do_execsql_test join9-$id.930 {
+ SELECT *
+ FROM t3 FULL JOIN (
+ t4 FULL JOIN (
+ t5 FULL JOIN t6 USING(id)
+ ) USING(id)
+ ) AS j1 ON j1.id=t3.id
+ ORDER BY coalesce(t3.id,j1.id);
+ } {
+ - - 0 - - 1000
+ - - 1 - red -
+ 2 two 2 alice orange -
+ 3 three 3 - yellow 333
+ - - 4 bob green 444
+ - - 5 - blue 555
+ 6 six 6 cindy - -
+ 7 seven - - - -
+ - - 8 dave - -
+ - - 9 - - 999
+ }
+
+ # Verified by PG-14
+ do_execsql_test join9-$id.940 {
+ SELECT *
+ FROM t3 FULL JOIN (
+ t4 RIGHT JOIN (
+ t5 FULL JOIN t6 USING(id)
+ ) USING(id)
+ ) AS j1 ON j1.id=t3.id
+ ORDER BY coalesce(t3.id,j1.id);
+ } {
+ - - 0 - - 1000
+ - - 1 - red -
+ 2 two 2 alice orange -
+ 3 three 3 - yellow 333
+ - - 4 bob green 444
+ - - 5 - blue 555
+ 6 six - - - -
+ 7 seven - - - -
+ - - 9 - - 999
+ }
+
+ # Verified by PG-14
+ do_execsql_test join9-$id.950 {
+ SELECT *
+ FROM t3 FULL JOIN (
+ t4 LEFT JOIN (
+ t5 FULL JOIN t6 USING(id)
+ ) USING(id)
+ ) AS j1 ON j1.id=t3.id
+ ORDER BY coalesce(t3.id,j1.id);
+ } {
+ 2 two 2 alice orange -
+ 3 three - - - -
+ - - 4 bob green 444
+ 6 six 6 cindy - -
+ 7 seven - - - -
+ - - 8 dave - -
+ }
+
+ # Restriction (27) in the query flattener
+ # Verified by PG-14
+ do_execsql_test join9-$id.1000 {
+ WITH t56(id,y,z) AS (SELECT * FROM t5 FULL JOIN t6 USING(id) LIMIT 50)
+ SELECT id,x,y,z FROM t4 JOIN t56 USING(id)
+ ORDER BY 1;
+ } {
+ 2 alice orange -
+ 4 bob green 444
+ }
+
+ # Verified by PG-14
+ do_execsql_test join9-$id.1010 {
+ SELECT id,x,y,z
+ FROM t4 INNER JOIN (t5 FULL JOIN t6 USING(id)) USING(id)
+ ORDER BY 1;
+ } {
+ 2 alice orange -
+ 4 bob green 444
+ }
+
+ # Verified by PG-14
+ do_execsql_test join9-$id.1020 {
+ SELECT id,x,y,z
+ FROM t4 FULL JOIN t5 USING(id) INNER JOIN t6 USING(id)
+ ORDER BY 1;
+ } {
+ 3 - yellow 333
+ 4 bob green 444
+ 5 - blue 555
+ }
+
+ # Verified by PG-14
+ do_execsql_test join9-$id.1030 {
+ WITH t45(id,x,y) AS (SELECT * FROM t4 FULL JOIN t5 USING(id) LIMIT 50)
+ SELECT id,x,y,z FROM t45 JOIN t6 USING(id)
+ ORDER BY 1;
+ } {
+ 3 - yellow 333
+ 4 bob green 444
+ 5 - blue 555
+ }
+
+}
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-19 21:02:36 +0000