1 files changed, 49 insertions, 0 deletions
diff --git a/test/join.test b/test/join.test
index 19a061d46..391e0681c 100644
--- a/test/join.test
+++ b/test/join.test
@@ -975,4 +975,53 @@ do_execsql_test join-21.10 {
   SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
 } {13 1 {} 14 1 {} 23 1 {} 24 1 {}}
 
+# 2019-12-18 problem with a LEFT JOIN where the RHS is a view.
+# Detected by Yongheng and Rui.
+# Follows from the optimization attempt of check-in 41c27bc0ff1d3135
+# on 2017-04-18
+#
+reset_db
+do_execsql_test join-22.10 {
+  CREATE TABLE t0(a, b);
+  CREATE INDEX t0a ON t0(a);
+  INSERT INTO t0 VALUES(10,10),(10,11),(10,12);
+  SELECT DISTINCT c FROM t0 LEFT JOIN (SELECT a+1 AS c FROM t0) ORDER BY c ;
+} {11}
+
+# 2019-12-22 ticket 7929c1efb2d67e98
+#
+reset_db
+do_execsql_test join-23.10 {
+  CREATE TABLE t0(c0);
+  INSERT INTO t0(c0) VALUES(123);
+  CREATE VIEW v0(c0) AS SELECT 0 GROUP BY 1;
+  SELECT t0.c0, v0.c0, vt0.name
+   FROM v0, t0 LEFT JOIN pragma_table_info('t0') AS vt0
+     ON vt0.name LIKE 'c0'
+   WHERE v0.c0 == 0;
+} {123 0 c0}
+
+#-------------------------------------------------------------------------
+reset_db
+do_execsql_test join-24.1 {
+  CREATE TABLE t1(a PRIMARY KEY, x);
+  CREATE TABLE t2(b INT);
+  CREATE INDEX t1aa ON t1(a, a);
+
+  INSERT INTO t1 VALUES('abc', 'def');
+  INSERT INTO t2 VALUES(1);
+}
+
+do_execsql_test join-24.2 {
+  SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='def';
+} {1 abc def}
+do_execsql_test join-24.3 {
+  SELECT * FROM t2 JOIN t1 WHERE a='abc' AND x='abc';
+} {}
+
+do_execsql_test join-24.2 {
+  SELECT * FROM t2 LEFT JOIN t1 ON a=0 WHERE (x='x' OR x IS NULL);
+} {1 {} {}}
+
 finish_test
+