third_party/sqlite/src/test/selectB.test

   1 # 2008 June 24
   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 # $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17
  18 ifcapable !compound {
  19   finish_test
  20   return
  21 }
  22
  23 proc test_transform {testname sql1 sql2 results} {
  24   set ::vdbe1 [list]
  25   set ::vdbe2 [list]
  26   db eval "explain $sql1" { lappend ::vdbe1 $opcode }
  27   db eval "explain $sql2" { lappend ::vdbe2 $opcode }
  28
  29   do_test $testname.transform {
  30     set ::vdbe1
  31   } $::vdbe2
  32
  33   set ::sql1 $sql1
  34   do_test $testname.sql1 {
  35     execsql $::sql1
  36   } $results
  37
  38   set ::sql2 $sql2
  39   do_test $testname.sql2 {
  40     execsql $::sql2
  41   } $results
  42 }
  43
  44 do_test selectB-1.1 {
  45   execsql {
  46     CREATE TABLE t1(a, b, c);
  47     CREATE TABLE t2(d, e, f);
  48
  49     INSERT INTO t1 VALUES( 2,  4,  6);
  50     INSERT INTO t1 VALUES( 8, 10, 12);
  51     INSERT INTO t1 VALUES(14, 16, 18);
  52
  53     INSERT INTO t2 VALUES(3,   6,  9);
  54     INSERT INTO t2 VALUES(12, 15, 18);
  55     INSERT INTO t2 VALUES(21, 24, 27);
  56   }
  57 } {}
  58
  59 for {set ii 1} {$ii <= 2} {incr ii} {
  60
  61   if {$ii == 2} {
  62     do_test selectB-2.1 {
  63       execsql {
  64         CREATE INDEX i1 ON t1(a);
  65         CREATE INDEX i2 ON t2(d);
  66       }
  67     } {}
  68   }
  69
  70   test_transform selectB-$ii.2 {
  71     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
  72   } {
  73     SELECT a FROM t1 UNION ALL SELECT d FROM t2
  74   } {2 8 14 3 12 21}
  75
  76   test_transform selectB-$ii.3 {
  77     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
  78   } {
  79     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
  80   } {2 3 8 12 14 21}
  81
  82   test_transform selectB-$ii.4 {
  83     SELECT * FROM
  84       (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
  85     WHERE a>10 ORDER BY 1
  86   } {
  87     SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
  88   } {12 14 21}
  89
  90   test_transform selectB-$ii.5 {
  91     SELECT * FROM
  92       (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
  93     WHERE a>10 ORDER BY a
  94   } {
  95     SELECT a FROM t1 WHERE a>10
  96       UNION ALL
  97     SELECT d FROM t2 WHERE d>10
  98     ORDER BY a
  99   } {12 14 21}
 100
 101   test_transform selectB-$ii.6 {
 102     SELECT * FROM
 103       (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12)
 104     WHERE a>10 ORDER BY a
 105   } {
 106     SELECT a FROM t1 WHERE a>10
 107       UNION ALL
 108     SELECT d FROM t2 WHERE d>12 AND d>10
 109     ORDER BY a
 110   } {14 21}
 111
 112   test_transform selectB-$ii.7 {
 113     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
 114     LIMIT 2
 115   } {
 116     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
 117   } {2 3}
 118
 119   test_transform selectB-$ii.8 {
 120     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
 121     LIMIT 2 OFFSET 3
 122   } {
 123     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
 124   } {12 14}
 125
 126   test_transform selectB-$ii.9 {
 127     SELECT * FROM (
 128       SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
 129     )
 130   } {
 131     SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
 132   } {2 8 14 3 12 21 6 12 18}
 133
 134   test_transform selectB-$ii.10 {
 135     SELECT * FROM (
 136       SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
 137     ) ORDER BY 1
 138   } {
 139     SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
 140     ORDER BY 1
 141   } {2 3 6 8 12 12 14 18 21}
 142
 143   test_transform selectB-$ii.11 {
 144     SELECT * FROM (
 145       SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
 146     ) WHERE a>=10 ORDER BY 1 LIMIT 3
 147   } {
 148     SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
 149     UNION ALL SELECT c FROM t1 WHERE c>=10
 150     ORDER BY 1 LIMIT 3
 151   } {12 12 14}
 152
 153   test_transform selectB-$ii.12 {
 154     SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
 155   } {
 156     SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
 157   } {2 8}
 158
 159   # An ORDER BY in a compound subqueries defeats flattening.  Ticket #3773
 160   # test_transform selectB-$ii.13 {
 161   #   SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
 162   # } {
 163   #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
 164   # } {2 3 8 12 14 21}
 165   #
 166   # test_transform selectB-$ii.14 {
 167   #  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
 168   # } {
 169   #  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
 170   # } {21 14 12 8 3 2}
 171   #
 172   # test_transform selectB-$ii.14 {
 173   #   SELECT * FROM (
 174   #     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
 175   #   ) LIMIT 2 OFFSET 2
 176   # } {
 177   #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
 178   #    LIMIT 2 OFFSET 2
 179   # } {12 8}
 180   #
 181   # test_transform selectB-$ii.15 {
 182   #   SELECT * FROM (
 183   #     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
 184   #  )
 185   # } {
 186   #   SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
 187   # } {2 4 3 6 8 10 12 15 14 16 21 24}
 188 }
 189
 190 do_test selectB-3.0 {
 191   execsql {
 192     DROP INDEX i1;
 193     DROP INDEX i2;
 194   }
 195 } {}
 196
 197 for {set ii 3} {$ii <= 4} {incr ii} {
 198
 199   if {$ii == 4} {
 200     do_test selectB-4.0 {
 201       execsql {
 202         CREATE INDEX i1 ON t1(a);
 203         CREATE INDEX i2 ON t1(b);
 204         CREATE INDEX i3 ON t1(c);
 205         CREATE INDEX i4 ON t2(d);
 206         CREATE INDEX i5 ON t2(e);
 207         CREATE INDEX i6 ON t2(f);
 208       }
 209     } {}
 210   }
 211
 212   do_test selectB-$ii.1 {
 213     execsql {
 214       SELECT DISTINCT * FROM
 215         (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
 216       ORDER BY 1;
 217     }
 218   } {6 12 15 18 24}
 219
 220   do_test selectB-$ii.2 {
 221     execsql {
 222       SELECT c, count(*) FROM
 223         (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
 224       GROUP BY c ORDER BY 1;
 225     }
 226   } {6 2 12 1 15 1 18 1 24 1}
 227   do_test selectB-$ii.3 {
 228     execsql {
 229       SELECT c, count(*) FROM
 230         (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
 231       GROUP BY c HAVING count(*)>1;
 232     }
 233   } {6 2}
 234   do_test selectB-$ii.4 {
 235     execsql {
 236       SELECT t4.c, t3.a FROM
 237         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
 238       WHERE t3.a=14
 239       ORDER BY 1
 240     }
 241   } {6 14 6 14 12 14 15 14 18 14 24 14}
 242
 243   do_test selectB-$ii.5 {
 244     execsql {
 245       SELECT d FROM t2
 246       EXCEPT
 247       SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
 248     }
 249   } {}
 250   do_test selectB-$ii.6 {
 251     execsql {
 252       SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
 253       EXCEPT
 254       SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
 255     }
 256   } {}
 257   do_test selectB-$ii.7 {
 258     execsql {
 259       SELECT c FROM t1
 260       EXCEPT
 261       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 262     }
 263   } {12}
 264   do_test selectB-$ii.8 {
 265     execsql {
 266       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 267       EXCEPT
 268       SELECT c FROM t1
 269     }
 270   } {9 15 24 27}
 271   do_test selectB-$ii.9 {
 272     execsql {
 273       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 274       EXCEPT
 275       SELECT c FROM t1
 276       ORDER BY c DESC
 277     }
 278   } {27 24 15 9}
 279
 280   do_test selectB-$ii.10 {
 281     execsql {
 282       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 283       UNION
 284       SELECT c FROM t1
 285       ORDER BY c DESC
 286     }
 287   } {27 24 18 15 12 9 6}
 288   do_test selectB-$ii.11 {
 289     execsql {
 290       SELECT c FROM t1
 291       UNION
 292       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 293       ORDER BY c
 294     }
 295   } {6 9 12 15 18 24 27}
 296   do_test selectB-$ii.12 {
 297     execsql {
 298       SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
 299       ORDER BY c
 300     }
 301   } {6 9 12 15 18 18 24 27}
 302   do_test selectB-$ii.13 {
 303     execsql {
 304       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 305       UNION
 306       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 307       ORDER BY 1
 308     }
 309   } {6 9 15 18 24 27}
 310
 311   do_test selectB-$ii.14 {
 312     execsql {
 313       SELECT c FROM t1
 314       INTERSECT
 315       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 316       ORDER BY 1
 317     }
 318   } {6 18}
 319   do_test selectB-$ii.15 {
 320     execsql {
 321       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 322       INTERSECT
 323       SELECT c FROM t1
 324       ORDER BY 1
 325     }
 326   } {6 18}
 327   do_test selectB-$ii.16 {
 328     execsql {
 329       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 330       INTERSECT
 331       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 332       ORDER BY 1
 333     }
 334   } {6 9 15 18 24 27}
 335
 336   do_test selectB-$ii.17 {
 337     execsql {
 338       SELECT * FROM (
 339         SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
 340       ) LIMIT 2
 341     }
 342   } {2 8}
 343
 344   do_test selectB-$ii.18 {
 345     execsql {
 346       SELECT * FROM (
 347         SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
 348       ) LIMIT 2
 349     }
 350   } {14 3}
 351
 352   do_test selectB-$ii.19 {
 353     execsql {
 354       SELECT * FROM (
 355         SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
 356       )
 357     }
 358   } {0 1 0 1}
 359
 360   do_test selectB-$ii.20 {
 361     execsql {
 362       SELECT DISTINCT * FROM (
 363         SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
 364       )
 365     }
 366   } {0 1}
 367
 368   do_test selectB-$ii.21 {
 369     execsql {
 370       SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
 371     }
 372   } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
 373
 374   do_test selectB-$ii.21 {
 375     execsql {
 376       SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
 377     }
 378   } {3 12 21 345}
 379 }
 380
 381 finish_test