/ Check-in [613a87d6]
Login
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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