New test cases, one of which is failing, indicating a bug that needs fixing.

FossilOrigin-Name: bd5fd68435ff068c18d7d46b33cf7591263a03c32a917a7df7c087b08c573cc8

3 files changed, 171 insertions, 29 deletions

diff --git a/test/join2.test b/test/join2.test
index 4142fd15b..e549c4934 100644
--- a/test/join2.test
+++ b/test/join2.test
@@ -78,10 +78,10 @@ ifcapable subquery {
   } {1 11 111 1111 2 22 {} {} 3 33 {} {}}
   do_test join2-1.7-rj {
     execsql {
-      SELECT * FROM
-        (t2 NATURAL JOIN t3) NATURAL RIGHT JOIN t1
+      SELECT a, b, c, d FROM
+        t2 NATURAL JOIN t3 NATURAL RIGHT JOIN t1
     }
-  } {11 111 1111 1 {} {} {} 2 {} {} {} 3}
+  } {1 11 111 1111 2 22 {} {} 3 33 {} {}}
 }
 
 #-------------------------------------------------------------------------
diff --git a/test/join8.test b/test/join8.test
index bd80c96de..0317722fd 100644
--- a/test/join8.test
+++ b/test/join8.test
@@ -50,32 +50,58 @@ do_test join8-issue-1 {set rc "Issue-1 'row-value subroutine' unresolved"} {}
 #
 do_test join8-issue-2 {set rc "Issue-2 'jump to addrHalt' unresolved"} {}
 
-# Pending issue #3:
-# USING does not work with RIGHT JOIN.
-# Test case:
+# Demonstrate that nested FULL JOINs and USING clauses work
 #
-# 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);
-# 
-# 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);
-# 
-# .mode box
-# SELECT *, t4.id, t5.id, t6.id
-#   FROM t4 NATURAL LEFT JOIN t5 NATURAL LEFT JOIN t6
-#  ORDER BY coalesce(t4.id,t5.id,t6.id);
-# 
-# SELECT *, t4.id, t5.id, t6.id
-#   FROM t4 NATURAL RIGHT JOIN t5 NATURAL RIGHT JOIN t6
-#  ORDER BY coalesce(t4.id,t5.id,t6.id);
-# 
-# SELECT *, t4.id, t5.id, t6.id
-#   FROM t4 NATURAL FULL JOIN t5 NATURAL FULL JOIN t6
-#  ORDER BY coalesce(t4.id,t5.id,t6.id);
-#
-do_test join8-issue-3 {set rc "Issue-3 'USING' unresolved"} {}
+reset_db
+load_static_extension db series
+do_execsql_test join8-3000 {
+  CREATE TABLE t1(id INTEGER PRIMARY KEY, a INT);
+  CREATE TABLE t2(id INTEGER PRIMARY KEY, b INT);
+  CREATE TABLE t3(id INTEGER PRIMARY KEY, c INT);
+  CREATE TABLE t4(id INTEGER PRIMARY KEY, d INT);
+  CREATE TABLE t5(id INTEGER PRIMARY KEY, e INT);
+  CREATE TABLE t6(id INTEGER PRIMARY KEY, f INT);
+  CREATE TABLE t7(id INTEGER PRIMARY KEY, g INT);
+  CREATE TABLE t8(id INTEGER PRIMARY KEY, h INT);
+  INSERT INTO t1 SELECT value, 1 FROM generate_series(1,256) WHERE value & 1;
+  INSERT INTO t2 SELECT value, 1 FROM generate_series(1,256) WHERE value & 2;
+  INSERT INTO t3 SELECT value, 1 FROM generate_series(1,256) WHERE value & 4;
+  INSERT INTO t4 SELECT value, 1 FROM generate_series(1,256) WHERE value & 8;
+  INSERT INTO t5 SELECT value, 1 FROM generate_series(1,256) WHERE value & 16;
+  INSERT INTO t6 SELECT value, 1 FROM generate_series(1,256) WHERE value & 32;
+  INSERT INTO t7 SELECT value, 1 FROM generate_series(1,256) WHERE value & 64;
+  INSERT INTO t8 SELECT value, 1 FROM generate_series(1,256) WHERE value & 128;
+  CREATE TABLE t9 AS
+    SELECT id, h, g, f, e, d, c, b, a
+      FROM t1
+      NATURAL FULL JOIN t2
+      NATURAL FULL JOIN t3
+      NATURAL FULL JOIN t4
+      NATURAL FULL JOIN t5
+      NATURAL FULL JOIN t6
+      NATURAL FULL JOIN t7
+      NATURAL FULL JOIN t8;
+} {}
+do_execsql_test join8-3010 {
+  SELECT count(*) FROM t9;
+} {255}
+do_execsql_test join8-3020 {
+  SELECT id, count(*) FROM t9 GROUP BY id HAVING count(*)!=1;
+} {}
+do_execsql_test join8-3030 {
+  UPDATE t9 SET a=0 WHERE a IS NULL;
+  UPDATE t9 SET b=0 WHERE b IS NULL;
+  UPDATE t9 SET c=0 WHERE c IS NULL;
+  UPDATE t9 SET d=0 WHERE d IS NULL;
+  UPDATE t9 SET e=0 WHERE e IS NULL;
+  UPDATE t9 SET f=0 WHERE f IS NULL;
+  UPDATE t9 SET g=0 WHERE g IS NULL;
+  UPDATE t9 SET h=0 WHERE h IS NULL;
+  SELECT count(*) FROM t9 WHERE id=128*h+64*g+32*f+16*e+8*d+4*c+2*b+a;
+} {255}
+do_execsql_test join8-3040 {
+  SELECT * FROM t9 WHERE id<>128*h+64*g+32*f+16*e+8*d+4*c+2*b+a;
+} {}
+
 
 finish_test
diff --git a/test/join9.test b/test/join9.test
index ad0545942..69f7dcbd5 100644
--- a/test/join9.test
+++ b/test/join9.test
@@ -28,6 +28,18 @@ foreach {id schema} {
                                (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 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);
+  }
 } {
   reset_db
   db nullvalue -
@@ -161,5 +173,109 @@ foreach {id schema} {
     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 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 
+  }
 }  
 finish_test