test/aggnested.test

   1 # 2012-08-23
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 # This file implements regression tests for SQLite library.
  12 #
  13 # This file implements tests for processing aggregate queries with
  14 # subqueries in which the subqueries hold the aggregate functions
  15 # or in which the subqueries are themselves aggregate queries
  16 #
  17
  18 set testdir [file dirname $argv0]
  19 source $testdir/tester.tcl
  20 set testprefix aggnested
  21
  22 do_test aggnested-1.1 {
  23   db eval {
  24     CREATE TABLE t1(a1 INTEGER);
  25     INSERT INTO t1 VALUES(1), (2), (3);
  26     CREATE TABLE t2(b1 INTEGER);
  27     INSERT INTO t2 VALUES(4), (5);
  28     SELECT (SELECT group_concat(a1,'x') FROM t2) FROM t1;
  29   }
  30 } {1x2x3}
  31 do_test aggnested-1.2 {
  32   db eval {
  33     SELECT
  34      (SELECT group_concat(a1,'x') || '-' || group_concat(b1,'y') FROM t2)
  35     FROM t1;
  36   }
  37 } {1x2x3-4y5}
  38 do_test aggnested-1.3 {
  39   db eval {
  40     SELECT (SELECT group_concat(b1,a1) FROM t2) FROM t1;
  41   }
  42 } {415 425 435}
  43 do_test aggnested-1.4 {
  44   db eval {
  45     SELECT (SELECT group_concat(a1,b1) FROM t2) FROM t1;
  46   }
  47 } {151 252 353}
  48
  49
  50 # This test case is a copy of the one in
  51 # http://www.mail-archive.com/sqlite-users@sqlite.org/msg70787.html
  52 #
  53 do_test aggnested-2.0 {
  54   sqlite3 db2 :memory:
  55   db2 eval {
  56     CREATE TABLE t1 (A1 INTEGER NOT NULL,A2 INTEGER NOT NULL,A3 INTEGER NOT
  57     NULL,A4 INTEGER NOT NULL,PRIMARY KEY(A1));
  58     REPLACE INTO t1 VALUES(1,11,111,1111);
  59     REPLACE INTO t1 VALUES(2,22,222,2222);
  60     REPLACE INTO t1 VALUES(3,33,333,3333);
  61     CREATE TABLE t2 (B1 INTEGER NOT NULL,B2 INTEGER NOT NULL,B3 INTEGER NOT
  62     NULL,B4 INTEGER NOT NULL,PRIMARY KEY(B1));
  63     REPLACE INTO t2 VALUES(1,88,888,8888);
  64     REPLACE INTO t2 VALUES(2,99,999,9999);
  65     SELECT (SELECT GROUP_CONCAT(CASE WHEN a1=1 THEN'A' ELSE 'B' END) FROM t2),
  66             t1.*
  67     FROM t1;
  68   }
  69 } {A,B,B 1 11 111 1111}
  70 db2 close
  71
  72 ##################### Test cases for ticket [bfbf38e5e9956ac69f] ############
  73 #
  74 # This first test case is the original problem report:
  75 do_test aggnested-3.0 {
  76   db eval {
  77     CREATE TABLE AAA (
  78       aaa_id       INTEGER PRIMARY KEY AUTOINCREMENT
  79     );
  80     CREATE TABLE RRR (
  81       rrr_id      INTEGER     PRIMARY KEY AUTOINCREMENT,
  82       rrr_date    INTEGER     NOT NULL,
  83       rrr_aaa     INTEGER
  84     );
  85     CREATE TABLE TTT (
  86       ttt_id      INTEGER PRIMARY KEY AUTOINCREMENT,
  87       target_aaa  INTEGER NOT NULL,
  88       source_aaa  INTEGER NOT NULL
  89     );
  90     insert into AAA (aaa_id) values (2);
  91     insert into TTT (ttt_id, target_aaa, source_aaa)
  92     values (4469, 2, 2);
  93     insert into TTT (ttt_id, target_aaa, source_aaa)
  94     values (4476, 2, 1);
  95     insert into RRR (rrr_id, rrr_date, rrr_aaa)
  96     values (0, 0, NULL);
  97     insert into RRR (rrr_id, rrr_date, rrr_aaa)
  98     values (2, 4312, 2);
  99     SELECT i.aaa_id,
 100       (SELECT sum(CASE WHEN (t.source_aaa == i.aaa_id) THEN 1 ELSE 0 END)
 101          FROM TTT t
 102       ) AS segfault
 103     FROM
 104      (SELECT curr.rrr_aaa as aaa_id
 105         FROM RRR curr
 106           -- you also can comment out the next line
 107           -- it causes segfault to happen after one row is outputted
 108           INNER JOIN AAA a ON (curr.rrr_aaa = aaa_id)
 109           LEFT JOIN RRR r ON (r.rrr_id <> 0 AND r.rrr_date < curr.rrr_date)
 110        GROUP BY curr.rrr_id
 111       HAVING r.rrr_date IS NULL
 112     ) i;
 113   }
 114 } {2 1}
 115
 116 # Further variants of the test case, as found in the ticket
 117 #
 118 do_test aggnested-3.1 {
 119   db eval {
 120     DROP TABLE IF EXISTS t1;
 121     DROP TABLE IF EXISTS t2;
 122     CREATE TABLE t1 (
 123       id1 INTEGER PRIMARY KEY AUTOINCREMENT,
 124       value1 INTEGER
 125     );
 126     INSERT INTO t1 VALUES(4469,2),(4476,1);
 127     CREATE TABLE t2 (
 128       id2 INTEGER PRIMARY KEY AUTOINCREMENT,
 129       value2 INTEGER
 130     );
 131     INSERT INTO t2 VALUES(0,1),(2,2);
 132     SELECT
 133      (SELECT sum(value2==xyz) FROM t2)
 134     FROM
 135      (SELECT curr.value1 as xyz
 136         FROM t1 AS curr LEFT JOIN t1 AS other
 137        GROUP BY curr.id1);
 138   }
 139 } {1 1}
 140 do_test aggnested-3.2 {
 141   db eval {
 142     DROP TABLE IF EXISTS t1;
 143     DROP TABLE IF EXISTS t2;
 144     CREATE TABLE t1 (
 145       id1 INTEGER,
 146       value1 INTEGER,
 147       x1 INTEGER
 148     );
 149     INSERT INTO t1 VALUES(4469,2,98),(4469,1,99),(4469,3,97);
 150     CREATE TABLE t2 (
 151       value2 INTEGER
 152     );
 153     INSERT INTO t2 VALUES(1);
 154     SELECT
 155      (SELECT sum(value2==xyz) FROM t2)
 156     FROM
 157      (SELECT value1 as xyz, max(x1) AS pqr
 158         FROM t1
 159        GROUP BY id1);
 160     SELECT
 161      (SELECT sum(value2<>xyz) FROM t2)
 162     FROM
 163      (SELECT value1 as xyz, max(x1) AS pqr
 164         FROM t1
 165        GROUP BY id1);
 166   }
 167 } {1 0}
 168 do_test aggnested-3.3 {
 169   db eval {
 170     DROP TABLE IF EXISTS t1;
 171     DROP TABLE IF EXISTS t2;
 172     CREATE TABLE t1(id1, value1);
 173     INSERT INTO t1 VALUES(4469,2),(4469,1);
 174     CREATE TABLE t2 (value2);
 175     INSERT INTO t2 VALUES(1);
 176     SELECT (SELECT sum(value2=value1) FROM t2), max(value1)
 177       FROM t1
 178      GROUP BY id1;
 179   }
 180 } {0 2}
 181
 182 # A batch of queries all doing approximately the same operation involving
 183 # two nested aggregate queries.
 184 #
 185 do_test aggnested-3.11 {
 186   db eval {
 187     DROP TABLE IF EXISTS t1;
 188     DROP TABLE IF EXISTS t2;
 189     CREATE TABLE t1(id1, value1);
 190     INSERT INTO t1 VALUES(4469,12),(4469,11),(4470,34);
 191     CREATE INDEX t1id1 ON t1(id1);
 192     CREATE TABLE t2 (value2);
 193     INSERT INTO t2 VALUES(12),(34),(34);
 194     INSERT INTO t2 SELECT value2 FROM t2;
 195
 196     SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=max(value1))
 197       FROM t1
 198      GROUP BY id1;
 199   }
 200 } {12 2 34 4}
 201 do_test aggnested-3.12 {
 202   db eval {
 203     SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=value1)
 204       FROM t1
 205      GROUP BY id1;
 206   }
 207 } {12 2 34 4}
 208 do_test aggnested-3.13 {
 209   db eval {
 210     SELECT value1, (SELECT sum(value2=value1) FROM t2)
 211       FROM t1;
 212   }
 213 } {12 2 11 0 34 4}
 214 do_test aggnested-3.14 {
 215   db eval {
 216     SELECT value1, (SELECT sum(value2=value1) FROM t2)
 217       FROM t1
 218      WHERE value1 IN (SELECT max(value1) FROM t1 GROUP BY id1);
 219   }
 220 } {12 2 34 4}
 221 do_test aggnested-3.15 {
 222   # FIXME:  If case 3.16 works, then this case really ought to work too...
 223   catchsql {
 224     SELECT max(value1), (SELECT sum(value2=max(value1)) FROM t2)
 225       FROM t1
 226      GROUP BY id1;
 227   }
 228 } {1 {misuse of aggregate function max()}}
 229 do_test aggnested-3.16 {
 230   db eval {
 231     SELECT max(value1), (SELECT sum(value2=value1) FROM t2)
 232       FROM t1
 233      GROUP BY id1;
 234   }
 235 } {12 2 34 4}
 236
 237 # 2019-08-31
 238 # Problem found by dbsqlfuzz
 239 #
 240 do_execsql_test aggnested-4.1 {
 241   DROP TABLE IF EXISTS aa;
 242   DROP TABLE IF EXISTS bb;
 243   CREATE TABLE aa(x INT);  INSERT INTO aa(x) VALUES(123);
 244   CREATE TABLE bb(y INT);  INSERT INTO bb(y) VALUES(456);
 245   SELECT (SELECT sum(x+(SELECT y)) FROM bb) FROM aa;
 246 } {579}
 247 do_execsql_test aggnested-4.2 {
 248   SELECT (SELECT sum(x+y) FROM bb) FROM aa;
 249 } {579}
 250 do_execsql_test aggnested-4.3 {
 251   DROP TABLE IF EXISTS tx;
 252   DROP TABLE IF EXISTS ty;
 253   CREATE TABLE tx(x INT);
 254   INSERT INTO tx VALUES(1),(2),(3),(4),(5);
 255   CREATE TABLE ty(y INT);
 256   INSERT INTO ty VALUES(91),(92),(93);
 257   SELECT min((SELECT count(y) FROM ty)) FROM tx;
 258 } {3}
 259 do_execsql_test aggnested-4.4 {
 260   SELECT max((SELECT a FROM (SELECT count(*) AS a FROM ty) AS s)) FROM tx;
 261 } {3}
 262
 263 #--------------------------------------------------------------------------
 264 #
 265 reset_db
 266 do_execsql_test 5.0 {
 267   CREATE TABLE x1(a, b);
 268   INSERT INTO x1 VALUES(1, 2);
 269   CREATE TABLE x2(x);
 270   INSERT INTO x2 VALUES(NULL), (NULL), (NULL);
 271 }
 272
 273 # At one point, aggregate "total()" in the query below was being processed
 274 # as part of the outer SELECT, not as part of the sub-select with no FROM
 275 # clause.
 276 do_execsql_test 5.1 {
 277   SELECT ( SELECT total( (SELECT b FROM x1) ) ) FROM x2;
 278 } {2.0 2.0 2.0}
 279
 280 do_execsql_test 5.2 {
 281   SELECT ( SELECT total( (SELECT 2 FROM x1) ) ) FROM x2;
 282 } {2.0 2.0 2.0}
 283
 284 do_execsql_test 5.3 {
 285   CREATE TABLE t1(a);
 286   CREATE TABLE t2(b);
 287 }
 288
 289 do_execsql_test 5.4 {
 290   SELECT(
 291     SELECT max(b) LIMIT (
 292       SELECT total( (SELECT a FROM t1) )
 293     )
 294   )
 295   FROM t2;
 296 } {{}}
 297
 298 do_execsql_test 5.5 {
 299   CREATE TABLE a(b);
 300   WITH c AS(SELECT a)
 301     SELECT(SELECT(SELECT group_concat(b, b)
 302           LIMIT(SELECT 0.100000 *
 303             AVG(DISTINCT(SELECT 0 FROM a ORDER BY b, b, b))))
 304         FROM a GROUP BY b,
 305         b, b) FROM a EXCEPT SELECT b FROM a ORDER BY b,
 306     b, b;
 307 }
 308
 309
 310
 311
 312 finish_test