Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Disable the LIKE optimization if the affinity of the LHS column is not TEXT. Ticket #3901. (CVS 6727) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a255c645c46ae03b65f862858fe57e46 |
User & Date: | drh 2009-06-07 23:45:11.000 |
Context
2009-06-08
| ||
12:52 | Increase the version number to 3.6.15 in preparation for the next release. (CVS 6728) (check-in: 456ea541d6 user: drh tags: trunk) | |
2009-06-07
| ||
23:45 | Disable the LIKE optimization if the affinity of the LHS column is not TEXT. Ticket #3901. (CVS 6727) (check-in: a255c645c4 user: drh tags: trunk) | |
2009-06-06
| ||
19:21 | Update the error message on one of the corruption tests to account for the fact that we are finding the corruption sooner. (CVS 6726) (check-in: ba9848e7b8 user: drh tags: trunk) | |
Changes
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.402 2009/06/07 23:45:11 drh Exp $ */ #include "sqliteInt.h" /* ** Trace output macros */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) |
︙ | ︙ | |||
650 651 652 653 654 655 656 | } pLeft = pList->a[1].pExpr; if( pLeft->op!=TK_COLUMN ){ return 0; } pColl = sqlite3ExprCollSeq(pParse, pLeft); assert( pColl!=0 || pLeft->iColumn==-1 ); | | < < < > | | 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 | } pLeft = pList->a[1].pExpr; if( pLeft->op!=TK_COLUMN ){ return 0; } pColl = sqlite3ExprCollSeq(pParse, pLeft); assert( pColl!=0 || pLeft->iColumn==-1 ); if( pColl==0 ) return 0; if( (pColl->type!=SQLITE_COLL_BINARY || *pnoCase) && (pColl->type!=SQLITE_COLL_NOCASE || !*pnoCase) ){ return 0; } if( sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT ) return 0; z = pRight->u.zToken; cnt = 0; if( ALWAYS(z) ){ 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] ){ return 0; } |
︙ | ︙ |
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.13 2009/06/07 23:45:11 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create some sample data to work with. # do_test like-1.0 { |
︙ | ︙ | |||
353 354 355 356 357 358 359 | } } {ABC {ABC abc xyz} abc abcd nosort {} i1} do_test like-5.2 { set sqlite_like_count } 12 do_test like-5.3 { execsql { | | | 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 | } } {ABC {ABC abc xyz} abc abcd nosort {} i1} do_test like-5.2 { set sqlite_like_count } 12 do_test like-5.3 { execsql { CREATE TABLE t2(x TEXT COLLATE NOCASE); INSERT INTO t2 SELECT * FROM t1; CREATE INDEX i2 ON t2(x COLLATE NOCASE); } set sqlite_like_count 0 queryplan { SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1 } |
︙ | ︙ | |||
472 473 474 475 476 477 478 | queryplan { SELECT x FROM t2 WHERE x LIKE 'ZZ%'; } } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} do_test like-5.25 { queryplan { PRAGMA case_sensitive_like=on; | | | 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 | queryplan { SELECT x FROM t2 WHERE x LIKE 'ZZ%'; } } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2} do_test like-5.25 { queryplan { PRAGMA case_sensitive_like=on; CREATE TABLE t3(x TEXT); CREATE INDEX i3 ON t3(x); INSERT INTO t3 VALUES('ZZ-upper-upper'); INSERT INTO t3 VALUES('zZ-lower-upper'); INSERT INTO t3 VALUES('Zz-upper-lower'); INSERT INTO t3 VALUES('zz-lower-lower'); SELECT x FROM t3 WHERE x LIKE 'zz%'; } |
︙ | ︙ | |||
515 516 517 518 519 520 521 | execsql { SELECT * FROM t2 WHERE x LIKE '''a%' } } {'abc 'ax} do_test like-7.1 { execsql { | | | | 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 | execsql { SELECT * FROM t2 WHERE x LIKE '''a%' } } {'abc 'ax} do_test like-7.1 { execsql { SELECT rowid, * FROM t1 WHERE rowid GLOB '1*' ORDER BY rowid; } } {1 a 10 ABC 11 CDE 12 {ABC abc xyz}} # ticket #3345. # # Overloading the LIKE function with -1 for the number of arguments # will overload both the 2-argument and the 3-argument LIKE. # do_test like-8.1 { |
︙ | ︙ | |||
625 626 627 628 629 630 631 632 | set res [sqlite3_exec_hex db { EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25' }] regexp {INDEX i2} $res } {1} } } | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 | set res [sqlite3_exec_hex db { EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25' }] regexp {INDEX i2} $res } {1} } } # Do an SQL statement. Append the search count to the end of the result. # proc count sql { set ::sqlite_search_count 0 set ::sqlite_like_count 0 return [concat [execsql $sql] scan $::sqlite_search_count \ like $::sqlite_like_count] } # The LIKE and GLOB optimizations do not work on columns with # affinity other than TEXT. # Ticket #3901 # do_test like-10.1 { db close sqlite3 db test.db execsql { CREATE TABLE t10( a INTEGER PRIMARY KEY, b INTEGER COLLATE nocase UNIQUE, c NUMBER COLLATE nocase UNIQUE, d BLOB COLLATE nocase UNIQUE, e COLLATE nocase UNIQUE, f TEXT COLLATE nocase UNIQUE ); INSERT INTO t10 VALUES(1,1,1,1,1,1); INSERT INTO t10 VALUES(12,12,12,12,12,12); INSERT INTO t10 VALUES(123,123,123,123,123,123); INSERT INTO t10 VALUES(234,234,234,234,234,234); INSERT INTO t10 VALUES(345,345,345,345,345,345); INSERT INTO t10 VALUES(45,45,45,45,45,45); } count { SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY a; } } {12 123 scan 5 like 6} do_test like-10.2 { count { SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY a; } } {12 123 scan 5 like 6} do_test like-10.3 { count { SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY a; } } {12 123 scan 5 like 6} do_test like-10.4 { count { 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 3 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 { CREATE TABLE t10b( a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c NUMBER UNIQUE, d BLOB UNIQUE, e UNIQUE, f TEXT UNIQUE ); INSERT INTO t10b SELECT * FROM t10; } count { SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY a; } } {12 123 scan 5 like 6} do_test like-10.11 { count { SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY a; } } {12 123 scan 5 like 6} do_test like-10.12 { count { SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY a; } } {12 123 scan 5 like 6} do_test like-10.13 { count { 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 3 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} finish_test |
Changes to test/where7.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2008 December 23 # # 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 multi-index OR clause optimizer. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2008 December 23 # # 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 multi-index OR clause optimizer. # # $Id: where7.test,v 1.9 2009/06/07 23:45:11 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !or_opt { finish_test return |
︙ | ︙ | |||
177 178 179 180 181 182 183 | append sql " ORDER BY a" count_steps $sql } {scan 0 sort 1} do_test where7-2.1 { db eval { | | | 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | append sql " ORDER BY a" count_steps $sql } {scan 0 sort 1} do_test where7-2.1 { db eval { CREATE TABLE t2(a INTEGER PRIMARY KEY,b,c,d,e,f TEXT,g); INSERT INTO t2 VALUES(1,11,1001,1.001,100.1,'bcdefghij','yxwvuts'); INSERT INTO t2 VALUES(2,22,1001,2.002,100.1,'cdefghijk','yxwvuts'); INSERT INTO t2 VALUES(3,33,1001,3.0029999999999997,100.1,'defghijkl','xwvutsr'); INSERT INTO t2 VALUES(4,44,2002,4.004,200.2,'efghijklm','xwvutsr'); INSERT INTO t2 VALUES(5,55,2002,5.004999999999999,200.2,'fghijklmn','xwvutsr'); INSERT INTO t2 VALUES(6,66,2002,6.005999999999999,200.2,'ghijklmno','xwvutsr'); INSERT INTO t2 VALUES(7,77,3003,7.007,300.29999999999995,'hijklmnop','xwvutsr'); |
︙ | ︙ | |||
284 285 286 287 288 289 290 | INSERT INTO t2 VALUES(100,1100,34034,100.1,3403.3999999999996,'wxyzabcde','edcbazy'); CREATE INDEX t2b ON t2(b); CREATE INDEX t2c ON t2(c); CREATE INDEX t2d ON t2(d); CREATE INDEX t2e ON t2(e); CREATE INDEX t2f ON t2(f); CREATE INDEX t2g ON t2(g); | | | 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 | INSERT INTO t2 VALUES(100,1100,34034,100.1,3403.3999999999996,'wxyzabcde','edcbazy'); CREATE INDEX t2b ON t2(b); CREATE INDEX t2c ON t2(c); CREATE INDEX t2d ON t2(d); CREATE INDEX t2e ON t2(e); CREATE INDEX t2f ON t2(f); CREATE INDEX t2g ON t2(g); CREATE TABLE t3(a INTEGER PRIMARY KEY,b,c,d,e,f TEXT,g); INSERT INTO t3 SELECT * FROM t2; CREATE INDEX t3b ON t3(b,c); CREATE INDEX t3c ON t3(c,e); CREATE INDEX t3d ON t3(d,g); CREATE INDEX t3e ON t3(e,f,g); CREATE INDEX t3f ON t3(f,b,d,c); CREATE INDEX t3g ON t3(g,f); |
︙ | ︙ |
Changes to test/where8.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The focus # is testing of where.c. More specifically, the focus is the optimization # of WHERE clauses that feature the OR operator. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The focus # is testing of where.c. More specifically, the focus is the optimization # of WHERE clauses that feature the OR operator. # # $Id: where8.test,v 1.8 2009/06/07 23:45:11 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Test organization: # # where8-1.*: Tests to demonstrate simple cases work with a single table |
︙ | ︙ | |||
36 37 38 39 40 41 42 | set ::sqlite_search_count 0 set result [uplevel [list execsql_status $sql $db]] concat $result $::sqlite_search_count } do_test where8-1.1 { execsql { | | | 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | set ::sqlite_search_count 0 set result [uplevel [list execsql_status $sql $db]] concat $result $::sqlite_search_count } do_test where8-1.1 { execsql { CREATE TABLE t1(a, b TEXT, c); CREATE INDEX i1 ON t1(a); CREATE INDEX i2 ON t1(b); INSERT INTO t1 VALUES(1, 'one', 'I'); INSERT INTO t1 VALUES(2, 'two', 'II'); INSERT INTO t1 VALUES(3, 'three', 'III'); INSERT INTO t1 VALUES(4, 'four', 'IV'); |
︙ | ︙ |