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 <= 6} {incr ii} {
 198
 199   switch $ii {
 200     4 {
 201       optimization_control db query-flattener off
 202     }
 203     5 {
 204       optimization_control db query-flattener on
 205       do_test selectB-5.0 {
 206         execsql {
 207           CREATE INDEX i1 ON t1(a);
 208           CREATE INDEX i2 ON t1(b);
 209           CREATE INDEX i3 ON t1(c);
 210           CREATE INDEX i4 ON t2(d);
 211           CREATE INDEX i5 ON t2(e);
 212           CREATE INDEX i6 ON t2(f);
 213         }
 214       } {}
 215     }
 216     6 {
 217       optimization_control db query-flattener off
 218     }
 219   }
 220
 221   do_test selectB-$ii.1 {
 222     execsql {
 223       SELECT DISTINCT * FROM
 224         (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
 225       ORDER BY 1;
 226     }
 227   } {6 12 15 18 24}
 228
 229   do_test selectB-$ii.2 {
 230     execsql {
 231       SELECT c, count(*) FROM
 232         (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
 233       GROUP BY c ORDER BY 1;
 234     }
 235   } {6 2 12 1 15 1 18 1 24 1}
 236   do_test selectB-$ii.3 {
 237     execsql {
 238       SELECT c, count(*) FROM
 239         (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
 240       GROUP BY c HAVING count(*)>1;
 241     }
 242   } {6 2}
 243   do_test selectB-$ii.4 {
 244     execsql {
 245       SELECT t4.c, t3.a FROM
 246         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
 247       WHERE t3.a=14
 248       ORDER BY 1
 249     }
 250   } {6 14 6 14 12 14 15 14 18 14 24 14}
 251
 252   do_test selectB-$ii.5 {
 253     execsql {
 254       SELECT d FROM t2
 255       EXCEPT
 256       SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
 257     }
 258   } {}
 259   do_test selectB-$ii.6 {
 260     execsql {
 261       SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
 262       EXCEPT
 263       SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
 264     }
 265   } {}
 266   do_test selectB-$ii.7 {
 267     execsql {
 268       SELECT c FROM t1
 269       EXCEPT
 270       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 271     }
 272   } {12}
 273   do_test selectB-$ii.8 {
 274     execsql {
 275       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 276       EXCEPT
 277       SELECT c FROM t1
 278     }
 279   } {9 15 24 27}
 280   do_test selectB-$ii.9 {
 281     execsql {
 282       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 283       EXCEPT
 284       SELECT c FROM t1
 285       ORDER BY c DESC
 286     }
 287   } {27 24 15 9}
 288
 289   do_test selectB-$ii.10 {
 290     execsql {
 291       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 292       UNION
 293       SELECT c FROM t1
 294       ORDER BY c DESC
 295     }
 296   } {27 24 18 15 12 9 6}
 297   do_test selectB-$ii.11 {
 298     execsql {
 299       SELECT c FROM t1
 300       UNION
 301       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 302       ORDER BY c
 303     }
 304   } {6 9 12 15 18 24 27}
 305   do_test selectB-$ii.12 {
 306     execsql {
 307       SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
 308       ORDER BY c
 309     }
 310   } {6 9 12 15 18 18 24 27}
 311   do_test selectB-$ii.13 {
 312     execsql {
 313       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 314       UNION
 315       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 316       ORDER BY 1
 317     }
 318   } {6 9 15 18 24 27}
 319
 320   do_test selectB-$ii.14 {
 321     execsql {
 322       SELECT c FROM t1
 323       INTERSECT
 324       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 325       ORDER BY 1
 326     }
 327   } {6 18}
 328   do_test selectB-$ii.15 {
 329     execsql {
 330       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 331       INTERSECT
 332       SELECT c FROM t1
 333       ORDER BY 1
 334     }
 335   } {6 18}
 336   do_test selectB-$ii.16 {
 337     execsql {
 338       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 339       INTERSECT
 340       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
 341       ORDER BY 1
 342     }
 343   } {6 9 15 18 24 27}
 344
 345   do_test selectB-$ii.17 {
 346     execsql {
 347       SELECT * FROM (
 348         SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
 349       ) LIMIT 2
 350     }
 351   } {2 8}
 352
 353   do_test selectB-$ii.18 {
 354     execsql {
 355       SELECT * FROM (
 356         SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
 357       ) LIMIT 2
 358     }
 359   } {14 3}
 360
 361   do_test selectB-$ii.19 {
 362     execsql {
 363       SELECT * FROM (
 364         SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
 365       )
 366     }
 367   } {0 1 1 0}
 368
 369   do_test selectB-$ii.20 {
 370     execsql {
 371       SELECT DISTINCT * FROM (
 372         SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
 373       )
 374     }
 375   } {0 1}
 376
 377   do_test selectB-$ii.21 {
 378     execsql {
 379       SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
 380     }
 381   } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
 382
 383   do_test selectB-$ii.22 {
 384     execsql {
 385       SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
 386     }
 387   } {3 12 21 345}
 388
 389   do_test selectB-$ii.23 {
 390     execsql {
 391       SELECT x, y FROM (
 392         SELECT a AS x, b AS y FROM t1
 393         UNION ALL
 394         SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d)
 395         UNION ALL
 396         SELECT a*100, b*100 FROM t1
 397       ) ORDER BY 1;
 398     }
 399   } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600}
 400
 401   do_test selectB-$ii.24 {
 402     execsql {
 403       SELECT x, y FROM (
 404         SELECT a AS x, b AS y FROM t1
 405         UNION ALL
 406         SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
 407         UNION ALL
 408         SELECT a*100, b*100 FROM t1
 409       ) ORDER BY 1;
 410     }
 411   } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}
 412
 413   do_test selectB-$ii.25 {
 414     execsql {
 415       SELECT x+y FROM (
 416         SELECT a AS x, b AS y FROM t1
 417         UNION ALL
 418         SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
 419         UNION ALL
 420         SELECT a*100, b*100 FROM t1
 421       ) WHERE y+x NOT NULL ORDER BY 1;
 422     }
 423   } {6 18 30 260.2 600 1800 3000}
 424 }
 425
 426 finish_test