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.1-rj {
 141   db eval {
 142     SELECT
 143      (SELECT sum(value2==xyz) FROM t2)
 144     FROM
 145      (SELECT curr.value1 as xyz
 146         FROM t1 AS other RIGHT JOIN t1 AS curr
 147        GROUP BY curr.id1);
 148   }
 149 } {1 1}
 150
 151 do_test aggnested-3.2 {
 152   db eval {
 153     DROP TABLE IF EXISTS t1;
 154     DROP TABLE IF EXISTS t2;
 155     CREATE TABLE t1 (
 156       id1 INTEGER,
 157       value1 INTEGER,
 158       x1 INTEGER
 159     );
 160     INSERT INTO t1 VALUES(4469,2,98),(4469,1,99),(4469,3,97);
 161     CREATE TABLE t2 (
 162       value2 INTEGER
 163     );
 164     INSERT INTO t2 VALUES(1);
 165     SELECT
 166      (SELECT sum(value2==xyz) FROM t2)
 167     FROM
 168      (SELECT value1 as xyz, max(x1) AS pqr
 169         FROM t1
 170        GROUP BY id1);
 171     SELECT
 172      (SELECT sum(value2<>xyz) FROM t2)
 173     FROM
 174      (SELECT value1 as xyz, max(x1) AS pqr
 175         FROM t1
 176        GROUP BY id1);
 177   }
 178 } {1 0}
 179 do_test aggnested-3.3 {
 180   db eval {
 181     DROP TABLE IF EXISTS t1;
 182     DROP TABLE IF EXISTS t2;
 183     CREATE TABLE t1(id1, value1);
 184     INSERT INTO t1 VALUES(4469,2),(4469,1);
 185     CREATE TABLE t2 (value2);
 186     INSERT INTO t2 VALUES(1);
 187     SELECT (SELECT sum(value2=value1) FROM t2), max(value1)
 188       FROM t1
 189      GROUP BY id1;
 190   }
 191 } {0 2}
 192
 193 # A batch of queries all doing approximately the same operation involving
 194 # two nested aggregate queries.
 195 #
 196 do_test aggnested-3.11 {
 197   db eval {
 198     DROP TABLE IF EXISTS t1;
 199     DROP TABLE IF EXISTS t2;
 200     CREATE TABLE t1(id1, value1);
 201     INSERT INTO t1 VALUES(4469,12),(4469,11),(4470,34);
 202     CREATE INDEX t1id1 ON t1(id1);
 203     CREATE TABLE t2 (value2);
 204     INSERT INTO t2 VALUES(12),(34),(34);
 205     INSERT INTO t2 SELECT value2 FROM t2;
 206
 207     SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=max(value1))
 208       FROM t1
 209      GROUP BY id1;
 210   }
 211 } {12 2 34 4}
 212 do_test aggnested-3.12 {
 213   db eval {
 214     SELECT max(value1), (SELECT count(*) FROM t2 WHERE value2=value1)
 215       FROM t1
 216      GROUP BY id1;
 217   }
 218 } {12 2 34 4}
 219 do_test aggnested-3.13 {
 220   db eval {
 221     SELECT value1, (SELECT sum(value2=value1) FROM t2)
 222       FROM t1;
 223   }
 224 } {12 2 11 0 34 4}
 225 do_test aggnested-3.14 {
 226   db eval {
 227     SELECT value1, (SELECT sum(value2=value1) FROM t2)
 228       FROM t1
 229      WHERE value1 IN (SELECT max(value1) FROM t1 GROUP BY id1);
 230   }
 231 } {12 2 34 4}
 232 do_test aggnested-3.15 {
 233   # FIXME:  If case 3.16 works, then this case really ought to work too...
 234   catchsql {
 235     SELECT max(value1), (SELECT sum(value2=max(value1)) FROM t2)
 236       FROM t1
 237      GROUP BY id1;
 238   }
 239 } {1 {misuse of aggregate function max()}}
 240 do_test aggnested-3.16 {
 241   db eval {
 242     SELECT max(value1), (SELECT sum(value2=value1) FROM t2)
 243       FROM t1
 244      GROUP BY id1;
 245   }
 246 } {12 2 34 4}
 247
 248 # 2019-08-31
 249 # Problem found by dbsqlfuzz
 250 #
 251 do_execsql_test aggnested-4.1 {
 252   DROP TABLE IF EXISTS aa;
 253   DROP TABLE IF EXISTS bb;
 254   CREATE TABLE aa(x INT);  INSERT INTO aa(x) VALUES(123);
 255   CREATE TABLE bb(y INT);  INSERT INTO bb(y) VALUES(456);
 256   SELECT (SELECT sum(x+(SELECT y)) FROM bb) FROM aa;
 257 } {579}
 258 do_execsql_test aggnested-4.2 {
 259   SELECT (SELECT sum(x+y) FROM bb) FROM aa;
 260 } {579}
 261 do_execsql_test aggnested-4.3 {
 262   DROP TABLE IF EXISTS tx;
 263   DROP TABLE IF EXISTS ty;
 264   CREATE TABLE tx(x INT);
 265   INSERT INTO tx VALUES(1),(2),(3),(4),(5);
 266   CREATE TABLE ty(y INT);
 267   INSERT INTO ty VALUES(91),(92),(93);
 268   SELECT min((SELECT count(y) FROM ty)) FROM tx;
 269 } {3}
 270 do_execsql_test aggnested-4.4 {
 271   SELECT max((SELECT a FROM (SELECT count(*) AS a FROM ty) AS s)) FROM tx;
 272 } {3}
 273
 274 #--------------------------------------------------------------------------
 275 #
 276 reset_db
 277 do_execsql_test 5.0 {
 278   CREATE TABLE x1(a, b);
 279   INSERT INTO x1 VALUES(1, 2);
 280   CREATE TABLE x2(x);
 281   INSERT INTO x2 VALUES(NULL), (NULL), (NULL);
 282 }
 283
 284 # At one point, aggregate "total()" in the query below was being processed
 285 # as part of the outer SELECT, not as part of the sub-select with no FROM
 286 # clause.
 287 do_execsql_test 5.1 {
 288   SELECT ( SELECT total( (SELECT b FROM x1) ) ) FROM x2;
 289 } {2.0 2.0 2.0}
 290
 291 do_execsql_test 5.2 {
 292   SELECT ( SELECT total( (SELECT 2 FROM x1) ) ) FROM x2;
 293 } {2.0 2.0 2.0}
 294
 295 do_execsql_test 5.3 {
 296   CREATE TABLE t1(a);
 297   CREATE TABLE t2(b);
 298 }
 299
 300 do_execsql_test 5.4 {
 301   SELECT(
 302     SELECT max(b) LIMIT (
 303       SELECT total( (SELECT a FROM t1) )
 304     )
 305   )
 306   FROM t2;
 307 } {{}}
 308
 309 do_execsql_test 5.5 {
 310   CREATE TABLE a(b);
 311   WITH c AS(SELECT a)
 312     SELECT(SELECT(SELECT group_concat(b, b)
 313           LIMIT(SELECT 0.100000 *
 314             AVG(DISTINCT(SELECT 0 FROM a ORDER BY b, b, b))))
 315         FROM a GROUP BY b,
 316         b, b) FROM a EXCEPT SELECT b FROM a ORDER BY b,
 317     b, b;
 318 }
 319
 320 #-------------------------------------------------------------------------
 321 # dbsqlfuzz a779227f721a834df95f4f42d0c31550a1f8b8a2
 322 #
 323 reset_db
 324 do_execsql_test 6.0 {
 325   CREATE TABLE t1(a);
 326   CREATE TABLE t2(b);
 327
 328   INSERT INTO t1 VALUES('x');
 329   INSERT INTO t2 VALUES(1);
 330 }
 331
 332 do_execsql_test 6.1.1 {
 333   SELECT (
 334     SELECT t2.b FROM (SELECT t2.b AS c FROM t1) GROUP BY 1 HAVING t2.b
 335   )
 336   FROM t2 GROUP BY 'constant_string';
 337 } {1}
 338 do_execsql_test 6.1.2 {
 339   SELECT (
 340     SELECT c FROM (SELECT t2.b AS c FROM t1) GROUP BY c HAVING t2.b
 341   )
 342   FROM t2 GROUP BY 'constant_string';
 343 } {1}
 344
 345 do_execsql_test 6.2.0 {
 346   UPDATE t2 SET b=0
 347 }
 348 do_execsql_test 6.2.1 {
 349   SELECT (
 350     SELECT t2.b FROM (SELECT t2.b AS c FROM t1) GROUP BY 1 HAVING t2.b
 351   )
 352   FROM t2 GROUP BY 'constant_string';
 353 } {{}}
 354 do_execsql_test 6.2.2 {
 355   SELECT (
 356     SELECT c FROM (SELECT t2.b AS c FROM t1) GROUP BY c HAVING t2.b
 357   )
 358   FROM t2 GROUP BY 'constant_string';
 359 } {{}}
 360
 361
 362
 363
 364
 365 finish_test