diff options
author | Hans-Christoph Steiner <hans@eds.org> | 2012-09-20 18:34:38 -0400 |
---|---|---|
committer | Hans-Christoph Steiner <hans@eds.org> | 2012-09-20 18:34:38 -0400 |
commit | 487e15dc239ccdb3344d1c99ce120e872bab4a74 (patch) | |
tree | c986d492f6092ca7b4401d91515f74daed17fae2 /test/subquery.test | |
parent | 7bb481fda9ecb134804b49c2ce77ca28f7eea583 (diff) |
Imported Upstream version 2.0.6
Diffstat (limited to 'test/subquery.test')
-rw-r--r-- | test/subquery.test | 85 |
1 files changed, 85 insertions, 0 deletions
diff --git a/test/subquery.test b/test/subquery.test index 169ceda..d9d2952 100644 --- a/test/subquery.test +++ b/test/subquery.test @@ -331,6 +331,91 @@ 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 |