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 | SQL 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 |
Context
|
2013-06-03
| ||
| 15:34 | Fix an issue that was causing ORDER BY DESC to come out in ascending order. check-in: 02984012 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: 7d91f688 user: drh tags: nextgen-query-plan-exp | |
| 15:07 | Set the WHERE_UNIQUE flag on loops that can only run once. check-in: 510f4d8e user: drh tags: nextgen-query-plan-exp | |
Changes
Changes to test/fts3aux1.test.
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 ... 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 ... 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 ... 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 ... 515 516 517 518 519 520 521 522 |
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: (~0 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: (~0 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' }
................................................................................
# 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: (~0 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: (~0 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: (~0 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: (~0 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: (~0 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: (~0 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 {
................................................................................
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: (~0 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
}
#-------------------------------------------------------------------------
................................................................................
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: (~0 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: (~0 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: (~0 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: (~0 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
................................................................................
do_test 8.2 {
execsql {DETACH att}
catchsql { SELECT * FROM aux2 }
} {1 {SQL logic error or missing database}}
finish_test
|
| | | | | | | | | | | | | < |
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 ... 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 ... 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 ... 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 ... 515 516 517 518 519 520 521 |
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' }
................................................................................
# 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 {
................................................................................
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
}
#-------------------------------------------------------------------------
................................................................................
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
................................................................................
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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
...
206
207
208
209
210
211
212
213
|
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: (~0 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: (~0 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)}
}
................................................................................
7 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1, 5) FROM t3 WHERE t3 MATCH 'gestures'"
{{ZZZthe hand XXXgesturesYYY (called beatsZZZ}}
}
finish_test
|
|
|
<
|
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
...
206
207
208
209
210
211
212
|
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)}
}
................................................................................
7 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1, 5) FROM t3 WHERE t3 MATCH 'gestures'"
{{ZZZthe hand XXXgesturesYYY (called beatsZZZ}}
}
finish_test
|