| # 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 |