Import SQLite 3.8.7.4.

third_party/sqlite/src/test/subquery.test

 # 2005 January 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 223 matching lines @@
 do_test subquery-2.5.3.1 {
   # The t4i index cannot be used to resolve the "x IN (...)" constraint
   # because the constraint has integer affinity but t4i has text affinity.
   execsql {
     CREATE INDEX t4i ON t4(x);
     SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
   }
 } {10.0}
 do_test subquery-2.5.3.2 {
   # Verify that the t4i index was not used in the previous query
-  set ::sqlite_query_plan
-} {t4 {}}
+  execsql {
+    EXPLAIN QUERY PLAN
+    SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
+  }
+} {~/t4i/}
 do_test subquery-2.5.4 {
   execsql {
     DROP TABLE t3;
     DROP TABLE t4;
   }
 } {}
 
 #------------------------------------------------------------------
 # The following test cases - subquery-3.* - test tickets that
 # were raised during development of correlated subqueries.
@@ skipping 68 matching lines @@
   execsql {
     SELECT a, (SELECT (SELECT d FROM t2 WHERE a=c)) FROM t1 GROUP BY a;
   }
 } {1 one 2 two}
 do_test subquery-3.3.5 {
   execsql {
     SELECT a, (SELECT count(*) FROM t2 WHERE a=c) FROM t1;
   }
 } {1 1 2 1}
 
+# The following tests check for aggregate subqueries in an aggregate
+# query.
+#
+do_test subquery-3.4.1 {
+  execsql {
+    CREATE TABLE t34(x,y);
+    INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);
+    SELECT a.x, avg(a.y)
+      FROM t34 AS a
+     GROUP BY a.x
+     HAVING NOT EXISTS( SELECT b.x, avg(b.y)
+                          FROM t34 AS b
+                         GROUP BY b.x
+                         HAVING avg(a.y) > avg(b.y));
+  }
+} {107 4.0}
+do_test subquery-3.4.2 {
+  execsql {
+    SELECT a.x, avg(a.y) AS avg1
+      FROM t34 AS a
+     GROUP BY a.x
+     HAVING NOT EXISTS( SELECT b.x, avg(b.y) AS avg2
+                          FROM t34 AS b
+                         GROUP BY b.x
+                         HAVING avg1 > avg2);
+  }
+} {107 4.0}
+do_test subquery-3.4.3 {
+  execsql {
+    SELECT
+       a.x,
+       avg(a.y),
+       NOT EXISTS ( SELECT b.x, avg(b.y)
+                      FROM t34 AS b
+                      GROUP BY b.x
+                     HAVING avg(a.y) > avg(b.y)),
+       EXISTS ( SELECT c.x, avg(c.y)
+                  FROM t34 AS c
+                  GROUP BY c.x
+                 HAVING avg(a.y) > avg(c.y))
+      FROM t34 AS a
+     GROUP BY a.x
+     ORDER BY a.x;
+  }
+} {106 4.5 0 1 107 4.0 1 0}
+
+do_test subquery-3.5.1 {
+  execsql {
+    CREATE TABLE t35a(x); INSERT INTO t35a VALUES(1),(2),(3);
+    CREATE TABLE t35b(y); INSERT INTO t35b VALUES(98), (99);
+    SELECT max((SELECT avg(y) FROM t35b)) FROM t35a;
+  }
+} {98.5}
+do_test subquery-3.5.2 {
+  execsql {
+    SELECT max((SELECT count(y) FROM t35b)) FROM t35a;
+  }
+} {2}
+do_test subquery-3.5.3 {
+  execsql {
+    SELECT max((SELECT count() FROM t35b)) FROM t35a;
+  }
+} {2}
+do_test subquery-3.5.4 {
+  catchsql {
+    SELECT max((SELECT count(x) FROM t35b)) FROM t35a;
+  }
+} {1 {misuse of aggregate: count()}}
+do_test subquery-3.5.5 {
+  catchsql {
+    SELECT max((SELECT count(x) FROM t35b)) FROM t35a;
+  }
+} {1 {misuse of aggregate: count()}}
+do_test subquery-3.5.6 {
+  catchsql {
+    SELECT max((SELECT a FROM (SELECT count(x) AS a FROM t35b))) FROM t35a;
+  }
+} {1 {misuse of aggregate: count()}}
+do_test subquery-3.5.7 {
+  execsql {
+    SELECT max((SELECT a FROM (SELECT count(y) AS a FROM t35b))) FROM t35a;
+  }
+} {2}
+
+
 #------------------------------------------------------------------
 # These tests - subquery-4.* - use the TCL statement cache to try 
 # and expose bugs to do with re-using statements that have been 
 # passed to sqlite3_reset().
 #
-# One problem was that VDBE memory cells were not being initialised
+# One problem was that VDBE memory cells were not being initialized
 # to NULL on the second and subsequent executions.
 #
 do_test subquery-4.1.1 {
   execsql {
     SELECT (SELECT a FROM t1);
   }
 } {1}
 do_test subquery-4.2 {
   execsql {
     DELETE FROM t1;
@@ skipping 140 matching lines @@
   }
 } {30101 30102 30103}
 do_test subquery-7.11 {
   execsql {
     SELECT (SELECT (SELECT max(c7)+max(c8)+max(c9) FROM t9) FROM t8) FROM t7
   }
 } {30303}
 }  ;############# Disabled
 
 finish_test