Overview
| Comment: | Try to optimize LIKE and GLOB operators when RHS contains no wildcard. Ticket [e090183531fc274747] |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 613a87d62fd1f8526d53136efae5adae91824bcf |
| User & Date: | drh 2010-07-22 12:33:57 |
Context
|
2010-07-22
| ||
| 15:44 | Add test/threadtest3.c, containing multi-thread tests implemented in C. check-in: aad88cf5 user: dan tags: trunk | |
| 12:33 | Try to optimize LIKE and GLOB operators when RHS contains no wildcard. Ticket [e090183531fc274747] check-in: 613a87d6 user: drh tags: trunk | |
| 11:40 | Tweaks to the virtual table documentation contained in comments of sqlite3.h. No changes to actual code. check-in: f5866d47 user: drh tags: trunk | |
Changes
Changes to src/where.c.
685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 |
z = pRight->u.zToken;
}
if( z ){
cnt = 0;
while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
cnt++;
}
if( cnt!=0 && c!=0 && 255!=(u8)z[cnt-1] ){
Expr *pPrefix;
*pisComplete = z[cnt]==wc[0] && z[cnt+1]==0;
pPrefix = sqlite3Expr(db, TK_STRING, z);
if( pPrefix ) pPrefix->u.zToken[cnt] = 0;
*ppPrefix = pPrefix;
if( op==TK_VARIABLE ){
Vdbe *v = pParse->pVdbe;
sqlite3VdbeSetVarmask(v, pRight->iColumn);
if( *pisComplete && pRight->u.zToken[1] ){
|
| | |
685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 |
z = pRight->u.zToken;
}
if( z ){
cnt = 0;
while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
cnt++;
}
if( cnt!=0 && 255!=(u8)z[cnt-1] ){
Expr *pPrefix;
*pisComplete = c==wc[0] && z[cnt+1]==0;
pPrefix = sqlite3Expr(db, TK_STRING, z);
if( pPrefix ) pPrefix->u.zToken[cnt] = 0;
*ppPrefix = pPrefix;
if( op==TK_VARIABLE ){
Vdbe *v = pParse->pVdbe;
sqlite3VdbeSetVarmask(v, pRight->iColumn);
if( *pisComplete && pRight->u.zToken[1] ){
|
Changes to test/analyze3.test.
264 265 266 267 268 269 270 271 272 273 274 275 276 277 |
set like "a%"
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 100}
do_test analyze3-2.5 {
set like "%a"
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {999 999 100}
#-------------------------------------------------------------------------
# This block of tests checks that statements are correctly marked as
# expired when the values bound to any parameters that may affect the
# query plan are modified.
#
|
> > > > > > > > > > > > > > > > |
264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 |
set like "a%"
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 100}
do_test analyze3-2.5 {
set like "%a"
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {999 999 100}
do_test analyze3-2.6 {
set like "a"
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 0}
do_test analyze3-2.7 {
set like "ab"
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {11 0 0}
do_test analyze3-2.8 {
set like "abc"
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {2 0 1}
do_test analyze3-2.9 {
set like "a_c"
sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like }
} {101 0 10}
#-------------------------------------------------------------------------
# This block of tests checks that statements are correctly marked as
# expired when the values bound to any parameters that may affect the
# query plan are modified.
#
|
Changes to test/like.test.
189
190
191
192
193
194
195
196
197
198
199
200
201
202
...
304
305
306
307
308
309
310
311
312
313
314
315
316
317
|
queryplan {
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
}
} {abc abcd nosort {} i1}
do_test like-3.4 {
set sqlite_like_count
} 0
# Partial optimization when the pattern does not end in '%'
#
do_test like-3.5 {
set sqlite_like_count 0
queryplan {
SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;
................................................................................
PRAGMA case_sensitive_like=off;
SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
}
} {abd acd nosort {} i1}
do_test like-3.24 {
set sqlite_like_count
} 6
# No optimization if the LHS of the LIKE is not a column name or
# if the RHS is not a string.
#
do_test like-4.1 {
execsql {PRAGMA case_sensitive_like=on}
set sqlite_like_count 0
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
...
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
|
queryplan {
SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
}
} {abc abcd nosort {} i1}
do_test like-3.4 {
set sqlite_like_count
} 0
# The LIKE optimization still works when the RHS is a string with no
# wildcard. Ticket [e090183531fc2747]
#
do_test like-3.4.2 {
queryplan {
SELECT x FROM t1 WHERE x LIKE 'a' ORDER BY 1;
}
} {a nosort {} i1}
do_test like-3.4.3 {
queryplan {
SELECT x FROM t1 WHERE x LIKE 'ab' ORDER BY 1;
}
} {ab nosort {} i1}
do_test like-3.4.4 {
queryplan {
SELECT x FROM t1 WHERE x LIKE 'abcd' ORDER BY 1;
}
} {abcd nosort {} i1}
do_test like-3.4.5 {
queryplan {
SELECT x FROM t1 WHERE x LIKE 'abcde' ORDER BY 1;
}
} {nosort {} i1}
# Partial optimization when the pattern does not end in '%'
#
do_test like-3.5 {
set sqlite_like_count 0
queryplan {
SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1;
................................................................................
PRAGMA case_sensitive_like=off;
SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
}
} {abd acd nosort {} i1}
do_test like-3.24 {
set sqlite_like_count
} 6
# GLOB optimization when there is no wildcard. Ticket [e090183531fc2747]
#
do_test like-3.25 {
queryplan {
SELECT x FROM t1 WHERE x GLOB 'a' ORDER BY 1;
}
} {a nosort {} i1}
do_test like-3.26 {
queryplan {
SELECT x FROM t1 WHERE x GLOB 'abcd' ORDER BY 1;
}
} {abcd nosort {} i1}
do_test like-3.27 {
queryplan {
SELECT x FROM t1 WHERE x GLOB 'abcde' ORDER BY 1;
}
} {nosort {} i1}
# No optimization if the LHS of the LIKE is not a column name or
# if the RHS is not a string.
#
do_test like-4.1 {
execsql {PRAGMA case_sensitive_like=on}
set sqlite_like_count 0
|