Import SQLite 3.10.2.

third_party/sqlite/src/test/join5.test

 # 2005 September 19
 #
 # 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.
 #
 #***********************************************************************
@@ skipping 143 matching lines @@
   CREATE TABLE x1(a);
   INSERT INTO x1 VALUES(1);
   CREATE TABLE x2(b NOT NULL);
   CREATE TABLE x3(c, d);
   INSERT INTO x3 VALUES('a', NULL);
   INSERT INTO x3 VALUES('b', NULL);
   INSERT INTO x3 VALUES('c', NULL);
   SELECT * FROM x1 LEFT JOIN x2 JOIN x3 WHERE x3.d = x2.b;
 } {}
 
+# Ticket https://www.sqlite.org/src/tktview/c2a19d81652f40568c770c43 on
+# 2015-08-20.  LEFT JOIN and the push-down optimization.
+#
+do_execsql_test join6-4.1 {
+  SELECT *
+  FROM (
+      SELECT 'apple' fruit
+      UNION ALL SELECT 'banana'
+  ) a
+  JOIN (
+      SELECT 'apple' fruit
+      UNION ALL SELECT 'banana'
+  ) b ON a.fruit=b.fruit
+  LEFT JOIN (
+      SELECT 1 isyellow
+  ) c ON b.fruit='banana';
+} {apple apple {} banana banana 1}
+do_execsql_test join6-4.2 {
+  SELECT *
+    FROM (SELECT 'apple' fruit UNION ALL SELECT 'banana')
+         LEFT JOIN (SELECT 1) ON fruit='banana';
+} {apple {} banana 1}
+
 finish_test