Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Make a separate limb in the EXPLAIN QUERY PLAN output for the various lines associated with the OR-optimization. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: | 75ac7b4e4fd0811ca80c719badacff20 |
| User & Date: | drh 2018-05-04 00:39:43 |
Context
|
2018-05-04
| ||
| 04:49 | For the amalgamation-tarball, enable FTS5 and JSON1 by default and provide a new --enable-debug option that actives debugging facilities. check-in: 03edecaf user: drh tags: trunk | |
| 00:39 | Make a separate limb in the EXPLAIN QUERY PLAN output for the various lines associated with the OR-optimization. check-in: 75ac7b4e user: drh tags: trunk | |
|
2018-05-03
| ||
| 23:20 | In ORDER BY LIMIT queries, try to evaluate the ORDER BY terms first, and it it becomes clear that the row will not come in under the LIMIT, then skip evaluation of the other columns. check-in: c381f0ea user: drh tags: trunk | |
Changes
Changes to ext/expert/expert1.test.
238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 |
do_setup_rec_test $tn.12.1 {
CREATE TABLE t7(a, b);
} {
SELECT * FROM t7 WHERE a=? OR b=?
} {
CREATE INDEX t7_idx_00000062 ON t7(b);
CREATE INDEX t7_idx_00000061 ON t7(a);
SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?)
SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}
# rowid terms.
#
do_setup_rec_test $tn.13.1 {
CREATE TABLE t8(a, b);
} {
|
> | | |
238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 |
do_setup_rec_test $tn.12.1 {
CREATE TABLE t7(a, b);
} {
SELECT * FROM t7 WHERE a=? OR b=?
} {
CREATE INDEX t7_idx_00000062 ON t7(b);
CREATE INDEX t7_idx_00000061 ON t7(a);
MULTI-INDEX OR
SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?)
SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}
# rowid terms.
#
do_setup_rec_test $tn.13.1 {
CREATE TABLE t8(a, b);
} {
|
Changes to src/wherecode.c.
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
....
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
|
}
/* Run a separate WHERE clause for each term of the OR clause. After
** eliminating duplicates from other WHERE clauses, the action for each
** sub-WHERE clause is to to invoke the main loop body as a subroutine.
*/
wctrlFlags = WHERE_OR_SUBCLAUSE | (pWInfo->wctrlFlags & WHERE_SEEK_TABLE);
for(ii=0; ii<pOrWc->nTerm; ii++){
WhereTerm *pOrTerm = &pOrWc->a[ii];
if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
WhereInfo *pSubWInfo; /* Info for single OR-term scan */
Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
int jmp1 = 0; /* Address of jump operation */
assert( (pTabItem[0].fg.jointype & JT_LEFT)==0
................................................................................
}
/* Finish the loop through table entries that match term pOrTerm. */
sqlite3WhereEnd(pSubWInfo);
}
}
}
pLevel->u.pCovidx = pCov;
if( pCov ) pLevel->iIdxCur = iCovCur;
if( pAndExpr ){
pAndExpr->pLeft = 0;
sqlite3ExprDelete(db, pAndExpr);
}
sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
|
>
>
|
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
....
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
|
}
/* Run a separate WHERE clause for each term of the OR clause. After
** eliminating duplicates from other WHERE clauses, the action for each
** sub-WHERE clause is to to invoke the main loop body as a subroutine.
*/
wctrlFlags = WHERE_OR_SUBCLAUSE | (pWInfo->wctrlFlags & WHERE_SEEK_TABLE);
ExplainQueryPlan((pParse, 1, "MULTI-INDEX OR"));
for(ii=0; ii<pOrWc->nTerm; ii++){
WhereTerm *pOrTerm = &pOrWc->a[ii];
if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
WhereInfo *pSubWInfo; /* Info for single OR-term scan */
Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
int jmp1 = 0; /* Address of jump operation */
assert( (pTabItem[0].fg.jointype & JT_LEFT)==0
................................................................................
}
/* Finish the loop through table entries that match term pOrTerm. */
sqlite3WhereEnd(pSubWInfo);
}
}
}
ExplainQueryPlanPop(pParse);
pLevel->u.pCovidx = pCov;
if( pCov ) pLevel->iIdxCur = iCovCur;
if( pAndExpr ){
pAndExpr->pLeft = 0;
sqlite3ExprDelete(db, pAndExpr);
}
sqlite3VdbeChangeP1(v, iRetInit, sqlite3VdbeCurrentAddr(v));
|
Changes to test/bestindex3.test.
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
...
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
SELECT * FROM t1 WHERE a = 'abc';
} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
do_eqp_test 1.3 {
SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
} {
QUERY PLAN
|--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?
`--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
}
do_eqp_test 1.4 {
SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
} {
QUERY PLAN
|--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?
`--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
}
do_execsql_test 1.5 {
CREATE TABLE ttt(a, b, c);
INSERT INTO ttt VALUES(1, 'two', 'three');
INSERT INTO ttt VALUES(2, 'one', 'two');
................................................................................
CREATE INDEX t2y ON t2(y);
}
do_eqp_test 2.2 {
SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
} [string map {"\n " \n} {
QUERY PLAN
|--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)
`--SEARCH TABLE t2 USING INDEX t2y (y=?)
}]
}
#-------------------------------------------------------------------------
# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE
# statement is currently ignored.
#
|
>
|
|
>
|
|
>
|
|
|
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
...
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
SELECT * FROM t1 WHERE a = 'abc';
} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
do_eqp_test 1.3 {
SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
} {
QUERY PLAN
`--MULTI-INDEX OR
|--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?
`--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
}
do_eqp_test 1.4 {
SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
} {
QUERY PLAN
`--MULTI-INDEX OR
|--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?
`--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
}
do_execsql_test 1.5 {
CREATE TABLE ttt(a, b, c);
INSERT INTO ttt VALUES(1, 'two', 'three');
INSERT INTO ttt VALUES(2, 'one', 'two');
................................................................................
CREATE INDEX t2y ON t2(y);
}
do_eqp_test 2.2 {
SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
} [string map {"\n " \n} {
QUERY PLAN
`--MULTI-INDEX OR
|--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)
`--SEARCH TABLE t2 USING INDEX t2y (y=?)
}]
}
#-------------------------------------------------------------------------
# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE
# statement is currently ignored.
#
|
Changes to test/cost.test.
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 ... 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 ... 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 |
do_eqp_test 3.2 {
SELECT a FROM t5
WHERE b IS NULL OR c IS NULL OR d IS NULL
ORDER BY a;
} {
QUERY PLAN
|--SEARCH TABLE t5 USING INDEX t5b (b=?)
|--SEARCH TABLE t5 USING INDEX t5c (c=?)
|--SEARCH TABLE t5 USING INDEX t5d (d=?)
`--USE TEMP B-TREE FOR ORDER BY
}
#-------------------------------------------------------------------------
# If there is no likelihood() or stat3 data, SQLite assumes that a closed
# range scan (e.g. one constrained by "col BETWEEN ? AND ?" constraint)
# visits 1/64 of the rows in a table.
................................................................................
CREATE INDEX t3i2 ON t3(c);
}
do_eqp_test 6.2 {
SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
} {
QUERY PLAN
|--SEARCH TABLE t3 USING INDEX t3i1 (b>? AND b<?)
|--SEARCH TABLE t3 USING INDEX t3i2 (c=?)
`--USE TEMP B-TREE FOR ORDER BY
}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 7.1 {
................................................................................
do_eqp_test 7.2 {
SELECT a FROM t1
WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
ORDER BY a
} {
QUERY PLAN
|--SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)
|--SEARCH TABLE t1 USING INDEX t1b (b=?)
`--USE TEMP B-TREE FOR ORDER BY
}
do_eqp_test 7.3 {
SELECT rowid FROM t1
WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
OR (b NOT NULL AND c IS NULL AND d NOT NULL)
|
> | | | > | | > | | |
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 ... 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 ... 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
do_eqp_test 3.2 {
SELECT a FROM t5
WHERE b IS NULL OR c IS NULL OR d IS NULL
ORDER BY a;
} {
QUERY PLAN
|--MULTI-INDEX OR
| |--SEARCH TABLE t5 USING INDEX t5b (b=?)
| |--SEARCH TABLE t5 USING INDEX t5c (c=?)
| `--SEARCH TABLE t5 USING INDEX t5d (d=?)
`--USE TEMP B-TREE FOR ORDER BY
}
#-------------------------------------------------------------------------
# If there is no likelihood() or stat3 data, SQLite assumes that a closed
# range scan (e.g. one constrained by "col BETWEEN ? AND ?" constraint)
# visits 1/64 of the rows in a table.
................................................................................
CREATE INDEX t3i2 ON t3(c);
}
do_eqp_test 6.2 {
SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
} {
QUERY PLAN
|--MULTI-INDEX OR
| |--SEARCH TABLE t3 USING INDEX t3i1 (b>? AND b<?)
| `--SEARCH TABLE t3 USING INDEX t3i2 (c=?)
`--USE TEMP B-TREE FOR ORDER BY
}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 7.1 {
................................................................................
do_eqp_test 7.2 {
SELECT a FROM t1
WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
ORDER BY a
} {
QUERY PLAN
|--MULTI-INDEX OR
| |--SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)
| `--SEARCH TABLE t1 USING INDEX t1b (b=?)
`--USE TEMP B-TREE FOR ORDER BY
}
do_eqp_test 7.3 {
SELECT rowid FROM t1
WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
OR (b NOT NULL AND c IS NULL AND d NOT NULL)
|
Changes to test/eqp.test.
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
CREATE TABLE t3(a INT, b INT, ex TEXT);
}
do_eqp_test 1.2 {
SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
} {
QUERY PLAN
|--SEARCH TABLE t1 USING INDEX i1 (a=?)
|--SEARCH TABLE t1 USING INDEX i2 (b=?)
`--SCAN TABLE t2
}
do_eqp_test 1.3 {
SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
} {
QUERY PLAN
|--SCAN TABLE t2
|--SEARCH TABLE t1 USING INDEX i1 (a=?)
`--SEARCH TABLE t1 USING INDEX i2 (b=?)
}
do_eqp_test 1.3 {
SELECT a FROM t1 ORDER BY a
} {
QUERY PLAN
`--SCAN TABLE t1 USING COVERING INDEX i1
}
|
> | | > | | |
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
CREATE TABLE t3(a INT, b INT, ex TEXT);
}
do_eqp_test 1.2 {
SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
} {
QUERY PLAN
|--MULTI-INDEX OR
| |--SEARCH TABLE t1 USING INDEX i1 (a=?)
| `--SEARCH TABLE t1 USING INDEX i2 (b=?)
`--SCAN TABLE t2
}
do_eqp_test 1.3 {
SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
} {
QUERY PLAN
|--SCAN TABLE t2
`--MULTI-INDEX OR
|--SEARCH TABLE t1 USING INDEX i1 (a=?)
`--SEARCH TABLE t1 USING INDEX i2 (b=?)
}
do_eqp_test 1.3 {
SELECT a FROM t1 ORDER BY a
} {
QUERY PLAN
`--SCAN TABLE t1 USING COVERING INDEX i1
}
|
Changes to test/join5.test.
262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 |
do_eqp_test 7.2 {
SELECT * FROM t1 LEFT JOIN t2 ON (
t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))
);
} {
QUERY PLAN
|--SCAN TABLE t1
|--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
`--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
}
do_execsql_test 7.3 {
CREATE TABLE t3(x);
CREATE TABLE t4(x, y, z);
CREATE INDEX t4xy ON t4(x, y);
|
> | | |
262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 |
do_eqp_test 7.2 {
SELECT * FROM t1 LEFT JOIN t2 ON (
t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))
);
} {
QUERY PLAN
|--SCAN TABLE t1
`--MULTI-INDEX OR
|--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
`--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
}
do_execsql_test 7.3 {
CREATE TABLE t3(x);
CREATE TABLE t4(x, y, z);
CREATE INDEX t4xy ON t4(x, y);
|
Changes to test/where7.test.
23348 23349 23350 23351 23352 23353 23354 23355 23356 23357 23358 23359 23360 23361 |
WHERE t302.c2 = 19571
AND t302.c3 > 1287603136
AND (t301.c4 = 1407449685622784
OR t301.c8 = 1407424651264000)
ORDER BY t302.c5 LIMIT 200;
} {
QUERY PLAN
|--SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)
|--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)
`--USE TEMP B-TREE FOR ORDER BY
}
finish_test
|
> | | |
23348 23349 23350 23351 23352 23353 23354 23355 23356 23357 23358 23359 23360 23361 23362 |
WHERE t302.c2 = 19571
AND t302.c3 > 1287603136
AND (t301.c4 = 1407449685622784
OR t301.c8 = 1407424651264000)
ORDER BY t302.c5 LIMIT 200;
} {
QUERY PLAN
|--MULTI-INDEX OR
| |--SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)
| `--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)
`--USE TEMP B-TREE FOR ORDER BY
}
finish_test
|
Changes to test/where9.test.
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
...
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
|
ifcapable explain {
do_eqp_test where9-3.1 {
SELECT t2.a FROM t1, t2
WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
} [string map {"\n " \n} {
QUERY PLAN
|--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH TABLE t2 USING INDEX t2d (d=?)
`--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
}]
do_eqp_test where9-3.2 {
SELECT coalesce(t2.a,9999)
FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
WHERE t1.a=80
} [string map {"\n " \n} {
QUERY PLAN
|--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH TABLE t2 USING INDEX t2d (d=?)
`--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
}]
}
# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
................................................................................
# The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
# the former is an equality test which is expected to return fewer rows.
#
do_eqp_test where9-5.1 {
SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
} {
QUERY PLAN
|--SEARCH TABLE t1 USING INDEX t1c (c=?)
`--SEARCH TABLE t1 USING INDEX t1d (d=?)
}
# In contrast, b=1000 is preferred over any OR-clause.
#
do_eqp_test where9-5.2 {
SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
} {SEARCH TABLE t1 USING INDEX t1b (b=?)}
|
>
|
|
>
|
|
>
|
|
|
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
...
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
|
ifcapable explain {
do_eqp_test where9-3.1 {
SELECT t2.a FROM t1, t2
WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
} [string map {"\n " \n} {
QUERY PLAN
|--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
`--MULTI-INDEX OR
|--SEARCH TABLE t2 USING INDEX t2d (d=?)
`--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
}]
do_eqp_test where9-3.2 {
SELECT coalesce(t2.a,9999)
FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
WHERE t1.a=80
} [string map {"\n " \n} {
QUERY PLAN
|--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
`--MULTI-INDEX OR
|--SEARCH TABLE t2 USING INDEX t2d (d=?)
`--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
}]
}
# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
................................................................................
# The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
# the former is an equality test which is expected to return fewer rows.
#
do_eqp_test where9-5.1 {
SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
} {
QUERY PLAN
`--MULTI-INDEX OR
|--SEARCH TABLE t1 USING INDEX t1c (c=?)
`--SEARCH TABLE t1 USING INDEX t1d (d=?)
}
# In contrast, b=1000 is preferred over any OR-clause.
#
do_eqp_test where9-5.2 {
SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
} {SEARCH TABLE t1 USING INDEX t1b (b=?)}
|
Changes to test/whereI.test.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
..
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
CREATE INDEX i2 ON t1(c);
}
do_eqp_test 1.1 {
SELECT a FROM t1 WHERE b='b' OR c='x'
} {
QUERY PLAN
|--SEARCH TABLE t1 USING INDEX i1 (b=?)
`--SEARCH TABLE t1 USING INDEX i2 (c=?)
}
do_execsql_test 1.2 {
SELECT a FROM t1 WHERE b='b' OR c='x'
} {2 3}
do_execsql_test 1.3 {
................................................................................
CREATE INDEX i4 ON t2(c);
}
do_eqp_test 2.1 {
SELECT a FROM t2 WHERE b='b' OR c='x'
} {
QUERY PLAN
|--SEARCH TABLE t2 USING INDEX i3 (b=?)
`--SEARCH TABLE t2 USING INDEX i4 (c=?)
}
do_execsql_test 2.2 {
SELECT a FROM t2 WHERE b='b' OR c='x'
} {ii iii}
do_execsql_test 2.3 {
|
>
|
|
>
|
|
|
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
CREATE INDEX i2 ON t1(c);
}
do_eqp_test 1.1 {
SELECT a FROM t1 WHERE b='b' OR c='x'
} {
QUERY PLAN
`--MULTI-INDEX OR
|--SEARCH TABLE t1 USING INDEX i1 (b=?)
`--SEARCH TABLE t1 USING INDEX i2 (c=?)
}
do_execsql_test 1.2 {
SELECT a FROM t1 WHERE b='b' OR c='x'
} {2 3}
do_execsql_test 1.3 {
................................................................................
CREATE INDEX i4 ON t2(c);
}
do_eqp_test 2.1 {
SELECT a FROM t2 WHERE b='b' OR c='x'
} {
QUERY PLAN
`--MULTI-INDEX OR
|--SEARCH TABLE t2 USING INDEX i3 (b=?)
`--SEARCH TABLE t2 USING INDEX i4 (c=?)
}
do_execsql_test 2.2 {
SELECT a FROM t2 WHERE b='b' OR c='x'
} {ii iii}
do_execsql_test 2.3 {
|