Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Test cases added. Comments fixed. Proposed solution for ticket [05f43be8fdda9fbd9]. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | like-opt-fix |
Files: | files | file ages | folders |
SHA1: |
6b993bd54035b67f4d84941e3f444ca7 |
User & Date: | drh 2015-03-06 20:49:52.770 |
Context
2015-03-07
| ||
00:57 | Refactor some jump opcodes in the VDBE. Add JumpZeroIncr and DecrJumpZero. Fix the LIKE optimization to work with DESC sort order. (check-in: 26cb5145bf user: drh tags: like-opt-fix) | |
2015-03-06
| ||
20:49 | Test cases added. Comments fixed. Proposed solution for ticket [05f43be8fdda9fbd9]. (check-in: 6b993bd540 user: drh tags: like-opt-fix) | |
19:47 | Fix the LIKE optimization even when comparing mixed-case BLOBs. (check-in: a58aafdb4e user: drh tags: like-opt-fix) | |
Changes
Changes to src/vdbe.c.
︙ | ︙ | |||
1049 1050 1051 1052 1053 1054 1055 | /* Opcode: String P1 P2 P3 P4 P5 ** Synopsis: r[P2]='P4' (len=P1) ** ** The string value P4 of length P1 (bytes) is stored in register P2. ** ** If P5!=0 and the content of register P3 is greater than zero, then | | | | | 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 | /* Opcode: String P1 P2 P3 P4 P5 ** Synopsis: r[P2]='P4' (len=P1) ** ** The string value P4 of length P1 (bytes) is stored in register P2. ** ** If P5!=0 and the content of register P3 is greater than zero, then ** the datatype of the register P2 is converted to BLOB. The content is ** the same sequence of bytes, it is merely interpreted as a BLOB instead ** of a string, as if it had been CAST. */ case OP_String: { /* out2-prerelease */ assert( pOp->p4.z!=0 ); pOut->flags = MEM_Str|MEM_Static|MEM_Term; pOut->z = pOp->p4.z; pOut->n = pOp->p1; pOut->enc = encoding; |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
1105 1106 1107 1108 1109 1110 1111 | WhereMaskSet *pMaskSet; /* Set of table index masks */ Expr *pExpr; /* The expression to be analyzed */ Bitmask prereqLeft; /* Prerequesites of the pExpr->pLeft */ Bitmask prereqAll; /* Prerequesites of pExpr */ Bitmask extraRight = 0; /* Extra dependencies on LEFT JOIN */ Expr *pStr1 = 0; /* RHS of LIKE/GLOB operator */ int isComplete = 0; /* RHS of LIKE/GLOB ends with wildcard */ | | | 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 | WhereMaskSet *pMaskSet; /* Set of table index masks */ Expr *pExpr; /* The expression to be analyzed */ Bitmask prereqLeft; /* Prerequesites of the pExpr->pLeft */ Bitmask prereqAll; /* Prerequesites of pExpr */ Bitmask extraRight = 0; /* Extra dependencies on LEFT JOIN */ Expr *pStr1 = 0; /* RHS of LIKE/GLOB operator */ int isComplete = 0; /* RHS of LIKE/GLOB ends with wildcard */ int noCase = 0; /* uppercase equivalent to lowercase */ int op; /* Top-level operator. pExpr->op */ Parse *pParse = pWInfo->pParse; /* Parsing context */ sqlite3 *db = pParse->db; /* Database connection */ if( db->mallocFailed ){ return; } |
︙ | ︙ | |||
1243 1244 1245 1246 1247 1248 1249 | } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION /* Add constraints to reduce the search space on a LIKE or GLOB ** operator. ** | | | | > > > < > | 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 | } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION /* Add constraints to reduce the search space on a LIKE or GLOB ** operator. ** ** A like pattern of the form "x LIKE 'aBc%'" is changed into constraints ** ** x>='ABC' AND x<'abd' AND x LIKE 'aBc%' ** ** The last character of the prefix "abc" is incremented to form the ** termination condition "abd". If case is not significant (the default ** for LIKE) then the lower-bound is made all uppercase and the upper- ** bound is made all lowercase so that the bounds also work when comparing ** BLOBs. */ if( pWC->op==TK_AND && isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase) ){ Expr *pLeft; /* LHS of LIKE/GLOB operator */ Expr *pStr2; /* Copy of pStr1 - RHS of LIKE/GLOB operator */ Expr *pNewExpr1; Expr *pNewExpr2; int idxNew1; int idxNew2; Token sCollSeqName; /* Name of collating sequence */ const u16 wtFlags = TERM_LIKEOPT | TERM_VIRTUAL | TERM_DYNAMIC; pLeft = pExpr->x.pList->a[1].pExpr; pStr2 = sqlite3ExprDup(db, pStr1, 0); /* Convert the lower bound to upper-case and the upper bound to ** lower-case (upper-case is less than lower-case in ASCII) so that ** the range constraints also work for BLOBs */ if( noCase && !pParse->db->mallocFailed ){ int i; char c; pTerm->wtFlags |= TERM_LIKE; for(i=0; (c = pStr1->u.zToken[i])!=0; i++){ pStr1->u.zToken[i] = sqlite3Toupper(c); pStr2->u.zToken[i] = sqlite3Tolower(c); } } if( !db->mallocFailed ){ |
︙ | ︙ | |||
2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 | ** \___________/ \______/ \_____/ ** parent child1 child2 ** ** Only the parent term was in the original WHERE clause. The child1 ** and child2 terms were added by the LIKE optimization. If both of ** the virtual child terms are valid, then testing of the parent can be ** skipped. */ static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){ int nLoop = 0; while( pTerm && (pTerm->wtFlags & TERM_CODED)==0 && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin)) && (pLevel->notReady & pTerm->prereqAll)==0 | > > > > > > | 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 | ** \___________/ \______/ \_____/ ** parent child1 child2 ** ** Only the parent term was in the original WHERE clause. The child1 ** and child2 terms were added by the LIKE optimization. If both of ** the virtual child terms are valid, then testing of the parent can be ** skipped. ** ** Usually the parent term is marked as TERM_CODED. But if the parent ** term was originally TERM_LIKE, then the parent gets TERM_LIKECOND instead. ** The TERM_LIKECOND marking indicates that the term should be coded inside ** a conditional such that is only evaluated on the second pass of a ** LIKE-optimization loop, when scanning BLOBs instead of strings. */ static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){ int nLoop = 0; while( pTerm && (pTerm->wtFlags & TERM_CODED)==0 && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_FromJoin)) && (pLevel->notReady & pTerm->prereqAll)==0 |
︙ | ︙ |
Changes to test/analyze3.test.
︙ | ︙ | |||
277 278 279 280 281 282 283 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}} do_eqp_test analyze3-2.3 { SELECT count(a) FROM t1 WHERE b LIKE '%a' } {0 0 0 {SCAN TABLE t1}} do_test analyze3-2.4 { sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' } | | | | | | | | | | | | | | 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 | } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?)}} do_eqp_test analyze3-2.3 { SELECT count(a) FROM t1 WHERE b LIKE '%a' } {0 0 0 {SCAN TABLE t1}} do_test analyze3-2.4 { sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' } } {102 0 100} do_test analyze3-2.5 { sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' } } {999 999 100} do_test analyze3-2.6 { set like "a%" sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } } {102 0 100} do_test analyze3-2.7 { set like "%a" sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } } {999 999 100} do_test analyze3-2.8 { set like "a" sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } } {102 0 0} do_test analyze3-2.9 { set like "ab" sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } } {12 0 0} do_test analyze3-2.10 { set like "abc" sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } } {3 0 1} do_test analyze3-2.11 { set like "a_c" sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE $like } } {102 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.
︙ | ︙ | |||
745 746 747 748 749 750 751 | SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.5 { count { SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a; } | | | 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 | SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.5 { count { SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY +a; } } {12 123 scan 4 like 0} do_test like-10.6 { count { SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.10 { execsql { |
︙ | ︙ | |||
786 787 788 789 790 791 792 | SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.14 { count { SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a; } | | | 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 | SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} do_test like-10.14 { count { SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY +a; } } {12 123 scan 4 like 0} do_test like-10.15 { count { SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY +a; } } {12 123 scan 5 like 6} } |
︙ | ︙ |
Added test/like3.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | # 2015-03-06 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This file implements regression tests for SQLite library. The # focus of this file is testing the LIKE and GLOB operators and # in particular the optimizations that occur to help those operators # run faster and that those optimizations work correctly when there # are both strings and blobs being tested. # # Ticket 05f43be8fdda9fbd948d374319b99b054140bc36 shows that the following # SQL was not working correctly: # # CREATE TABLE t1(x TEXT UNIQUE COLLATE nocase); # INSERT INTO t1(x) VALUES(x'616263'); # SELECT 'query-1', x FROM t1 WHERE x LIKE 'a%'; # SELECT 'query-2', x FROM t1 WHERE +x LIKE 'a%'; # # This script verifies that it works right now. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test like3-1.1 { PRAGMA encoding=UTF8; CREATE TABLE t1(a,b TEXT COLLATE nocase); INSERT INTO t1(a,b) VALUES(1,'abc'), (2,'ABX'), (3,'BCD'), (4,x'616263'), (5,x'414258'), (6,x'424344'); CREATE INDEX t1ba ON t1(b,a); SELECT a, b FROM t1 WHERE b LIKE 'aB%' ORDER BY +a; } {1 abc 2 ABX 4 abc 5 ABX} do_execsql_test like3-1.2 { SELECT a, b FROM t1 WHERE +b LIKE 'aB%' ORDER BY +a; } {1 abc 2 ABX 4 abc 5 ABX} do_execsql_test like3-1.3 { CREATE TABLE t2(a, b TEXT); INSERT INTO t2 SELECT a, b FROM t1; CREATE INDEX t2ba ON t2(b,a); SELECT a, b FROM t2 WHERE b GLOB 'ab*' ORDER BY +a; } {1 abc 4 abc} do_execsql_test like3-1.4 { SELECT a, b FROM t2 WHERE +b GLOB 'ab*' ORDER BY +a; } {1 abc 4 abc} finish_test |
Changes to test/where8.test.
︙ | ︙ | |||
62 63 64 65 66 67 68 | do_test where8-1.3 { execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' } } {IX X II 0 0 6} do_test where8-1.4 { execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' } | | | | 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | do_test where8-1.3 { execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' } } {IX X II 0 0 6} do_test where8-1.4 { execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' } } {IX X III II 0 0 10} do_test where8-1.5 { execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' } } {IX X V IV 0 0 10} do_test where8-1.6 { execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid } } {I III 0 1} do_test where8-1.7 { execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a } |
︙ | ︙ |