Add support for RIGHT and FULL OUTER JOINs.

FossilOrigin-Name: fa9d206f904280e3eafc6f4ba6c0c7325948364c62eeeb9f0fdc5825d622ec35

16 files changed, 449 insertions, 37 deletions

diff --git a/test/affinity3.test b/test/affinity3.test
index ef1533a8f..48942de72 100644
--- a/test/affinity3.test
+++ b/test/affinity3.test
@@ -30,11 +30,24 @@ do_execsql_test affinity3-100 {
   FROM customer c
   LEFT JOIN apr i ON i.id=c.id;
   
+  CREATE VIEW v1rj AS
+  SELECT c.id, i.apr
+  FROM apr i
+  RIGHT JOIN customer c ON i.id=c.id;
+  
   CREATE VIEW v2 AS
   SELECT c.id, v1.apr
   FROM customer c
   LEFT JOIN v1 ON v1.id=c.id;
   
+  CREATE VIEW v2rj AS
+  SELECT c.id, v1.apr
+  FROM v1 RIGHT JOIN customer c ON v1.id=c.id;
+  
+  CREATE VIEW v2rjrj AS
+  SELECT c.id, v1rj.apr
+  FROM v1rj RIGHT JOIN customer c ON v1rj.id=c.id;
+  
   INSERT INTO customer (id) VALUES (1);
   INSERT INTO apr (id, apr) VALUES (1, 12);
   INSERT INTO customer (id) VALUES (2);
@@ -44,16 +57,35 @@ do_execsql_test affinity3-110 {
   PRAGMA automatic_index=ON;
   SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
 } {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-111 {
+  PRAGMA automatic_index=ON;
+  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1rj;
+} {1 0.12 real 2 0.1201 real}
 do_execsql_test affinity3-120 {
   SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
 } {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-121 {
+  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rj;
+} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-122 {
+  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rjrj;
+} {1 0.12 real 2 0.1201 real}
 do_execsql_test affinity3-130 {
   PRAGMA automatic_index=OFF;
   SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
 } {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-131 {
+  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1rj;
+} {1 0.12 real 2 0.1201 real}
 do_execsql_test affinity3-140 {
   SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
 } {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-141 {
+  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rj;
+} {1 0.12 real 2 0.1201 real}
+do_execsql_test affinity3-142 {
+  SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2rjrj;
+} {1 0.12 real 2 0.1201 real}
 
 # Ticket https://www.sqlite.org/src/info/7ffd1ca1d2ad4ecf  (2017-01-16)
 # Incorrect affinity when using automatic indexes 
diff --git a/test/aggnested.test b/test/aggnested.test
index 35d5f1e3a..1b8b60880 100644
--- a/test/aggnested.test
+++ b/test/aggnested.test
@@ -137,6 +137,17 @@ do_test aggnested-3.1 {
        GROUP BY curr.id1);
   }
 } {1 1}
+do_test aggnested-3.1-rj {
+  db eval {
+    SELECT
+     (SELECT sum(value2==xyz) FROM t2)
+    FROM
+     (SELECT curr.value1 as xyz
+        FROM t1 AS other RIGHT JOIN t1 AS curr
+       GROUP BY curr.id1);
+  }
+} {1 1}
+
 do_test aggnested-3.2 {
   db eval {
     DROP TABLE IF EXISTS t1;
diff --git a/test/autoindex1.test b/test/autoindex1.test
index 6b437f186..2cd490040 100644
--- a/test/autoindex1.test
+++ b/test/autoindex1.test
@@ -283,7 +283,7 @@ do_eqp_test autoindex1-600a {
   |  `--CORRELATED SCALAR SUBQUERY xxxxxx
   |     `--SEARCH later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
   |--SCAN x USING INDEX sheep_reg_flock_index
-  `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?)
+  `--SEARCH y USING AUTOMATIC COVERING INDEX (sheep_no=?) LEFT-JOIN
 }
 
 
diff --git a/test/autoindex4.test b/test/autoindex4.test
index 24604af58..d9ab783e4 100644
--- a/test/autoindex4.test
+++ b/test/autoindex4.test
@@ -32,12 +32,21 @@ do_execsql_test autoindex4-1.1 {
 do_execsql_test autoindex4-1.2 {
   SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555;
 } {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
+do_execsql_test autoindex4-1.2-rj {
+  SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON a=234 AND x=555;
+} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
 do_execsql_test autoindex4-1.3 {
   SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234;
 } {234 def {} {} | 234 ghi {} {} |}
+do_execsql_test autoindex4-1.3-rj {
+  SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 ON x=555 WHERE a=234;
+} {234 def {} {} | 234 ghi {} {} |}
 do_execsql_test autoindex4-1.4 {
   SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555;
 } {}
+do_execsql_test autoindex4-1.4-rj {
+  SELECT t1.*, t2.*, '|' FROM t2 RIGHT JOIN t1 WHERE a=234 AND x=555;
+} {}
 
 
 do_execsql_test autoindex4-2.0 {
@@ -69,6 +78,14 @@ do_execsql_test autoindex4-3.0 {
     ORDER BY Items.ItemName;
 } {Item1 Item2}
 do_execsql_test autoindex4-3.1 {
+  SELECT Items.ItemName
+    FROM A
+      RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
+      LEFT JOIN B ON (B.Name = Items.ItemName)
+    WHERE Items.Name = 'Parent'
+    ORDER BY Items.ItemName;
+} {Item1 Item2}
+do_execsql_test autoindex4-3.10 {
   CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
   
   SELECT Items.ItemName
@@ -78,6 +95,14 @@ do_execsql_test autoindex4-3.1 {
     WHERE Items.Name = 'Parent'
     ORDER BY Items.ItemName;
 } {Item1 Item2}
+do_execsql_test autoindex4-3.11 {
+  SELECT Items.ItemName
+    FROM A
+      RIGHT JOIN Items ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
+      LEFT JOIN B ON (B.Name = Items.ItemName)
+    WHERE Items.Name = 'Parent'
+    ORDER BY Items.ItemName;
+} {Item1 Item2}
 
 # 2021-11-30 - Enhancement to help the automatic index mechanism to
 # create a partial index more often.
diff --git a/test/btree01.test b/test/btree01.test
index 9c309760d..6e4717ae6 100644
--- a/test/btree01.test
+++ b/test/btree01.test
@@ -148,6 +148,9 @@ do_execsql_test btree01-2.1 {
   INSERT INTO t2(y) VALUES(198),(187),(100);
   SELECT y, c FROM t2 LEFT JOIN t1 ON y=a ORDER BY x;
 } {198 99 187 {} 100 50}
+do_execsql_test btree01-2.2 {
+  SELECT y, c FROM t1 RIGHT JOIN t2 ON y=a ORDER BY x;
+} {198 99 187 {} 100 50}
 
 
 finish_test
diff --git a/test/collate2.test b/test/collate2.test
index d5aadb4eb..281aa3570 100644
--- a/test/collate2.test
+++ b/test/collate2.test
@@ -684,16 +684,34 @@ do_test collate2-5.3 {
     SELECT collate2t1.b FROM collate2t2 NATURAL JOIN collate2t1;
   }
 } {aa}
-do_test collate2-5.4 {
+do_test collate2-5.4.1 {
   execsql {
-    SELECT collate2t2.b FROM collate2t1 LEFT OUTER JOIN collate2t2 USING (b) order by collate2t1.oid;
+    SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 USING (b) order by collate2t1.oid;
   }
 } {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
-do_test collate2-5.5 {
+do_test collate2-5.4.2 {
+  execsql {
+    SELECT collate2t2.b FROM collate2t2 RIGHT JOIN collate2t1 ON collate2t1.b=collate2t2.b
+     ORDER BY collate2t1.oid;
+  }
+} {{} aa {} {} {} aa {} {} {} aa {} {} {} aa {} {} {}}
+do_test collate2-5.4.3 {
+  execsql {
+    SELECT collate2t2.b FROM collate2t1 LEFT JOIN collate2t2 ON collate2t2.b=collate2t1.b
+     ORDER BY collate2t1.oid;
+  }
+} {{} aa {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}}
+do_test collate2-5.5.1 {
   execsql {
     SELECT collate2t1.b, collate2t2.b FROM collate2t2 LEFT OUTER JOIN collate2t1 USING (b);
   }
 } {aa aa}
+do_test collate2-5.5.2 {
+  execsql {
+    SELECT collate2t1.b, collate2t2.b
+      FROM collate2t1 RIGHT JOIN collate2t2 ON collate2t2.b=collate2t1.b
+  }
+} {aa aa}
 
 do_execsql_test 6.1 {
   CREATE TABLE t1(x);
diff --git a/test/fts3join.test b/test/fts3join.test
index 9a7994193..3333b1ab4 100644
--- a/test/fts3join.test
+++ b/test/fts3join.test
@@ -100,7 +100,7 @@ do_eqp_test 4.2 {
   |--MATERIALIZE rr
   |  `--SCAN ft4 VIRTUAL TABLE INDEX 3:
   |--SCAN t4
-  `--SEARCH rr USING AUTOMATIC COVERING INDEX (docid=?)
+  `--SEARCH rr USING AUTOMATIC COVERING INDEX (docid=?) LEFT-JOIN
 }
 
 finish_test
diff --git a/test/index6.test b/test/index6.test
index 7eed6a47e..1ae2ee875 100644
--- a/test/index6.test
+++ b/test/index6.test
@@ -320,7 +320,7 @@ do_eqp_test index6-8.1 {
 } {
   QUERY PLAN
   |--SCAN t8a
-  `--SEARCH t8b USING INDEX i8c (y=?)
+  `--SEARCH t8b USING INDEX i8c (y=?) LEFT-JOIN
 }
 
 do_execsql_test index6-8.2 {
diff --git a/test/join.test b/test/join.test
index f48a1a149..d6e775436 100644
--- a/test/join.test
+++ b/test/join.test
@@ -272,11 +272,13 @@ do_test join-2.2 {
     SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
   }
 } {1 2 3 {} 2 3 4 1 3 4 5 2}
-do_test join-2.3 {
-  catchsql {
-    SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
-  }
-} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+
+#do_test join-2.3 {
+#  catchsql {
+#    SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
+#  }
+#} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+
 do_test join-2.4 {
   execsql {
     SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
@@ -330,32 +332,32 @@ do_test join-3.7 {
   catchsql {
     SELECT * FROM t1 INNER OUTER JOIN t2;
   }
-} {1 {unknown or unsupported join type: INNER OUTER}}
+} {1 {unknown join type: INNER OUTER}}
 do_test join-3.8 {
   catchsql {
     SELECT * FROM t1 INNER OUTER CROSS JOIN t2;
   }
-} {1 {unknown or unsupported join type: INNER OUTER CROSS}}
+} {1 {unknown join type: INNER OUTER CROSS}}
 do_test join-3.9 {
   catchsql {
     SELECT * FROM t1 OUTER NATURAL INNER JOIN t2;
   }
-} {1 {unknown or unsupported join type: OUTER NATURAL INNER}}
+} {1 {unknown join type: OUTER NATURAL INNER}}
 do_test join-3.10 {
   catchsql {
     SELECT * FROM t1 LEFT BOGUS JOIN t2;
   }
-} {1 {unknown or unsupported join type: LEFT BOGUS}}
+} {1 {unknown join type: LEFT BOGUS}}
 do_test join-3.11 {
   catchsql {
     SELECT * FROM t1 INNER BOGUS CROSS JOIN t2;
   }
-} {1 {unknown or unsupported join type: INNER BOGUS CROSS}}
+} {1 {unknown join type: INNER BOGUS CROSS}}
 do_test join-3.12 {
   catchsql {
     SELECT * FROM t1 NATURAL AWK SED JOIN t2;
   }
-} {1 {unknown or unsupported join type: NATURAL AWK SED}}
+} {1 {unknown join type: NATURAL AWK SED}}
 
 do_test join-4.1 {
   execsql {
diff --git a/test/join2.test b/test/join2.test
index 170000ca2..4142fd15b 100644
--- a/test/join2.test
+++ b/test/join2.test
@@ -63,6 +63,12 @@ do_test join2-1.6 {
       t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
   }
 } {1 11 111 1111}
+do_test join2-1.6-rj {
+  execsql {
+    SELECT * FROM
+      t2 NATURAL RIGHT OUTER JOIN t1 NATURAL JOIN t3
+  }
+} {11 111 1 1111}
 ifcapable subquery {
   do_test join2-1.7 {
     execsql {
@@ -70,6 +76,12 @@ ifcapable subquery {
         t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
     }
   } {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
+    }
+  } {11 111 1111 1 {} {} {} 2 {} {} {} 3}
 }
 
 #-------------------------------------------------------------------------
@@ -88,6 +100,9 @@ do_execsql_test 2.0 {
 do_catchsql_test 2.1 {
   SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
 } {1 {ON clause references tables to its right}}
+do_catchsql_test 2.1b {
+  SELECT * FROM aa RIGHT JOIN cc ON (a=b) JOIN bb ON (b=coalesce(c,1));
+} {1 {ON clause references tables to its right}}
 do_catchsql_test 2.2 {
   SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);
 } {0 {one one one}}
@@ -114,7 +129,7 @@ do_eqp_test 3.1 {
 } {
   QUERY PLAN
   |--SCAN t1
-  `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
+  `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 }
 
 do_eqp_test 3.2 {
@@ -122,7 +137,7 @@ do_eqp_test 3.2 {
 } {
   QUERY PLAN
   |--SCAN t1
-  `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
+  `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 }
 
 #-------------------------------------------------------------------------
@@ -162,15 +177,15 @@ do_eqp_test 4.1.5 {
 } {
   QUERY PLAN
   |--SCAN c1
-  |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?)
-  `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?)
+  |--SEARCH c2 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
+  `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 }
 do_eqp_test 4.1.6 {
   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 } {
   QUERY PLAN
   |--SCAN c1
-  `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?)
+  `--SEARCH c3 USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
 }
 
 do_execsql_test 4.2.0 {
@@ -209,15 +224,15 @@ do_eqp_test 4.2.5 {
 } {
   QUERY PLAN
   |--SCAN c1
-  |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?)
-  `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
+  |--SEARCH c2 USING INDEX sqlite_autoindex_c2_1 (k=?) LEFT-JOIN
+  `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN
 }
 do_eqp_test 4.2.6 {
   SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
 } {
   QUERY PLAN
   |--SCAN c1
-  `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
+  `--SEARCH c3 USING INDEX sqlite_autoindex_c3_1 (k=?) LEFT-JOIN
 }
 
 # 2017-11-23 (Thanksgiving day)
diff --git a/test/join5.test b/test/join5.test
index a5cedd8cb..e4fd9cd87 100644
--- a/test/join5.test
+++ b/test/join5.test
@@ -280,9 +280,9 @@ do_eqp_test 7.2 {
   |--SCAN t1
   `--MULTI-INDEX OR
      |--INDEX 1
-     |  `--SEARCH t2 USING INDEX t2xy (x=? AND y=?)
+     |  `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
      `--INDEX 2
-        `--SEARCH t2 USING INDEX t2xy (x=? AND y=?)
+        `--SEARCH t2 USING INDEX t2xy (x=? AND y=?) LEFT-JOIN
 }
 
 do_execsql_test 7.3 {
@@ -303,7 +303,7 @@ do_eqp_test 7.4 {
 } {
   QUERY PLAN
   |--SCAN t3
-  `--SEARCH t4 USING INDEX t4xz (x=?)
+  `--SEARCH t4 USING INDEX t4xz (x=?) LEFT-JOIN
 } 
 do_eqp_test 7.4b {
   SELECT * FROM t3 CROSS JOIN t4 ON (t4.x = t3.x) WHERE (+t4.y = ? OR t4.z = ?);
diff --git a/test/join7.test b/test/join7.test
new file mode 100644
index 000000000..a0507be1d
--- /dev/null
+++ b/test/join7.test
@@ -0,0 +1,273 @@
+# 2022-04-09
+#
+# 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 t1(a INT, b INT);
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE INDEX t1a ON t1(a);
+    CREATE TABLE t2(c INT, d INT);
+    INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+    CREATE INDEX t2c ON t2(c);
+    CREATE VIEW dual(dummy) AS VALUES('x');
+  }
+  2 {
+    CREATE TABLE t1(a INT, b INT);
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE INDEX t1ab ON t1(a,b);
+    CREATE TABLE t2(c INT, d INT);
+    INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+    CREATE INDEX t2cd ON t2(c,d);
+    CREATE VIEW dual(dummy) AS VALUES('x');
+  }
+  3 {
+    CREATE TABLE t1(a INT, b INT);
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE INDEX t1a ON t1(a);
+    CREATE TABLE t2(c INT, d INT PRIMARY KEY) WITHOUT ROWID;
+    INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+    CREATE INDEX t2c ON t2(c);
+    CREATE VIEW dual(dummy) AS VALUES('x');
+  }
+  4 {
+    CREATE TABLE t1(a INT, b INT);
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
+    INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+    CREATE VIEW dual(dummy) AS VALUES('x');
+  }
+  5 {
+    CREATE TABLE t1(a INT, b INT);
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE TABLE t2(c INT PRIMARY KEY, d INT) WITHOUT ROWID;
+    INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+    CREATE VIEW dual(dummy) AS VALUES('x');
+  }
+  6 {
+    CREATE TABLE t1(a INT, b INT);
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE VIEW t2(c,d) AS VALUES(3,33),(4,44),(5,55);
+    CREATE VIEW dual(dummy) AS VALUES('x');
+  }
+  7 {
+    CREATE VIEW t1(a,b) AS VALUES(1,2),(1,3),(1,4);
+    CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
+    INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+    CREATE VIEW dual(dummy) AS VALUES('x');
+  }
+  8 {
+    CREATE TABLE t1(a INT, b INT);
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE TABLE t2(c INT, d INT);
+    INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
+    CREATE VIEW dual(dummy) AS VALUES('x');
+  }
+  9 {
+    CREATE TABLE t1(a INT, b INT);
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
+    CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
+    CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
+    INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
+    INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
+    CREATE TABLE dual(dummy TEXT);
+    INSERT INTO dual(dummy) VALUES('x');
+  }
+  10 {
+    CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a,b)) WITHOUT ROWID;
+    INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
+    CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
+    CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
+    CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
+    INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
+    INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
+    CREATE TABLE dual(dummy TEXT);
+    INSERT INTO dual(dummy) VALUES('x');
+  }
+} {
+  reset_db
+  db nullvalue NULL
+  do_execsql_test join7-$id.setup $schema {}
+  do_execsql_test join7-$id.10 {
+    SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+  } {
+    NULL 55
+    2    NULL
+    3    33
+    4    44
+  }
+  do_execsql_test join7-$id.20 {
+    SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+  } {
+    NULL  5
+    1     NULL
+    1     3
+    1     4
+  }
+  do_execsql_test join7-$id.30 {
+    SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.31 {
+    SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.40 {
+    SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.50 {
+    SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.60 {
+    SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+  } {
+    NULL NULL NULL 5    55
+    x    1    3    3    33
+    x    1    4    4    44
+  }
+  do_execsql_test join7-$id.70 {
+    SELECT t1.*, t2.* 
+      FROM t2 LEFT JOIN (dual JOIN t1 ON true) ON b=c ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.80 {
+    SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+  } {
+    NULL NULL NULL 5    55
+    x    1    3    3    33
+    x    1    4    4    44
+  }
+  do_execsql_test join7-$id.81 {
+    SELECT dual.*, t1.*, t2.*
+      FROM t1 CROSS JOIN dual RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
+  } {
+    NULL NULL NULL 5    55
+    x    1    3    3    33
+    x    1    4    4    44
+  }
+  do_execsql_test join7-$id.90 {
+    SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b;
+  } {
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.100 {
+    SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.101 {
+    SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND a=1 ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.110 {
+    SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b;
+  } {
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.111 {
+    SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE a=1 ORDER BY +b;
+  } {
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.115 {
+    SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c
+     WHERE a=1 OR a IS NULL ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.116 {
+    SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
+     WHERE a=1 OR a IS NULL ORDER BY +b;
+  } {
+    NULL NULL 5    55
+    1    2    NULL NULL
+    1    3    3    33
+    1    4    4    44
+  }
+  do_execsql_test join7-$id.120 {
+    SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d;
+  } {
+    NULL NULL 3    33
+    NULL NULL 4    44
+    NULL NULL 5    55
+  }
+  do_execsql_test join7-$id.130 {
+    SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d;
+  } {
+    NULL NULL 3    33
+    NULL NULL 4    44
+    NULL NULL 5    55
+    1    2    NULL NULL
+    1    3    NULL NULL
+    1    4    NULL NULL
+  }
+  do_execsql_test join7-$id.140 {
+    SELECT a, b, c, d
+      FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0 ORDER BY +b, +d;
+  } {
+    NULL NULL 3    33
+    NULL NULL 4    44
+    NULL NULL 5    55
+    1    2    NULL NULL
+    1    3    NULL NULL
+    1    4    NULL NULL
+  }
+  do_execsql_test join7-$id.141 {
+    SELECT a, b, c, d
+      FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0
+     ORDER BY +b, +d LIMIT 2 OFFSET 2
+  } {
+    NULL NULL 5    55
+    1    2    NULL NULL
+  }
+}  
+finish_test
diff --git a/test/join8.test b/test/join8.test
new file mode 100644
index 000000000..0854d9729
--- /dev/null
+++ b/test/join8.test
@@ -0,0 +1,24 @@
+# 2022-04-12
+#
+# 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 tests for RIGHT and FULL OUTER JOINs.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+db null NULL
+do_execsql_test join8-10 {
+  CREATE TABLE t1(a,b,c);
+  CREATE TABLE t2(x,y);
+  CREATE INDEX t2x ON t2(x);
+  SELECT avg(DISTINCT b) FROM (SELECT * FROM t2 LEFT RIGHT JOIN t1 ON c);
+} {NULL}
+finish_test
diff --git a/test/vtab6.test b/test/vtab6.test
index ab52c9f15..2ee5e2705 100644
--- a/test/vtab6.test
+++ b/test/vtab6.test
@@ -223,11 +223,11 @@ do_test vtab6-2.2 {
     SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
   }
 } {1 2 3 {} 2 3 4 1 3 4 5 2}
-do_test vtab6-2.3 {
-  catchsql {
-    SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
-  }
-} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
+#do_test vtab6-2.3 {
+#  catchsql {
+#    SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
+#  }
+#} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
 do_test vtab6-2.4 {
   execsql {
     SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
@@ -281,12 +281,12 @@ do_test vtab6-3.7 {
   catchsql {
     SELECT * FROM t1 INNER OUTER JOIN t2;
   }
-} {1 {unknown or unsupported join type: INNER OUTER}}
+} {1 {unknown join type: INNER OUTER}}
 do_test vtab6-3.7 {
   catchsql {
     SELECT * FROM t1 LEFT BOGUS JOIN t2;
   }
-} {1 {unknown or unsupported join type: LEFT BOGUS}}
+} {1 {unknown join type: LEFT BOGUS}}
 
 do_test vtab6-4.1 {
   execsql {
diff --git a/test/where.test b/test/where.test
index 8ee57b8b6..2f53f2cb4 100644
--- a/test/where.test
+++ b/test/where.test
@@ -1348,16 +1348,25 @@ do_execsql_test where-18.1 {
   INSERT INTO t181 VALUES(1);
   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL;
 } {1}
+do_execsql_test where-18.1rj {
+  SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c IS NULL;
+} {1}
 do_execsql_test where-18.2 {
   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
 } {1}
 do_execsql_test where-18.3 {
   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c;
 } {1}
+do_execsql_test where-18.3rj {
+  SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY c;
+} {1}
 do_execsql_test where-18.4 {
   INSERT INTO t181 VALUES(1),(1),(1),(1);
   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c;
 } {1}
+do_execsql_test where-18.4rj {
+  SELECT DISTINCT a FROM t182 RIGHT JOIN t181 ON a=b ORDER BY +c;
+} {1}
 do_execsql_test where-18.5 {
   INSERT INTO t181 VALUES(2);
   SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a;
diff --git a/test/where9.test b/test/where9.test
index 429708f1f..475788153 100644
--- a/test/where9.test
+++ b/test/where9.test
@@ -378,9 +378,9 @@ ifcapable explain {
     |--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
     `--MULTI-INDEX OR
        |--INDEX 1
-       |  `--SEARCH t2 USING INDEX t2d (d=?)
+       |  `--SEARCH t2 USING INDEX t2d (d=?) LEFT-JOIN
        `--INDEX 2
-          `--SEARCH t2 USING COVERING INDEX t2f (f=?)
+          `--SEARCH t2 USING COVERING INDEX t2f (f=?) LEFT-JOIN
   }]
 }