Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Virtual tables now always report 25 rows instead of 0 rows in the EXPLAIN QUERY PLAN output. Adjust tests accordingly. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | nextgen-query-plan-exp |
Files: | files | file ages | folders |
SHA1: |
7d91f688815597ff65ec04b7daa21cb9 |
User & Date: | drh 2013-06-03 15:24:11.404 |
Context
2013-06-03
| ||
15:34 | Fix an issue that was causing ORDER BY DESC to come out in ascending order. (check-in: 0298401240 user: drh tags: nextgen-query-plan-exp) | |
15:24 | Virtual tables now always report 25 rows instead of 0 rows in the EXPLAIN QUERY PLAN output. Adjust tests accordingly. (check-in: 7d91f68881 user: drh tags: nextgen-query-plan-exp) | |
15:07 | Set the WHERE_UNIQUE flag on loops that can only run once. (check-in: 510f4d8ecf user: drh tags: nextgen-query-plan-exp) | |
Changes
Changes to test/fts3aux1.test.
︙ | ︙ | |||
101 102 103 104 105 106 107 | db func rec rec # Use EQP to show that the WHERE expression "term='braid'" uses a different # index number (1) than "+term='braid'" (0). # do_execsql_test 2.1.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' | | | | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | db func rec rec # Use EQP to show that the WHERE expression "term='braid'" uses a different # index number (1) than "+term='braid'" (0). # do_execsql_test 2.1.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} } do_execsql_test 2.1.1.2 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid' } {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}} # Now show that using "term='braid'" means the virtual table returns # only 1 row to SQLite, but "+term='braid'" means all 19 are returned. # do_test 2.1.2.1 { set cnt 0 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' } |
︙ | ︙ | |||
150 151 152 153 154 155 156 | # Special case: term=NULL # do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} do_execsql_test 2.2.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' | | | | | | | | 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | # Special case: term=NULL # do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} do_execsql_test 2.2.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~25 rows)} } do_execsql_test 2.2.1.2 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } do_execsql_test 2.2.1.3 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~25 rows)} } do_execsql_test 2.2.1.4 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } do_execsql_test 2.2.1.5 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~25 rows)} } do_execsql_test 2.2.1.6 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} } do_test 2.2.2.1 { set cnt 0 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' } set cnt } {18} do_test 2.2.2.2 { |
︙ | ︙ | |||
331 332 333 334 335 336 337 | 5 1 "ORDER BY documents" 6 1 "ORDER BY documents DESC" 7 1 "ORDER BY occurrences ASC" 8 1 "ORDER BY occurrences" 9 1 "ORDER BY occurrences DESC" } { | | | 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 | 5 1 "ORDER BY documents" 6 1 "ORDER BY documents DESC" 7 1 "ORDER BY occurrences ASC" 8 1 "ORDER BY occurrences" 9 1 "ORDER BY occurrences DESC" } { set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}] if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } set sql "SELECT * FROM terms $orderby" do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res } #------------------------------------------------------------------------- |
︙ | ︙ | |||
407 408 409 410 411 412 413 | uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r] } do_plansql_test 4.2 { SELECT y FROM x2, terms WHERE y = term AND col = '*' } { 0 0 0 {SCAN TABLE x2 (~1000000 rows)} | | | | | | 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 | uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r] } do_plansql_test 4.2 { SELECT y FROM x2, terms WHERE y = term AND col = '*' } { 0 0 0 {SCAN TABLE x2 (~1000000 rows)} 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} a b c d e f g h i j k l } do_plansql_test 4.3 { SELECT y FROM terms, x2 WHERE y = term AND col = '*' } { 0 0 1 {SCAN TABLE x2 (~1000000 rows)} 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} a b c d e f g h i j k l } do_plansql_test 4.4 { SELECT y FROM x3, terms WHERE y = term AND col = '*' } { 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} a b c d e f g h i j k l } do_plansql_test 4.5 { SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*' } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} a k l } #------------------------------------------------------------------------- # The following tests check that fts4aux can handle an fts table with an # odd name (one that requires quoting for use in SQL statements). And that |
︙ | ︙ | |||
515 516 517 518 519 520 521 | do_test 8.2 { execsql {DETACH att} catchsql { SELECT * FROM aux2 } } {1 {SQL logic error or missing database}} finish_test | < | 515 516 517 518 519 520 521 | do_test 8.2 { execsql {DETACH att} catchsql { SELECT * FROM aux2 } } {1 {SQL logic error or missing database}} finish_test |
Changes to test/fts3query.test.
︙ | ︙ | |||
115 116 117 118 119 120 121 | CREATE TABLE bt(title); } } {} do_eqp_test fts3query-4.2 { SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date } { 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} | | | | 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | CREATE TABLE bt(title); } } {} do_eqp_test fts3query-4.2 { SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date } { 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)} } do_eqp_test fts3query-4.3 { SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date } { 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)} } do_eqp_test fts3query-4.4 { SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date } { 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } |
︙ | ︙ | |||
206 207 208 209 210 211 212 | 7 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1, 5) FROM t3 WHERE t3 MATCH 'gestures'" {{ZZZthe hand XXXgesturesYYY (called beatsZZZ}} } finish_test | < | 206 207 208 209 210 211 212 | 7 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1, 5) FROM t3 WHERE t3 MATCH 'gestures'" {{ZZZthe hand XXXgesturesYYY (called beatsZZZ}} } finish_test |