test/select9.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: select9.test,v 1.4 2008/07/01 14:39:35 danielk1977 Exp $
  14
  15 # The tests in this file are focused on test compound SELECT statements
  16 # that have any or all of an ORDER BY, LIMIT or OFFSET clauses. As of
  17 # version 3.6.0, SQLite contains code to use SQL indexes where possible
  18 # to optimize such statements.
  19 #
  20
  21 # TODO Points:
  22 #
  23 #   * Are there any "column affinity" issues to consider?
  24
  25 set testdir [file dirname $argv0]
  26 source $testdir/tester.tcl
  27
  28 #-------------------------------------------------------------------------
  29 # test_compound_select TESTNAME SELECT RESULT
  30 #
  31 #   This command is used to run multiple LIMIT/OFFSET test cases based on
  32 #   the single SELECT statement passed as the second argument. The SELECT
  33 #   statement may not contain a LIMIT or OFFSET clause. This proc tests
  34 #   many statements of the form:
  35 #
  36 #     "$SELECT limit $X offset $Y"
  37 #
  38 #   for various values of $X and $Y.
  39 #
  40 #   The third argument, $RESULT, should contain the expected result of
  41 #   the command [execsql $SELECT].
  42 #
  43 #   The first argument, $TESTNAME, is used as the base test case name to
  44 #   pass to [do_test] for each individual LIMIT OFFSET test case.
  45 #
  46 proc test_compound_select {testname sql result} {
  47
  48   set nCol 1
  49   db eval $sql A {
  50     set nCol [llength $A(*)]
  51     break
  52   }
  53   set nRow [expr {[llength $result] / $nCol}]
  54
  55   set ::compound_sql $sql
  56   do_test $testname {
  57     execsql $::compound_sql
  58   } $result
  59 #return
  60
  61   set iLimitIncr  1
  62   set iOffsetIncr 1
  63   if {[info exists ::G(isquick)] && $::G(isquick) && $nRow>=5} {
  64     set iOffsetIncr [expr $nRow / 5]
  65     set iLimitIncr [expr $nRow / 5]
  66   }
  67
  68   set iLimitEnd   [expr $nRow+$iLimitIncr]
  69   set iOffsetEnd  [expr $nRow+$iOffsetIncr]
  70
  71   for {set iOffset 0} {$iOffset < $iOffsetEnd} {incr iOffset $iOffsetIncr} {
  72     for {set iLimit 0} {$iLimit < $iLimitEnd} {incr iLimit} {
  73
  74       set ::compound_sql "$sql LIMIT $iLimit"
  75       if {$iOffset != 0} {
  76         append ::compound_sql " OFFSET $iOffset"
  77       }
  78
  79       set iStart [expr {$iOffset*$nCol}]
  80       set iEnd [expr {($iOffset*$nCol) + ($iLimit*$nCol) -1}]
  81
  82       do_test $testname.limit=$iLimit.offset=$iOffset {
  83         execsql $::compound_sql
  84       } [lrange $result $iStart $iEnd]
  85     }
  86   }
  87 }
  88
  89 #-------------------------------------------------------------------------
  90 # test_compound_select_flippable TESTNAME SELECT RESULT
  91 #
  92 #   This command is for testing statements of the form:
  93 #
  94 #     <simple select 1> <compound op> <simple select 2> ORDER BY <order by>
  95 #
  96 #   where each <simple select> is a simple (non-compound) select statement
  97 #   and <compound op> is one of "INTERSECT", "UNION ALL" or "UNION".
  98 #
  99 #   This proc calls [test_compound_select] twice, once with the select
 100 #   statement as it is passed to this command, and once with the positions
 101 #   of <select statement 1> and <select statement 2> exchanged.
 102 #
 103 proc test_compound_select_flippable {testname sql result} {
 104   test_compound_select $testname $sql $result
 105
 106   set select [string trim $sql]
 107   set RE {(.*)(UNION ALL|INTERSECT|UNION)(.*)(ORDER BY.*)}
 108   set rc [regexp $RE $select -> s1 op s2 order_by]
 109   if {!$rc} {error "Statement is unflippable: $select"}
 110
 111   set flipsql "$s2 $op $s1 $order_by"
 112   test_compound_select $testname.flipped $flipsql $result
 113 }
 114
 115 #############################################################################
 116 # Begin tests.
 117 #
 118
 119 # Create and populate a sample database.
 120 #
 121 do_test select9-1.0 {
 122   execsql {
 123     CREATE TABLE t1(a, b, c);
 124     CREATE TABLE t2(d, e, f);
 125     BEGIN;
 126       INSERT INTO t1 VALUES(1,  'one',   'I');
 127       INSERT INTO t1 VALUES(3,  NULL,    NULL);
 128       INSERT INTO t1 VALUES(5,  'five',  'V');
 129       INSERT INTO t1 VALUES(7,  'seven', 'VII');
 130       INSERT INTO t1 VALUES(9,  NULL,    NULL);
 131       INSERT INTO t1 VALUES(2,  'two',   'II');
 132       INSERT INTO t1 VALUES(4,  'four',  'IV');
 133       INSERT INTO t1 VALUES(6,  NULL,    NULL);
 134       INSERT INTO t1 VALUES(8,  'eight', 'VIII');
 135       INSERT INTO t1 VALUES(10, 'ten',   'X');
 136
 137       INSERT INTO t2 VALUES(1,  'two',      'IV');
 138       INSERT INTO t2 VALUES(2,  'four',     'VIII');
 139       INSERT INTO t2 VALUES(3,  NULL,       NULL);
 140       INSERT INTO t2 VALUES(4,  'eight',    'XVI');
 141       INSERT INTO t2 VALUES(5,  'ten',      'XX');
 142       INSERT INTO t2 VALUES(6,  NULL,       NULL);
 143       INSERT INTO t2 VALUES(7,  'fourteen', 'XXVIII');
 144       INSERT INTO t2 VALUES(8,  'sixteen',  'XXXII');
 145       INSERT INTO t2 VALUES(9,  NULL,       NULL);
 146       INSERT INTO t2 VALUES(10, 'twenty',   'XL');
 147
 148     COMMIT;
 149   }
 150 } {}
 151
 152 # Each iteration of this loop runs the same tests with a different set
 153 # of indexes present within the database schema. The data returned by
 154 # the compound SELECT statements in the test cases should be the same
 155 # in each case.
 156 #
 157 set iOuterLoop 1
 158 foreach indexes [list {
 159   /* Do not create any indexes. */
 160 } {
 161   CREATE INDEX i1 ON t1(a)
 162 } {
 163   CREATE INDEX i2 ON t1(b)
 164 } {
 165   CREATE INDEX i3 ON t2(d)
 166 } {
 167   CREATE INDEX i4 ON t2(e)
 168 }] {
 169
 170   do_test select9-1.$iOuterLoop.1 {
 171     execsql $indexes
 172   } {}
 173
 174   # Test some 2-way UNION ALL queries. No WHERE clauses.
 175   #
 176   test_compound_select select9-1.$iOuterLoop.2 {
 177     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2
 178   } {1 one 3 {} 5 five 7 seven 9 {} 2 two 4 four 6 {} 8 eight 10 ten 1 two 2 four 3 {} 4 eight 5 ten 6 {} 7 fourteen 8 sixteen 9 {} 10 twenty}
 179   test_compound_select select9-1.$iOuterLoop.3 {
 180     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1
 181   } {1 one 1 two 2 two 2 four 3 {} 3 {} 4 four 4 eight 5 five 5 ten 6 {} 6 {} 7 seven 7 fourteen 8 eight 8 sixteen 9 {} 9 {} 10 ten 10 twenty}
 182   test_compound_select select9-1.$iOuterLoop.4 {
 183     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2
 184   } {3 {} 9 {} 6 {} 3 {} 6 {} 9 {} 8 eight 4 eight 5 five 4 four 2 four 7 fourteen 1 one 7 seven 8 sixteen 10 ten 5 ten 10 twenty 2 two 1 two}
 185   test_compound_select_flippable select9-1.$iOuterLoop.5 {
 186     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 1, 2
 187   } {1 one 1 two 2 four 2 two 3 {} 3 {} 4 eight 4 four 5 five 5 ten 6 {} 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 9 {} 10 ten 10 twenty}
 188   test_compound_select_flippable select9-1.$iOuterLoop.6 {
 189     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY 2, 1
 190   } {3 {} 3 {} 6 {} 6 {} 9 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}
 191
 192   # Test some 2-way UNION queries.
 193   #
 194   test_compound_select select9-1.$iOuterLoop.7 {
 195     SELECT a, b FROM t1 UNION SELECT d, e FROM t2
 196   } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}
 197
 198   test_compound_select select9-1.$iOuterLoop.8 {
 199     SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1
 200   } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}
 201
 202   test_compound_select select9-1.$iOuterLoop.9 {
 203     SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2
 204   } {3 {} 6 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}
 205
 206   test_compound_select_flippable select9-1.$iOuterLoop.10 {
 207     SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 1, 2
 208   } {1 one 1 two 2 four 2 two 3 {} 4 eight 4 four 5 five 5 ten 6 {} 7 fourteen 7 seven 8 eight 8 sixteen 9 {} 10 ten 10 twenty}
 209
 210   test_compound_select_flippable select9-1.$iOuterLoop.11 {
 211     SELECT a, b FROM t1 UNION SELECT d, e FROM t2 ORDER BY 2, 1
 212   } {3 {} 6 {} 9 {} 4 eight 8 eight 5 five 2 four 4 four 7 fourteen 1 one 7 seven 8 sixteen 5 ten 10 ten 10 twenty 1 two 2 two}
 213
 214   # Test some 2-way INTERSECT queries.
 215   #
 216   test_compound_select select9-1.$iOuterLoop.11 {
 217     SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2
 218   } {3 {} 6 {} 9 {}}
 219   test_compound_select_flippable select9-1.$iOuterLoop.12 {
 220     SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1
 221   } {3 {} 6 {} 9 {}}
 222   test_compound_select select9-1.$iOuterLoop.13 {
 223     SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2
 224   } {3 {} 6 {} 9 {}}
 225   test_compound_select_flippable select9-1.$iOuterLoop.14 {
 226     SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 2, 1
 227   } {3 {} 6 {} 9 {}}
 228   test_compound_select_flippable select9-1.$iOuterLoop.15 {
 229     SELECT a, b FROM t1 INTERSECT SELECT d, e FROM t2 ORDER BY 1, 2
 230   } {3 {} 6 {} 9 {}}
 231
 232   # Test some 2-way EXCEPT queries.
 233   #
 234   test_compound_select select9-1.$iOuterLoop.16 {
 235     SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2
 236   } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}
 237
 238   test_compound_select select9-1.$iOuterLoop.17 {
 239     SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1
 240   } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}
 241
 242   test_compound_select select9-1.$iOuterLoop.18 {
 243     SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2
 244   } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}
 245
 246   test_compound_select select9-1.$iOuterLoop.19 {
 247     SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 1, 2
 248   } {1 one 2 two 4 four 5 five 7 seven 8 eight 10 ten}
 249
 250   test_compound_select select9-1.$iOuterLoop.20 {
 251     SELECT a, b FROM t1 EXCEPT SELECT d, e FROM t2 ORDER BY 2, 1
 252   } {8 eight 5 five 4 four 1 one 7 seven 10 ten 2 two}
 253
 254   incr iOuterLoop
 255 }
 256
 257 do_test select9-2.0 {
 258   execsql {
 259     DROP INDEX i1;
 260     DROP INDEX i2;
 261     DROP INDEX i3;
 262     DROP INDEX i4;
 263   }
 264 } {}
 265
 266 proc reverse {lhs rhs} {
 267   return [string compare $rhs $lhs]
 268 }
 269 db collate reverse reverse
 270
 271 # This loop is similar to the previous one (test cases select9-1.*)
 272 # except that the simple select statements have WHERE clauses attached
 273 # to them. Sometimes the WHERE clause may be satisfied using the same
 274 # index used for ORDER BY, sometimes not.
 275 #
 276 set iOuterLoop 1
 277 foreach indexes [list {
 278   /* Do not create any indexes. */
 279 } {
 280   CREATE INDEX i1 ON t1(a)
 281 } {
 282   DROP INDEX i1;
 283   CREATE INDEX i1 ON t1(b, a)
 284 } {
 285   CREATE INDEX i2 ON t2(d DESC, e COLLATE REVERSE ASC);
 286 } {
 287   CREATE INDEX i3 ON t1(a DESC);
 288 }] {
 289   do_test select9-2.$iOuterLoop.1 {
 290     execsql $indexes
 291   } {}
 292
 293   test_compound_select_flippable select9-2.$iOuterLoop.2 {
 294     SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 1
 295   } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL}
 296
 297   test_compound_select_flippable select9-2.$iOuterLoop.2 {
 298     SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1
 299   } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II}
 300
 301   test_compound_select_flippable select9-2.$iOuterLoop.3 {
 302     SELECT * FROM t1 WHERE a<5 UNION SELECT * FROM t2 WHERE d>=5
 303     ORDER BY 2 COLLATE reverse, 1
 304   } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV}
 305
 306   test_compound_select_flippable select9-2.$iOuterLoop.4 {
 307     SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 1
 308   } {1 one I 2 two II 3 {} {} 4 four IV 5 ten XX 6 {} {} 7 fourteen XXVIII 8 sixteen XXXII 9 {} {} 10 twenty XL}
 309
 310   test_compound_select_flippable select9-2.$iOuterLoop.5 {
 311     SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5 ORDER BY 2, 1
 312   } {3 {} {} 6 {} {} 9 {} {} 4 four IV 7 fourteen XXVIII 1 one I 8 sixteen XXXII 5 ten XX 10 twenty XL 2 two II}
 313
 314   test_compound_select_flippable select9-2.$iOuterLoop.6 {
 315     SELECT * FROM t1 WHERE a<5 UNION ALL SELECT * FROM t2 WHERE d>=5
 316     ORDER BY 2 COLLATE reverse, 1
 317   } {3 {} {} 6 {} {} 9 {} {} 2 two II 10 twenty XL 5 ten XX 8 sixteen XXXII 1 one I 7 fourteen XXVIII 4 four IV}
 318
 319   test_compound_select select9-2.$iOuterLoop.4 {
 320     SELECT a FROM t1 WHERE a<8 EXCEPT SELECT d FROM t2 WHERE d<=3 ORDER BY 1
 321   } {4 5 6 7}
 322
 323   test_compound_select select9-2.$iOuterLoop.4 {
 324     SELECT a FROM t1 WHERE a<8 INTERSECT SELECT d FROM t2 WHERE d<=3 ORDER BY 1
 325   } {1 2 3}
 326
 327 }
 328
 329 do_test select9-2.X {
 330   execsql {
 331     DROP INDEX i1;
 332     DROP INDEX i2;
 333     DROP INDEX i3;
 334   }
 335 } {}
 336
 337 # This procedure executes the SQL.  Then it checks the generated program
 338 # for the SQL and appends a "nosort" to the result if the program contains the
 339 # SortCallback opcode.  If the program does not contain the SortCallback
 340 # opcode it appends "sort"
 341 #
 342 proc cksort {sql} {
 343   set ::sqlite_sort_count 0
 344   set data [execsql $sql]
 345   if {$::sqlite_sort_count} {set x sort} {set x nosort}
 346   lappend data $x
 347   return $data
 348 }
 349
 350 # If the right indexes exist, the following query:
 351 #
 352 #     SELECT t1.a FROM t1 UNION ALL SELECT t2.d FROM t2 ORDER BY 1
 353 #
 354 # can use indexes to run without doing a in-memory sort operation.
 355 # This block of tests (select9-3.*) is used to check if the same
 356 # is possible with:
 357 #
 358 #     CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2
 359 #     SELECT a FROM v1 ORDER BY 1
 360 #
 361 # It turns out that it is.
 362 #
 363 do_test select9-3.1 {
 364   cksort { SELECT a FROM t1 ORDER BY 1 }
 365 } {1 2 3 4 5 6 7 8 9 10 sort}
 366 do_test select9-3.2 {
 367   execsql { CREATE INDEX i1 ON t1(a) }
 368   cksort { SELECT a FROM t1 ORDER BY 1 }
 369 } {1 2 3 4 5 6 7 8 9 10 nosort}
 370 do_test select9-3.3 {
 371   cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
 372 } {1 1 2 2 3 sort}
 373 do_test select9-3.4 {
 374   execsql { CREATE INDEX i2 ON t2(d) }
 375   cksort { SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
 376 } {1 1 2 2 3 nosort}
 377 do_test select9-3.5 {
 378   execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION ALL SELECT d FROM t2 }
 379   cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }
 380 } {1 1 2 2 3 nosort}
 381 do_test select9-3.X {
 382   execsql {
 383     DROP INDEX i1;
 384     DROP INDEX i2;
 385     DROP VIEW v1;
 386   }
 387 } {}
 388
 389 # This block of tests is the same as the preceding one, except that
 390 # "UNION" is tested instead of "UNION ALL".
 391 #
 392 do_test select9-4.1 {
 393   cksort { SELECT a FROM t1 ORDER BY 1 }
 394 } {1 2 3 4 5 6 7 8 9 10 sort}
 395 do_test select9-4.2 {
 396   execsql { CREATE INDEX i1 ON t1(a) }
 397   cksort { SELECT a FROM t1 ORDER BY 1 }
 398 } {1 2 3 4 5 6 7 8 9 10 nosort}
 399 do_test select9-4.3 {
 400   cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
 401 } {1 2 3 4 5 sort}
 402 do_test select9-4.4 {
 403   execsql { CREATE INDEX i2 ON t2(d) }
 404   cksort { SELECT a FROM t1 UNION SELECT d FROM t2 ORDER BY 1 LIMIT 5 }
 405 } {1 2 3 4 5 nosort}
 406 do_test select9-4.5 {
 407   execsql { CREATE VIEW v1 AS SELECT a FROM t1 UNION SELECT d FROM t2 }
 408   cksort { SELECT a FROM v1 ORDER BY 1 LIMIT 5 }
 409 } {1 2 3 4 5 sort}
 410 do_test select9-4.X {
 411   execsql {
 412     DROP INDEX i1;
 413     DROP INDEX i2;
 414     DROP VIEW v1;
 415   }
 416 } {}
 417
 418 # Testing to make sure that queries involving a view of a compound select
 419 # are planned efficiently.  This detects a problem reported on the mailing
 420 # list on 2012-04-26.  See
 421 #
 422 #  http://www.mail-archive.com/sqlite-users%40sqlite.org/msg69746.html
 423 #
 424 # For additional information.
 425 #
 426 do_test select9-5.1 {
 427   db eval {
 428     CREATE TABLE t51(x, y);
 429     CREATE TABLE t52(x, y);
 430     CREATE VIEW v5 as
 431        SELECT x, y FROM t51
 432        UNION ALL
 433        SELECT x, y FROM t52;
 434     CREATE INDEX t51x ON t51(x);
 435     CREATE INDEX t52x ON t52(x);
 436     EXPLAIN QUERY PLAN
 437        SELECT * FROM v5 WHERE x='12345' ORDER BY y;
 438   }
 439 } {~/SCAN/}  ;# Uses indices with "*"
 440 do_test select9-5.2 {
 441   db eval {
 442     EXPLAIN QUERY PLAN
 443        SELECT x, y FROM v5 WHERE x='12345' ORDER BY y;
 444   }
 445 } {~/SCAN/}  ;# Uses indices with "x, y"
 446 do_test select9-5.3 {
 447   db eval {
 448     EXPLAIN QUERY PLAN
 449        SELECT x, y FROM v5 WHERE +x='12345' ORDER BY y;
 450   }
 451 } {/SCAN/}   ;# Full table scan if the "+x" prevents index usage.
 452
 453 # 2013-07-09:  Ticket [490a4b7235624298]:
 454 # "WHERE 0" on the first element of a UNION causes an assertion fault
 455 #
 456 do_execsql_test select9-6.1 {
 457   CREATE TABLE t61(a);
 458   CREATE TABLE t62(b);
 459   INSERT INTO t61 VALUES(111);
 460   INSERT INTO t62 VALUES(222);
 461   SELECT a FROM t61 WHERE 0 UNION SELECT b FROM t62;
 462 } {222}
 463 do_execsql_test select9-6.2 {
 464   SELECT a FROM t61 WHERE 0 UNION ALL SELECT b FROM t62;
 465 } {222}
 466 do_execsql_test select9-6.3 {
 467   SELECT a FROM t61 UNION SELECT b FROM t62 WHERE 0;
 468 } {111}
 469
 470
 471
 472 finish_test