test/unionall.test

   1 # 2020-12-16
   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.  The
  12 # focus of this file is flattening UNION ALL sub-queries.
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17 set testprefix unionall
  18
  19 do_execsql_test 1.0 {
  20   CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
  21   CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);
  22   CREATE TABLE t1_c(e INTEGER PRIMARY KEY, f TEXT);
  23
  24   INSERT INTO t1_a VALUES(1, 'one'), (4, 'four');
  25   INSERT INTO t1_b VALUES(2, 'two'), (5, 'five');
  26   INSERT INTO t1_c VALUES(3, 'three'), (6, 'six');
  27
  28   CREATE VIEW t1 AS
  29     SELECT a, b FROM t1_a   UNION ALL
  30     SELECT c, d FROM t1_b   UNION ALL
  31     SELECT e, f FROM t1_c;
  32
  33   CREATE TABLE i1(x);
  34   INSERT INTO i1 VALUES(2), (5), (6), (1);
  35 }
  36
  37 do_execsql_test 1.1 {
  38   SELECT a, b FROM (
  39     SELECT a, b FROM t1_a   UNION ALL
  40     SELECT c, d FROM t1_b   UNION ALL
  41     SELECT e, f FROM t1_c
  42   ) ORDER BY a
  43 } {
  44   1 one 2 two 3 three 4 four 5 five 6 six
  45 }
  46
  47 do_execsql_test 1.2 {
  48   SELECT a, b FROM t1 ORDER BY a
  49 } {
  50   1 one 2 two 3 three 4 four 5 five 6 six
  51 }
  52
  53 do_execsql_test 1.3 {
  54   SELECT a, b FROM i1, t1 WHERE a=x ORDER BY a
  55 } {1 one 2 two 5 five 6 six}
  56
  57
  58 #-------------------------------------------------------------------------
  59 reset_db
  60
  61 do_execsql_test 2.1.0 {
  62   CREATE TABLE t1(x, y);
  63   INSERT INTO t1 VALUES(1, 'one');
  64   INSERT INTO t1 VALUES(1, 'ONE');
  65   INSERT INTO t1 VALUES(2, 'two');
  66   INSERT INTO t1 VALUES(2, 'TWO');
  67   INSERT INTO t1 VALUES(3, 'three');
  68   INSERT INTO t1 VALUES(3, 'THREE');
  69 }
  70
  71 do_execsql_test 2.1.1 {
  72   WITH s(i) AS (
  73       SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<3
  74   )
  75   SELECT * FROM (
  76     SELECT 0 AS i UNION ALL SELECT i FROM s UNION ALL SELECT 0
  77   ), t1 WHERE x=i;
  78 } {
  79   1 1 one 1 1 ONE 2 2 two 2 2 TWO 3 3 three 3 3 THREE
  80 }
  81
  82 do_catchsql_test 2.1.2 {
  83   WITH s(i) AS (
  84       SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<3 UNION ALL SELECT 4
  85   )
  86   SELECT * FROM s, t1 WHERE x=i;
  87 } {1 {circular reference: s}}
  88
  89 do_execsql_test 2.2.0 {
  90   CREATE TABLE t2_a(k INTEGER PRIMARY KEY, v TEXT);
  91   CREATE TABLE t2_b(k INTEGER PRIMARY KEY, v TEXT);
  92
  93   CREATE VIEW t2 AS
  94     SELECT * FROM t2_a
  95     UNION ALL
  96     SELECT * FROM t2_b;
  97
  98   CREATE TRIGGER t2_insert INSTEAD OF INSERT ON t2 BEGIN
  99     INSERT INTO t2_a SELECT new.k, new.v WHERE (new.k%2)==0;
 100     INSERT INTO t2_b SELECT new.k, new.v WHERE (new.k%2)==1;
 101   END;
 102
 103   INSERT INTO t2 VALUES(5, 'v'), (4, 'iv'), (3, 'iii'), (2, 'ii');
 104 }
 105
 106 do_execsql_test 2.2.1 {
 107   SELECT * FROM t1, t2 WHERE x=k;
 108 } {
 109   2 two 2 ii 2 TWO 2 ii 3 three 3 iii 3 THREE 3 iii
 110 }
 111
 112 do_execsql_test 2.2.2 {
 113   SELECT * FROM t1 LEFT JOIN t2 ON (x=k);
 114 } {
 115   1 one {} {}
 116   1 ONE {} {}
 117   2 two 2 ii 2 TWO 2 ii 3 three 3 iii 3 THREE 3 iii
 118 }
 119
 120 do_execsql_test 2.2.3 {
 121   SELECT x1.*, x2.* FROM t2 AS x1, t2 AS x2 WHERE x1.k=x2.k+1
 122 } {
 123   4 iv   3 iii
 124   3 iii  2 ii
 125   5 v    4 iv
 126 }
 127
 128 do_execsql_test 2.2.4 {
 129   SELECT * FROM t1, t2 WHERE x=k ORDER BY y;
 130 } {
 131   3 THREE 3 iii
 132   2 TWO 2 ii
 133   3 three 3 iii
 134   2 two 2 ii
 135 }
 136 do_execsql_test 2.2.5 {
 137   SELECT * FROM t1, t2 WHERE x=k ORDER BY y||'';
 138 } {
 139   3 THREE 3 iii
 140   2 TWO 2 ii
 141   3 three 3 iii
 142   2 two 2 ii
 143 }
 144 do_execsql_test 2.2.6 {
 145   SELECT * FROM t1, t2 WHERE x=k ORDER BY v
 146 } {
 147   2 two   2 ii
 148   2 TWO   2 ii
 149   3 three 3 iii
 150   3 THREE 3 iii
 151 }
 152 do_execsql_test 2.2.7 {
 153   SELECT * FROM t1, t2 WHERE x=k ORDER BY v||''
 154 } {
 155   2 two   2 ii
 156   2 TWO   2 ii
 157   3 three 3 iii
 158   3 THREE 3 iii
 159 }
 160 do_execsql_test 2.2.8 {
 161   SELECT * FROM t1, t2 WHERE x=k ORDER BY k,v||''
 162 } {
 163   2 two   2 ii
 164   2 TWO   2 ii
 165   3 three 3 iii
 166   3 THREE 3 iii
 167 }
 168 do_execsql_test 2.2.9a {
 169   SELECT * FROM t1, t2 ORDER BY +k
 170 } {
 171   1 one 2 ii 1 ONE 2 ii 2 two 2 ii
 172   2 TWO 2 ii 3 three 2 ii 3 THREE 2 ii
 173
 174   1 one 3 iii 1 ONE 3 iii 2 two 3 iii
 175   2 TWO 3 iii 3 three 3 iii 3 THREE 3 iii
 176
 177   1 one 4 iv 1 ONE 4 iv 2 two 4 iv
 178   2 TWO 4 iv 3 three 4 iv 3 THREE 4 iv
 179
 180   1 one 5 v 1 ONE 5 v 2 two 5 v
 181   2 TWO 5 v 3 three 5 v 3 THREE 5 v
 182 }
 183
 184 do_execsql_test 2.2.9b {
 185   SELECT * FROM t1, t2 ORDER BY k
 186 } {
 187   1 one 2 ii 1 ONE 2 ii 2 two 2 ii
 188   2 TWO 2 ii 3 three 2 ii 3 THREE 2 ii
 189
 190   1 one 3 iii 1 ONE 3 iii 2 two 3 iii
 191   2 TWO 3 iii 3 three 3 iii 3 THREE 3 iii
 192
 193   1 one 4 iv 1 ONE 4 iv 2 two 4 iv
 194   2 TWO 4 iv 3 three 4 iv 3 THREE 4 iv
 195
 196   1 one 5 v 1 ONE 5 v 2 two 5 v
 197   2 TWO 5 v 3 three 5 v 3 THREE 5 v
 198 }
 199
 200 #-------------------------------------------------------------------------
 201 reset_db
 202 do_execsql_test 3.0 {
 203   CREATE TABLE t1(c INTEGER PRIMARY KEY, d TEXT);
 204   INSERT INTO t1 VALUES(1,2);
 205   CREATE TABLE t3_a(k INTEGER PRIMARY KEY, v TEXT);
 206   INSERT INTO t3_a VALUES(2,'ii');
 207   CREATE TABLE t3_b(k INTEGER PRIMARY KEY, v TEXT);
 208   CREATE VIEW t3 AS
 209     SELECT * FROM t3_a
 210     UNION ALL
 211     SELECT * FROM t3_b;
 212 } {}
 213
 214 do_execsql_test 3.1 {
 215   SELECT * FROM t1, t3 ORDER BY k;
 216 } {1 2 2 ii}
 217
 218 reset_db
 219 do_execsql_test 4.0 {
 220
 221   CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
 222   INSERT INTO t1_a VALUES(123, 't1_a');
 223   CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);
 224
 225   CREATE VIEW t1 AS
 226     SELECT a, b FROM t1_a
 227     UNION ALL
 228     SELECT c, d FROM t1_b;
 229
 230   CREATE TABLE t3_a(k INTEGER PRIMARY KEY, v TEXT);
 231   INSERT INTO t3_a VALUES(456, 't3_a');
 232   CREATE TABLE t3_b(k INTEGER PRIMARY KEY, v TEXT);
 233
 234   CREATE VIEW t3 AS
 235     SELECT * FROM t3_a
 236     UNION ALL
 237     SELECT * FROM t3_b;
 238 }
 239
 240 do_execsql_test 4.1 {
 241   SELECT * FROM t1, t3 ORDER BY k;
 242 } {123 t1_a 456 t3_a}
 243
 244 do_execsql_test 4.2 {
 245   SELECT * FROM (SELECT * FROM t1, t3) ORDER BY k;
 246 } {123 t1_a 456 t3_a}
 247
 248 do_execsql_test 4.3 {
 249   SELECT * FROM (SELECT * FROM t1, t3), (
 250     SELECT max(a) OVER () FROM t1
 251       UNION ALL
 252     SELECT min(a) OVER () FROM t1
 253   )
 254   ORDER BY k;
 255 } {
 256   123 t1_a 456 t3_a 123
 257   123 t1_a 456 t3_a 123
 258 }
 259
 260 do_execsql_test 4.3 {
 261   SELECT * FROM (SELECT * FROM t1, t3), (
 262     SELECT group_concat(a) OVER (ORDER BY a),
 263            group_concat(a) OVER (ORDER BY a),
 264            group_concat(a) OVER (ORDER BY a),
 265            group_concat(a) OVER (ORDER BY a),
 266            group_concat(a) OVER (ORDER BY a),
 267            group_concat(a) OVER (ORDER BY a),
 268            group_concat(a) OVER (ORDER BY a),
 269            group_concat(a) OVER (ORDER BY a),
 270            group_concat(a) OVER (ORDER BY a)
 271     FROM t1
 272   )
 273   ORDER BY k;
 274 } {
 275   123 t1_a 456 t3_a 123 123 123 123 123 123 123 123 123
 276 }
 277
 278 do_execsql_test 4.3 {
 279   SELECT * FROM (SELECT * FROM t1, t3) AS o, (
 280     SELECT * FROM t1 LEFT JOIN t3 ON a=k
 281   );
 282 } {
 283   123 t1_a 456 t3_a 123 t1_a {} {}
 284 }
 285
 286 # 2020-12-30: dbsqlfuzz find
 287 reset_db
 288 do_execsql_test 5.1 {
 289   CREATE TABLE t1_a(a INTEGER PRIMARY KEY, b TEXT);
 290   INSERT INTO t1_a VALUES(1,'one');
 291   INSERT INTO t1_a VALUES(0,NULL);
 292   CREATE TABLE t1_b(c INTEGER PRIMARY KEY, d TEXT);
 293   INSERT INTO t1_b VALUES(2,'two');
 294   INSERT INTO t1_b VALUES(5,'five');
 295   CREATE TABLE t1_c(e INTEGER PRIMARY KEY, f TEXT);
 296   INSERT INTO t1_c VALUES(3,'three');
 297   INSERT INTO t1_c VALUES(6,'six');
 298   CREATE TABLE t2(k,v);
 299   INSERT INTO t2 VALUES(5,'v');
 300   INSERT INTO t2 VALUES(4,'iv');
 301   INSERT INTO t2 VALUES(3,'iii');
 302   INSERT INTO t2 VALUES(2,'ii');
 303   CREATE TABLE t3_a(k INTEGER PRIMARY KEY, v TEXT);
 304   INSERT INTO t3_a VALUES(2,'ii');
 305   INSERT INTO t3_a VALUES(4,'iv');
 306   CREATE TABLE t3_b(k INTEG5R PRIMARY KEY, v TEXT);
 307   INSERT INTO t3_b VALUES(NULL,'iii');
 308   INSERT INTO t3_b VALUES(NULL,'v');
 309   CREATE VIEW t1 AS
 310     SELECT a, b FROM t1_a   UNION ALL
 311     SELECT c, d FROM t1_b   UNION ALL
 312     SELECT e, f FROM t1_c;
 313   CREATE VIEW t3 AS
 314       SELECT * FROM t3_a
 315       UNION ALL
 316       SELECT * FROM t3_b;
 317   CREATE TRIGGER t3_insert INSTEAD OF INSERT ON t3 BEGIN
 318       INSERT INTO t3_a SELECT new.k, new.v WHERE (new.k%2)==0;
 319       INSERT INTO t3_b SELECT new.k, new.v WHERE (new.k%2)==1;
 320   END;
 321 } {}
 322 do_execsql_test 5.10 {
 323   SELECT *, '+' FROM t1 LEFT JOIN t2 ON (a NOT IN(SELECT v FROM t1, t3 WHERE a=k)=NOT EXISTS(SELECT 1 FROM t1 LEFT JOIN t3 ON (a=k)));
 324 } {0 {} {} {} + 1 one {} {} + 2 two {} {} + 5 five {} {} + 3 three {} {} + 6 six {} {} +}
 325 do_execsql_test 5.20 {
 326   SELECT *, '+' FROM t1 LEFT JOIN t3 ON (a NOT IN(SELECT v FROM t1 LEFT JOIN t2 ON (a=k))=k);
 327 } {0 {} {} {} + 1 one {} {} + 2 two {} {} + 5 five {} {} + 3 three {} {} + 6 six {} {} +}
 328
 329 reset_db
 330 do_execsql_test 6.0 {
 331   CREATE TABLE t1(a,b);
 332   INSERT INTO t1 VALUES(1,2);
 333   CREATE TABLE t2(a,b);
 334   INSERT INTO t2 VALUES(3,4);
 335
 336   CREATE TABLE t3(a,b);
 337   INSERT INTO t3 VALUES(5,6);
 338   CREATE TABLE t4(a,b);
 339   INSERT INTO t4 VALUES(7,8);
 340
 341   CREATE TABLE t5(a,b);
 342   INSERT INTO t5 VALUES(9,10);
 343 }
 344
 345 do_execsql_test 6.1 {
 346   WITH x(c) AS (
 347     SELECT 1000 FROM t1 UNION ALL SELECT 800 FROM t2
 348   ),
 349   y(d) AS (
 350     SELECT  100 FROM t3 UNION ALL SELECT 400 FROM t4
 351   )
 352   SELECT * FROM t5, x, y;
 353 } {
 354   9 10 1000 100     9 10 1000 400
 355   9 10 800 100      9 10 800 400
 356 }
 357
 358 # 2021-04-26 dbsqlfuzz 88ed5c66789fced139d148aed823cba7c0926dd7
 359 reset_db
 360 do_execsql_test 7.1 {
 361   WITH c1(x) AS (VALUES(0) UNION ALL SELECT 100+x FROM c1 WHERE x<100 UNION ALL SELECT 1+x FROM c1 WHERE x<1)
 362   SELECT x, y, '|'
 363     FROM c1 AS x1, (SELECT x+1 AS y FROM c1 WHERE x<1 UNION ALL SELECT 1+x FROM c1 WHERE 1<x) AS x2
 364    ORDER BY x, y;
 365 } {0 1 | 0 101 | 0 102 | 1 1 | 1 101 | 1 102 | 100 1 | 100 101 | 100 102 | 101 1 | 101 101 | 101 102 |}
 366
 367 finish_test