Annotation of embedaddon/sqlite3/test/eqp.test, revision 1.1
1.1 ! misho 1: # 2010 November 6
! 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: #
! 12:
! 13: set testdir [file dirname $argv0]
! 14: source $testdir/tester.tcl
! 15:
! 16: set testprefix eqp
! 17:
! 18: #-------------------------------------------------------------------------
! 19: #
! 20: # eqp-1.*: Assorted tests.
! 21: # eqp-2.*: Tests for single select statements.
! 22: # eqp-3.*: Select statements that execute sub-selects.
! 23: # eqp-4.*: Compound select statements.
! 24: # ...
! 25: # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count).
! 26: #
! 27:
! 28: proc det {args} { uplevel do_eqp_test $args }
! 29:
! 30: do_execsql_test 1.1 {
! 31: CREATE TABLE t1(a, b);
! 32: CREATE INDEX i1 ON t1(a);
! 33: CREATE INDEX i2 ON t1(b);
! 34: CREATE TABLE t2(a, b);
! 35: CREATE TABLE t3(a, b);
! 36: }
! 37:
! 38: do_eqp_test 1.2 {
! 39: SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
! 40: } {
! 41: 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
! 42: 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}
! 43: 0 1 0 {SCAN TABLE t2 (~1000000 rows)}
! 44: }
! 45: do_eqp_test 1.3 {
! 46: SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
! 47: } {
! 48: 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 49: 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
! 50: 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}
! 51: }
! 52: do_eqp_test 1.3 {
! 53: SELECT a FROM t1 ORDER BY a
! 54: } {
! 55: 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
! 56: }
! 57: do_eqp_test 1.4 {
! 58: SELECT a FROM t1 ORDER BY +a
! 59: } {
! 60: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 61: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 62: }
! 63: do_eqp_test 1.5 {
! 64: SELECT a FROM t1 WHERE a=4
! 65: } {
! 66: 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}
! 67: }
! 68: do_eqp_test 1.6 {
! 69: SELECT DISTINCT count(*) FROM t3 GROUP BY a;
! 70: } {
! 71: 0 0 0 {SCAN TABLE t3 (~1000000 rows)}
! 72: 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
! 73: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
! 74: }
! 75:
! 76: do_eqp_test 1.7 {
! 77: SELECT * FROM t3 JOIN (SELECT 1)
! 78: } {
! 79: 0 0 1 {SCAN SUBQUERY 1 (~1 rows)}
! 80: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
! 81: }
! 82: do_eqp_test 1.8 {
! 83: SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)
! 84: } {
! 85: 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
! 86: 0 0 1 {SCAN SUBQUERY 1 (~2 rows)}
! 87: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
! 88: }
! 89: do_eqp_test 1.9 {
! 90: SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17)
! 91: } {
! 92: 3 0 0 {SCAN TABLE t3 (~1000000 rows)}
! 93: 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)}
! 94: 0 0 1 {SCAN SUBQUERY 1 (~17 rows)}
! 95: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
! 96: }
! 97: do_eqp_test 1.10 {
! 98: SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17)
! 99: } {
! 100: 3 0 0 {SCAN TABLE t3 (~1000000 rows)}
! 101: 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)}
! 102: 0 0 1 {SCAN SUBQUERY 1 (~1 rows)}
! 103: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
! 104: }
! 105:
! 106: do_eqp_test 1.11 {
! 107: SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17)
! 108: } {
! 109: 3 0 0 {SCAN TABLE t3 (~1000000 rows)}
! 110: 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)}
! 111: 0 0 1 {SCAN SUBQUERY 1 (~17 rows)}
! 112: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
! 113: }
! 114:
! 115: #-------------------------------------------------------------------------
! 116: # Test cases eqp-2.* - tests for single select statements.
! 117: #
! 118: drop_all_tables
! 119: do_execsql_test 2.1 {
! 120: CREATE TABLE t1(x, y);
! 121:
! 122: CREATE TABLE t2(x, y);
! 123: CREATE INDEX t2i1 ON t2(x);
! 124: }
! 125:
! 126: det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
! 127: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 128: 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
! 129: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
! 130: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 131: }
! 132: det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
! 133: 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
! 134: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
! 135: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 136: }
! 137: det 2.2.3 "SELECT DISTINCT * FROM t1" {
! 138: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 139: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
! 140: }
! 141: det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
! 142: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 143: 0 1 1 {SCAN TABLE t2 (~1000000 rows)}
! 144: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
! 145: }
! 146: det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
! 147: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 148: 0 1 1 {SCAN TABLE t2 (~1000000 rows)}
! 149: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
! 150: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 151: }
! 152: det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
! 153: 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
! 154: 0 1 0 {SCAN TABLE t1 (~1000000 rows)}
! 155: }
! 156:
! 157: det 2.3.1 "SELECT max(x) FROM t2" {
! 158: 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
! 159: }
! 160: det 2.3.2 "SELECT min(x) FROM t2" {
! 161: 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
! 162: }
! 163: det 2.3.3 "SELECT min(x), max(x) FROM t2" {
! 164: 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 165: }
! 166:
! 167: det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
! 168: 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
! 169: }
! 170:
! 171:
! 172:
! 173: #-------------------------------------------------------------------------
! 174: # Test cases eqp-3.* - tests for select statements that use sub-selects.
! 175: #
! 176: do_eqp_test 3.1.1 {
! 177: SELECT (SELECT x FROM t1 AS sub) FROM t1;
! 178: } {
! 179: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 180: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
! 181: 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
! 182: }
! 183: do_eqp_test 3.1.2 {
! 184: SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
! 185: } {
! 186: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 187: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
! 188: 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
! 189: }
! 190: do_eqp_test 3.1.3 {
! 191: SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
! 192: } {
! 193: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 194: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
! 195: 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
! 196: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 197: }
! 198: do_eqp_test 3.1.4 {
! 199: SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
! 200: } {
! 201: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 202: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
! 203: 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
! 204: }
! 205:
! 206: det 3.2.1 {
! 207: SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
! 208: } {
! 209: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 210: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 211: 0 0 0 {SCAN SUBQUERY 1 (~10 rows)}
! 212: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 213: }
! 214: det 3.2.2 {
! 215: SELECT * FROM
! 216: (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
! 217: (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
! 218: ORDER BY x2.y LIMIT 5
! 219: } {
! 220: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 221: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 222: 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)}
! 223: 0 0 0 {SCAN SUBQUERY 1 AS x1 (~10 rows)}
! 224: 0 1 1 {SCAN SUBQUERY 2 AS x2 (~10 rows)}
! 225: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 226: }
! 227:
! 228: det 3.3.1 {
! 229: SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
! 230: } {
! 231: 0 0 0 {SCAN TABLE t1 (~100000 rows)}
! 232: 0 0 0 {EXECUTE LIST SUBQUERY 1}
! 233: 1 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 234: }
! 235: det 3.3.2 {
! 236: SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
! 237: } {
! 238: 0 0 0 {SCAN TABLE t1 (~500000 rows)}
! 239: 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
! 240: 1 0 0 {SCAN TABLE t2 (~500000 rows)}
! 241: }
! 242: det 3.3.3 {
! 243: SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
! 244: } {
! 245: 0 0 0 {SCAN TABLE t1 (~500000 rows)}
! 246: 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1}
! 247: 1 0 0 {SCAN TABLE t2 (~500000 rows)}
! 248: }
! 249:
! 250: #-------------------------------------------------------------------------
! 251: # Test cases eqp-4.* - tests for composite select statements.
! 252: #
! 253: do_eqp_test 4.1.1 {
! 254: SELECT * FROM t1 UNION ALL SELECT * FROM t2
! 255: } {
! 256: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 257: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 258: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
! 259: }
! 260: do_eqp_test 4.1.2 {
! 261: SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
! 262: } {
! 263: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 264: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 265: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 266: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 267: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
! 268: }
! 269: do_eqp_test 4.1.3 {
! 270: SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
! 271: } {
! 272: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 273: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 274: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 275: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 276: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)}
! 277: }
! 278: do_eqp_test 4.1.4 {
! 279: SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
! 280: } {
! 281: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 282: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 283: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 284: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 285: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)}
! 286: }
! 287: do_eqp_test 4.1.5 {
! 288: SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
! 289: } {
! 290: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 291: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 292: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 293: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 294: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
! 295: }
! 296:
! 297: do_eqp_test 4.2.2 {
! 298: SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
! 299: } {
! 300: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 301: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 302: 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)}
! 303: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
! 304: }
! 305: do_eqp_test 4.2.3 {
! 306: SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
! 307: } {
! 308: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 309: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 310: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 311: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 312: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)}
! 313: }
! 314: do_eqp_test 4.2.4 {
! 315: SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
! 316: } {
! 317: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 318: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 319: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 320: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 321: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)}
! 322: }
! 323: do_eqp_test 4.2.5 {
! 324: SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
! 325: } {
! 326: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 327: 1 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 328: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 329: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 330: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
! 331: }
! 332:
! 333: do_eqp_test 4.3.1 {
! 334: SELECT x FROM t1 UNION SELECT x FROM t2
! 335: } {
! 336: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 337: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 338: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
! 339: }
! 340:
! 341: do_eqp_test 4.3.2 {
! 342: SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
! 343: } {
! 344: 2 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 345: 3 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 346: 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
! 347: 4 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 348: 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
! 349: }
! 350: do_eqp_test 4.3.3 {
! 351: SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
! 352: } {
! 353: 2 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 354: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 355: 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
! 356: 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)}
! 357: 4 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 358: 4 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 359: 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
! 360: }
! 361:
! 362: #-------------------------------------------------------------------------
! 363: # This next block of tests verifies that the examples on the
! 364: # lang_explain.html page are correct.
! 365: #
! 366: drop_all_tables
! 367:
! 368: # EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b
! 369: # FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows)
! 370: do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
! 371: det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
! 372: 0 0 0 {SCAN TABLE t1 (~100000 rows)}
! 373: }
! 374:
! 375: # EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a);
! 376: # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
! 377: # 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
! 378: do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
! 379: det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
! 380: 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
! 381: }
! 382:
! 383: # EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b);
! 384: # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
! 385: # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
! 386: do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
! 387: det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
! 388: 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
! 389: }
! 390:
! 391: # EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
! 392: # t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1
! 393: # USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2
! 394: # (~1000000 rows)
! 395: do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
! 396: det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
! 397: 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)}
! 398: 0 1 1 {SCAN TABLE t2 (~1000000 rows)}
! 399: }
! 400:
! 401: # EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
! 402: # t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1
! 403: # USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2
! 404: # (~1000000 rows)
! 405: det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
! 406: 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)}
! 407: 0 1 0 {SCAN TABLE t2 (~1000000 rows)}
! 408: }
! 409:
! 410: # EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b);
! 411: # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
! 412: # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
! 413: # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
! 414: do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
! 415: det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
! 416: 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
! 417: 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
! 418: }
! 419:
! 420: # EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d
! 421: # FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP
! 422: # B-TREE FOR ORDER BY
! 423: det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
! 424: 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 425: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 426: }
! 427:
! 428: # EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c);
! 429: # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
! 430: # 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows)
! 431: do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
! 432: det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
! 433: 0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)}
! 434: }
! 435:
! 436: # EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT
! 437: # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
! 438: # 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
! 439: # 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
! 440: # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
! 441: # INDEX i3 (b=?) (~10 rows)
! 442: det 5.9 {
! 443: SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
! 444: } {
! 445: 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 446: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
! 447: 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
! 448: 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
! 449: 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
! 450: }
! 451:
! 452: # EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT
! 453: # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
! 454: # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN
! 455: # SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY
! 456: det 5.10 {
! 457: SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
! 458: } {
! 459: 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
! 460: 0 0 0 {SCAN SUBQUERY 1 (~100 rows)}
! 461: 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
! 462: }
! 463:
! 464: # EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
! 465: # (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
! 466: # (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
! 467: det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
! 468: 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
! 469: 0 1 1 {SCAN TABLE t1 (~1000000 rows)}
! 470: }
! 471:
! 472: # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
! 473: # t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
! 474: # 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
! 475: # USING TEMP B-TREE (UNION)
! 476: det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
! 477: 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 478: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 479: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
! 480: }
! 481:
! 482: # EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
! 483: # t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
! 484: # COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows)
! 485: # 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2
! 486: # (EXCEPT)
! 487: det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
! 488: 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
! 489: 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
! 490: 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 491: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
! 492: }
! 493:
! 494:
! 495: #-------------------------------------------------------------------------
! 496: # The following tests - eqp-6.* - test that the example C code on
! 497: # documentation page eqp.html works. The C code is duplicated in test1.c
! 498: # and wrapped in Tcl command [print_explain_query_plan]
! 499: #
! 500: set boilerplate {
! 501: proc explain_query_plan {db sql} {
! 502: set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY]
! 503: print_explain_query_plan $stmt
! 504: sqlite3_finalize $stmt
! 505: }
! 506: sqlite3 db test.db
! 507: explain_query_plan db {%SQL%}
! 508: db close
! 509: exit
! 510: }
! 511:
! 512: # Do a "Print Explain Query Plan" test.
! 513: proc do_peqp_test {tn sql res} {
! 514: set fd [open script.tcl w]
! 515: puts $fd [string map [list %SQL% $sql] $::boilerplate]
! 516: close $fd
! 517:
! 518: uplevel do_test $tn [list {
! 519: set fd [open "|[info nameofexec] script.tcl"]
! 520: set data [read $fd]
! 521: close $fd
! 522: set data
! 523: }] [list $res]
! 524: }
! 525:
! 526: do_peqp_test 6.1 {
! 527: SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1
! 528: } [string trimleft {
! 529: 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)
! 530: 2 0 0 SCAN TABLE t2 (~1000000 rows)
! 531: 2 0 0 USE TEMP B-TREE FOR ORDER BY
! 532: 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
! 533: }]
! 534:
! 535: #-------------------------------------------------------------------------
! 536: # The following tests - eqp-7.* - test that queries that use the OP_Count
! 537: # optimization return something sensible with EQP.
! 538: #
! 539: drop_all_tables
! 540:
! 541: do_execsql_test 7.0 {
! 542: CREATE TABLE t1(a, b);
! 543: CREATE TABLE t2(a, b);
! 544: CREATE INDEX i1 ON t2(a);
! 545: }
! 546:
! 547: det 7.1 "SELECT count(*) FROM t1" {
! 548: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 549: }
! 550:
! 551: det 7.2 "SELECT count(*) FROM t2" {
! 552: 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~1000000 rows)}
! 553: }
! 554:
! 555: do_execsql_test 7.3 {
! 556: INSERT INTO t1 VALUES(1, 2);
! 557: INSERT INTO t1 VALUES(3, 4);
! 558:
! 559: INSERT INTO t2 VALUES(1, 2);
! 560: INSERT INTO t2 VALUES(3, 4);
! 561: INSERT INTO t2 VALUES(5, 6);
! 562:
! 563: ANALYZE;
! 564: }
! 565:
! 566: db close
! 567: sqlite3 db test.db
! 568:
! 569: det 7.4 "SELECT count(*) FROM t1" {
! 570: 0 0 0 {SCAN TABLE t1 (~2 rows)}
! 571: }
! 572:
! 573: det 7.5 "SELECT count(*) FROM t2" {
! 574: 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~3 rows)}
! 575: }
! 576:
! 577:
! 578: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to eqp.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