Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a bug in the LIKE query optimization. (Found by coverage testing.) (CVS 6137) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
fe90e9116b6e1e25cf3119d2777a8e9c |
User & Date: | drh 2009-01-07 18:24:03.000 |
Context
2009-01-07
| ||
20:58 | Add new test cases to increase coverage of where.c. (CVS 6138) (check-in: 2e1ab51f05 user: drh tags: trunk) | |
18:24 | Fix a bug in the LIKE query optimization. (Found by coverage testing.) (CVS 6137) (check-in: fe90e9116b user: drh tags: trunk) | |
18:08 | Make the same change as (6121) (accidentally reverted). Also enhance test_journal.c to catch this kind of bug. (CVS 6136) (check-in: ccc9c211a2 user: danielk1977 tags: trunk) | |
Changes
Changes to src/test1.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing all sorts of SQLite interfaces. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing all sorts of SQLite interfaces. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** ** $Id: test1.c,v 1.339 2009/01/07 18:24:03 drh Exp $ */ #include "sqliteInt.h" #include "tcl.h" #include <stdlib.h> #include <string.h> /* |
︙ | ︙ | |||
321 322 323 324 325 326 327 328 329 330 331 332 333 334 | } if( getDbPointer(interp, argv[1], &db) ) return TCL_ERROR; Tcl_DStringInit(&str); zSql = sqlite3_mprintf(argv[2], argv[3]); rc = sqlite3_exec(db, zSql, exec_printf_cb, &str, &zErr); sqlite3_free(zSql); sprintf(zBuf, "%d", rc); Tcl_AppendElement(interp, zBuf); Tcl_AppendElement(interp, rc==SQLITE_OK ? Tcl_DStringValue(&str) : zErr); Tcl_DStringFree(&str); if( zErr ) sqlite3_free(zErr); if( sqlite3TestErrCode(interp, db, rc) ) return TCL_ERROR; return TCL_OK; } | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 321 322 323 324 325 326 327 328 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 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 | } if( getDbPointer(interp, argv[1], &db) ) return TCL_ERROR; Tcl_DStringInit(&str); zSql = sqlite3_mprintf(argv[2], argv[3]); rc = sqlite3_exec(db, zSql, exec_printf_cb, &str, &zErr); sqlite3_free(zSql); sprintf(zBuf, "%d", rc); Tcl_AppendElement(interp, zBuf); Tcl_AppendElement(interp, rc==SQLITE_OK ? Tcl_DStringValue(&str) : zErr); Tcl_DStringFree(&str); if( zErr ) sqlite3_free(zErr); if( sqlite3TestErrCode(interp, db, rc) ) return TCL_ERROR; return TCL_OK; } /* ** Usage: sqlite3_exec_hex DB HEX ** ** Invoke the sqlite3_exec() on a string that is obtained by translating ** HEX into ASCII. Most characters are translated as is. %HH becomes ** a hex character. */ static int test_exec_hex( void *NotUsed, Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ int argc, /* Number of arguments */ char **argv /* Text of each argument */ ){ sqlite3 *db; Tcl_DString str; int rc, i, j; char *zErr = 0; char *zHex; char zSql[500]; char zBuf[30]; if( argc!=3 ){ Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0], " DB HEX", 0); return TCL_ERROR; } if( getDbPointer(interp, argv[1], &db) ) return TCL_ERROR; zHex = argv[2]; for(i=j=0; i<sizeof(zSql) && zHex[j]; i++, j++){ if( zHex[j]=='%' && zHex[j+2] && zHex[j+2] ){ zSql[i] = (testHexToInt(zHex[j+1])<<4) + testHexToInt(zHex[j+2]); j += 2; }else{ zSql[i] = zHex[j]; } } zSql[i] = 0; Tcl_DStringInit(&str); rc = sqlite3_exec(db, zSql, exec_printf_cb, &str, &zErr); sprintf(zBuf, "%d", rc); Tcl_AppendElement(interp, zBuf); Tcl_AppendElement(interp, rc==SQLITE_OK ? Tcl_DStringValue(&str) : zErr); Tcl_DStringFree(&str); if( zErr ) sqlite3_free(zErr); if( sqlite3TestErrCode(interp, db, rc) ) return TCL_ERROR; return TCL_OK; } |
︙ | ︙ | |||
4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 | { "sqlite3_mprintf_scaled", (Tcl_CmdProc*)sqlite3_mprintf_scaled }, { "sqlite3_mprintf_hexdouble", (Tcl_CmdProc*)sqlite3_mprintf_hexdouble}, { "sqlite3_mprintf_z_test", (Tcl_CmdProc*)test_mprintf_z }, { "sqlite3_mprintf_n_test", (Tcl_CmdProc*)test_mprintf_n }, { "sqlite3_snprintf_int", (Tcl_CmdProc*)test_snprintf_int }, { "sqlite3_last_insert_rowid", (Tcl_CmdProc*)test_last_rowid }, { "sqlite3_exec_printf", (Tcl_CmdProc*)test_exec_printf }, { "sqlite3_exec", (Tcl_CmdProc*)test_exec }, { "sqlite3_exec_nr", (Tcl_CmdProc*)test_exec_nr }, #ifndef SQLITE_OMIT_GET_TABLE { "sqlite3_get_table_printf", (Tcl_CmdProc*)test_get_table_printf }, #endif { "sqlite3_close", (Tcl_CmdProc*)sqlite_test_close }, { "sqlite3_create_function", (Tcl_CmdProc*)test_create_function }, | > | 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 4815 | { "sqlite3_mprintf_scaled", (Tcl_CmdProc*)sqlite3_mprintf_scaled }, { "sqlite3_mprintf_hexdouble", (Tcl_CmdProc*)sqlite3_mprintf_hexdouble}, { "sqlite3_mprintf_z_test", (Tcl_CmdProc*)test_mprintf_z }, { "sqlite3_mprintf_n_test", (Tcl_CmdProc*)test_mprintf_n }, { "sqlite3_snprintf_int", (Tcl_CmdProc*)test_snprintf_int }, { "sqlite3_last_insert_rowid", (Tcl_CmdProc*)test_last_rowid }, { "sqlite3_exec_printf", (Tcl_CmdProc*)test_exec_printf }, { "sqlite3_exec_hex", (Tcl_CmdProc*)test_exec_hex }, { "sqlite3_exec", (Tcl_CmdProc*)test_exec }, { "sqlite3_exec_nr", (Tcl_CmdProc*)test_exec_nr }, #ifndef SQLITE_OMIT_GET_TABLE { "sqlite3_get_table_printf", (Tcl_CmdProc*)test_get_table_printf }, #endif { "sqlite3_close", (Tcl_CmdProc*)sqlite_test_close }, { "sqlite3_create_function", (Tcl_CmdProc*)test_create_function }, |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is responsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is responsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.358 2009/01/07 18:24:03 drh Exp $ */ #include "sqliteInt.h" /* ** Trace output macros */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) |
︙ | ︙ | |||
260 261 262 263 264 265 266 | /* Forward reference */ static void whereClauseClear(WhereClause*); /* ** Deallocate all memory associated with a WhereOrInfo object. */ static void whereOrInfoDelete(sqlite3 *db, WhereOrInfo *p){ | < | | < < | | < | 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 | /* Forward reference */ static void whereClauseClear(WhereClause*); /* ** Deallocate all memory associated with a WhereOrInfo object. */ static void whereOrInfoDelete(sqlite3 *db, WhereOrInfo *p){ whereClauseClear(&p->wc); sqlite3DbFree(db, p); } /* ** Deallocate all memory associated with a WhereAndInfo object. */ static void whereAndInfoDelete(sqlite3 *db, WhereAndInfo *p){ whereClauseClear(&p->wc); sqlite3DbFree(db, p); } /* ** Deallocate a WhereClause structure. The WhereClause structure ** itself is not freed. This routine is the inverse of whereClauseInit(). */ static void whereClauseClear(WhereClause *pWC){ |
︙ | ︙ | |||
619 620 621 622 623 624 625 | static int isLikeOrGlob( Parse *pParse, /* Parsing and code generating context */ Expr *pExpr, /* Test this expression */ int *pnPattern, /* Number of non-wildcard prefix characters */ int *pisComplete, /* True if the only wildcard is % in the last character */ int *pnoCase /* True if uppercase is equivalent to lowercase */ ){ | | | | | > | | | | < | 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 | static int isLikeOrGlob( Parse *pParse, /* Parsing and code generating context */ Expr *pExpr, /* Test this expression */ int *pnPattern, /* Number of non-wildcard prefix characters */ int *pisComplete, /* True if the only wildcard is % in the last character */ int *pnoCase /* True if uppercase is equivalent to lowercase */ ){ const char *z; /* String on RHS of LIKE operator */ Expr *pRight, *pLeft; /* Right and left size of LIKE operator */ ExprList *pList; /* List of operands to the LIKE operator */ int c; /* One character in z[] */ int cnt; /* Number of non-wildcard prefix characters */ char wc[3]; /* Wildcard characters */ CollSeq *pColl; /* Collating sequence for LHS */ sqlite3 *db = pParse->db; /* Database connection */ if( !sqlite3IsLikeFunction(db, pExpr, pnoCase, wc) ){ return 0; } #ifdef SQLITE_EBCDIC if( *pnoCase ) return 0; #endif pList = pExpr->pList; pRight = pList->a[0].pExpr; if( pRight->op!=TK_STRING ){ return 0; } pLeft = pList->a[1].pExpr; if( pLeft->op!=TK_COLUMN ){ return 0; } pColl = sqlite3ExprCollSeq(pParse, pLeft); |
︙ | ︙ | |||
659 660 661 662 663 664 665 | } sqlite3DequoteExpr(db, pRight); z = (char *)pRight->token.z; cnt = 0; if( z ){ while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){ cnt++; } } | | | 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 | } sqlite3DequoteExpr(db, pRight); z = (char *)pRight->token.z; cnt = 0; if( z ){ while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){ cnt++; } } if( cnt==0 || 255==(u8)z[cnt-1] ){ return 0; } *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0; *pnPattern = cnt; return 1; } #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ |
︙ | ︙ |
Changes to test/like.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # 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. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # 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. # # $Id: like.test,v 1.11 2009/01/07 18:24:03 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create some sample data to work with. # do_test like-1.0 { |
︙ | ︙ | |||
546 547 548 549 550 551 552 553 554 | db function like -argcount 3 newlike db eval { SELECT 1, x FROM t8 WHERE x LIKE '%h%'; SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; } } {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 | db function like -argcount 3 newlike db eval { SELECT 1, x FROM t8 WHERE x LIKE '%h%'; SELECT 2, x FROM t8 WHERE x LIKE '%h%' ESCAPE 'x'; } } {1 abcdef 1 ghijkl 1 mnopqr 2 abcdef 2 ghijkl 2 mnopqr} ifcapable like_opt { # Evaluate SQL. Return the result set followed by the # and the number of full-scan steps. # db close sqlite3 db test.db proc count_steps {sql} { set r [db eval $sql] lappend r scan [db status step] sort [db status sort] } do_test like-9.1 { count_steps { SELECT x FROM t2 WHERE x LIKE 'x%' } } {xyz scan 0 sort 0} do_test like-9.2 { count_steps { SELECT x FROM t2 WHERE x LIKE '_y%' } } {xyz scan 19 sort 0} do_test like-9.3.1 { set res [sqlite3_exec_hex db { SELECT x FROM t2 WHERE x LIKE '%78%25' }] } {0 {x xyz}} ifcapable explain { do_test like-9.3.2 { set res [sqlite3_exec_hex db { EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%78%25' }] regexp {INDEX i2} $res } {1} } do_test like-9.4.1 { sqlite3_exec_hex db {INSERT INTO t2 VALUES('%ffhello')} set res [sqlite3_exec_hex db { SELECT substr(x,2) AS x FROM t2 WHERE +x LIKE '%ff%25' }] } {0 {x hello}} do_test like-9.4.2 { set res [sqlite3_exec_hex db { SELECT substr(x,2) AS x FROM t2 WHERE x LIKE '%ff%25' }] } {0 {x hello}} ifcapable explain { do_test like-9.4.3 { set res [sqlite3_exec_hex db { EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25' }] regexp {INDEX i2} $res } {0} } do_test like-9.5.1 { set res [sqlite3_exec_hex db { SELECT x FROM t2 WHERE x LIKE '%fe%25' }] } {0 {}} ifcapable explain { do_test like-9.5.2 { set res [sqlite3_exec_hex db { EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25' }] regexp {INDEX i2} $res } {1} } } finish_test |