Annotation of embedaddon/sqlite3/test/select9.test, revision 1.1
1.1 ! misho 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:
! 419: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to select9.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test