Imported from /home/lorry/working-area/delta_berkeleydb/db-6.1.23.tar.gz.HEADdb-6.1.23master

1 files changed, 91 insertions, 3 deletions

diff --git a/lang/sql/sqlite/test/subquery.test b/lang/sql/sqlite/test/subquery.test
index 169cedac..93c3f28d 100644
--- a/lang/sql/sqlite/test/subquery.test
+++ b/lang/sql/sqlite/test/subquery.test
@@ -241,8 +241,11 @@ do_test subquery-2.5.3.1 {
 } {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;
@@ -331,12 +334,97 @@ do_test subquery-3.3.5 {
   }
 } {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 {