New test cases. One of the new test cases shows a flaw in the base design

of RIGHT JOIN.

FossilOrigin-Name: 4c3ce6475a67b1e207a5b63d9e2d38ee5bc45c899ad00ee327f292bf59039b9c

4 files changed, 110 insertions, 12 deletions

diff --git a/manifest b/manifest
index a8f3238cc..6220e0f07 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C RIGHT\sJOIN\sUSING\snow\sappears\sto\swork\sthe\ssame\sas\sPG-14.\s\sLegacy\stests\spass.
-D 2022-04-16T12:40:52.845
+C New\stest\scases.\s\sOne\sof\sthe\snew\stest\scases\sshows\sa\sflaw\sin\sthe\sbase\sdesign\nof\sRIGHT\sJOIN.
+D 2022-04-16T13:55:48.695
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -550,7 +550,7 @@ F src/pragma.h e690a356c18e98414d2e870ea791c1be1545a714ba623719deb63f7f226d8bb7
 F src/prepare.c fd940149c691684e7c1073c3787a7170e44852b02d1275d2e30a5b58e89cfcaf
 F src/printf.c 05d8dfd2018bc4fc3ddb8b37eb97ccef7abf985643fa1caebdcf2916ca90fa32
 F src/random.c 097dc8b31b8fba5a9aca1697aeb9fd82078ec91be734c16bffda620ced7ab83c
-F src/resolve.c ed18f81f424c473e37b6ab7dcd3d0a0e2198590a4342d4f2b2a999e94ff0c97b
+F src/resolve.c 58b5c54b7e5cd7101b57901f9039dee86224b6a93699a5e8639f402aff43e7cc
 F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92
 F src/select.c d273657c2958eb5eee34d68920796b6e0214034dd3a2a88dc8e6dc92a4804271
 F src/shell.c.in eb7f10d5e2c47bd014d92ec5db1def21fcc1ed56ffaaa4ee715b6c37c370b47f
@@ -1148,7 +1148,7 @@ F test/join5.test c4df54e2e204d7f1417bfbdd21ca324b4b07415c647595cc47798eacfddc96
 F test/join6.test f809c025fa253f9e150c0e9afd4cef8813257bceeb6f46e04041228c9403cc2c
 F test/join7.test e5c9b1b729d7e1d0b4195e99833e0ff0cf2d88e7fdd32b49af1044f4c76f72d9
 F test/join8.test 9731b16dbb4309a980ba4adcb4ec31277626230300f819296ffc753c564b2011
-F test/join9.test ab637b0983877c802b02c7cba71c8fc849bcff55edb819bea31ad8aade3e7dbc
+F test/join9.test 195c9986d1d1a9840627d610056718c6747e3e6bbc230ac711fe3c4465aa21c7
 F test/journal1.test c7b768041b7f494471531e17abc2f4f5ebf9e5096984f43ed17c4eb80ba34497
 F test/journal2.test 9dac6b4ba0ca79c3b21446bbae993a462c2397c4
 F test/journal3.test 7c3cf23ffc77db06601c1fcfc9743de8441cb77db9d1aa931863d94f5ffa140e
@@ -1948,8 +1948,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 719c8d920b435b0f234d47467a2abe2941ab7d55ecca886e211cfce577ae648c
-R c3fdd17a4836973d7440bcccabeca593
+P 5bfb862419541de955dae35fc91f6e9fc70e2744bb786d49fa26bede80b9091d
+R 567fa939c6a9dfdced52167ff4c7578b
 U drh
-Z da7116bcca24d6303c79a8dd758b8403
+Z 987f243665c64e4fbbc2d1f9cc8152ec
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index a977f8862..37ed90638 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-5bfb862419541de955dae35fc91f6e9fc70e2744bb786d49fa26bede80b9091d
\ No newline at end of file
+4c3ce6475a67b1e207a5b63d9e2d38ee5bc45c899ad00ee327f292bf59039b9c
\ No newline at end of file
diff --git a/src/resolve.c b/src/resolve.c
index d1698b127..09b16f1ad 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -684,7 +684,9 @@ static int lookupName(
     const char *zErr;
     if( pFJMatch ){
       if( pFJMatch->nExpr==cnt-1 ){
-        if( !ExprHasProperty(pExpr,(EP_TokenOnly|EP_Leaf)) ){
+        if( ExprHasProperty(pExpr,EP_Leaf) ){
+          ExprClearProperty(pExpr,EP_Leaf);
+        }else{
           sqlite3ExprDelete(db, pExpr->pLeft);
           pExpr->pLeft = 0;
           sqlite3ExprDelete(db, pExpr->pRight);
diff --git a/test/join9.test b/test/join9.test
index fde5436ea..ad0545942 100644
--- a/test/join9.test
+++ b/test/join9.test
@@ -17,9 +17,12 @@ 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');
@@ -29,7 +32,20 @@ foreach {id schema} {
   reset_db
   db nullvalue -
   do_execsql_test join9-$id.setup $schema {}
-  do_execsql_test join9-$id.10 {
+
+  # 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;
@@ -39,7 +55,54 @@ foreach {id schema} {
     6   cindy  -       -    6   -   - 
     8   dave   -       -    8   -   - 
   }
-  do_execsql_test join9-$id.20 {
+  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;
@@ -50,9 +113,42 @@ foreach {id schema} {
     5   -    blue    555   -   5   5 
     9   -    -       999   -   -   9 
   }
-  do_execsql_test join9-$id.30 {
+
+  # 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