/ Check-in [a255c645]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a255c645c46ae03b65f862858fe57e462076e1fc
User & Date: drh 2009-06-07 23:45:11
Context
2009-06-08
12:52
Increase the version number to 3.6.15 in preparation for the next release. (CVS 6728) check-in: 456ea541 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: a255c645 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: ba9848e7 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  This module is responsible for
    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   **
    19         -** $Id: where.c,v 1.401 2009/06/06 15:17:28 drh Exp $
           19  +** $Id: where.c,v 1.402 2009/06/07 23:45:11 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** Trace output macros
    25     25   */
    26     26   #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
................................................................................
   650    650     }
   651    651     pLeft = pList->a[1].pExpr;
   652    652     if( pLeft->op!=TK_COLUMN ){
   653    653       return 0;
   654    654     }
   655    655     pColl = sqlite3ExprCollSeq(pParse, pLeft);
   656    656     assert( pColl!=0 || pLeft->iColumn==-1 );
   657         -  if( pColl==0 ){
   658         -    /* No collation is defined for the ROWID.  Use the default. */
   659         -    pColl = db->pDfltColl;
   660         -  }
          657  +  if( pColl==0 ) return 0;
   661    658     if( (pColl->type!=SQLITE_COLL_BINARY || *pnoCase) &&
   662    659         (pColl->type!=SQLITE_COLL_NOCASE || !*pnoCase) ){
   663    660       return 0;
   664    661     }
          662  +  if( sqlite3ExprAffinity(pLeft)!=SQLITE_AFF_TEXT ) return 0;
   665    663     z = pRight->u.zToken;
   666    664     cnt = 0;
   667         -  if( z ){
          665  +  if( ALWAYS(z) ){
   668    666       while( (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2] ){
   669    667         cnt++;
   670    668       }
   671    669     }
   672    670     if( cnt==0 || c==0 || 255==(u8)z[cnt-1] ){
   673    671       return 0;
   674    672     }

Changes to test/like.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the LIKE and GLOB operators and
    13     13   # in particular the optimizations that occur to help those operators
    14     14   # run faster.
    15     15   #
    16         -# $Id: like.test,v 1.12 2009/01/09 21:41:17 drh Exp $
           16  +# $Id: like.test,v 1.13 2009/06/07 23:45:11 drh Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   # Create some sample data to work with.
    22     22   #
    23     23   do_test like-1.0 {
................................................................................
   353    353     }
   354    354   } {ABC {ABC abc xyz} abc abcd nosort {} i1}
   355    355   do_test like-5.2 {
   356    356     set sqlite_like_count
   357    357   } 12
   358    358   do_test like-5.3 {
   359    359     execsql {
   360         -    CREATE TABLE t2(x COLLATE NOCASE);
          360  +    CREATE TABLE t2(x TEXT COLLATE NOCASE);
   361    361       INSERT INTO t2 SELECT * FROM t1;
   362    362       CREATE INDEX i2 ON t2(x COLLATE NOCASE);
   363    363     }
   364    364     set sqlite_like_count 0
   365    365     queryplan {
   366    366       SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
   367    367     }
................................................................................
   472    472     queryplan {
   473    473       SELECT x FROM t2 WHERE x LIKE 'ZZ%';
   474    474     }
   475    475   } {zz-lower-lower zZ-lower-upper Zz-upper-lower ZZ-upper-upper nosort {} i2}
   476    476   do_test like-5.25 {
   477    477     queryplan {
   478    478       PRAGMA case_sensitive_like=on;
   479         -    CREATE TABLE t3(x);
          479  +    CREATE TABLE t3(x TEXT);
   480    480       CREATE INDEX i3 ON t3(x);
   481    481       INSERT INTO t3 VALUES('ZZ-upper-upper');
   482    482       INSERT INTO t3 VALUES('zZ-lower-upper');
   483    483       INSERT INTO t3 VALUES('Zz-upper-lower');
   484    484       INSERT INTO t3 VALUES('zz-lower-lower');
   485    485       SELECT x FROM t3 WHERE x LIKE 'zz%';
   486    486     }
................................................................................
   515    515     execsql {
   516    516       SELECT * FROM t2 WHERE x LIKE '''a%'
   517    517     }
   518    518   } {'abc 'ax}
   519    519   
   520    520   do_test like-7.1 {
   521    521     execsql {
   522         -    SELECT * FROM t1 WHERE rowid GLOB '1*';
          522  +    SELECT rowid, * FROM t1 WHERE rowid GLOB '1*' ORDER BY rowid;
   523    523     }
   524         -} {a}
          524  +} {1 a 10 ABC 11 CDE 12 {ABC abc xyz}}
   525    525   
   526    526   # ticket #3345.
   527    527   #
   528    528   # Overloading the LIKE function with -1 for the number of arguments
   529    529   # will overload both the 2-argument and the 3-argument LIKE.
   530    530   #
   531    531   do_test like-8.1 {
................................................................................
   625    625         set res [sqlite3_exec_hex db {
   626    626            EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%fe%25'
   627    627         }]
   628    628         regexp {INDEX i2} $res
   629    629       } {1}
   630    630     }
   631    631   }
          632  +
          633  +# Do an SQL statement.  Append the search count to the end of the result.
          634  +#
          635  +proc count sql {
          636  +  set ::sqlite_search_count 0
          637  +  set ::sqlite_like_count 0
          638  +  return [concat [execsql $sql] scan $::sqlite_search_count \
          639  +           like $::sqlite_like_count]
          640  +}
          641  +
          642  +# The LIKE and GLOB optimizations do not work on columns with
          643  +# affinity other than TEXT.
          644  +# Ticket #3901
          645  +#
          646  +do_test like-10.1 {
          647  +  db close
          648  +  sqlite3 db test.db
          649  +  execsql {
          650  +    CREATE TABLE t10(
          651  +      a INTEGER PRIMARY KEY,
          652  +      b INTEGER COLLATE nocase UNIQUE,
          653  +      c NUMBER COLLATE nocase UNIQUE,
          654  +      d BLOB COLLATE nocase UNIQUE,
          655  +      e COLLATE nocase UNIQUE,
          656  +      f TEXT COLLATE nocase UNIQUE
          657  +    );
          658  +    INSERT INTO t10 VALUES(1,1,1,1,1,1);
          659  +    INSERT INTO t10 VALUES(12,12,12,12,12,12);
          660  +    INSERT INTO t10 VALUES(123,123,123,123,123,123);
          661  +    INSERT INTO t10 VALUES(234,234,234,234,234,234);
          662  +    INSERT INTO t10 VALUES(345,345,345,345,345,345);
          663  +    INSERT INTO t10 VALUES(45,45,45,45,45,45);
          664  +  }
          665  +  count {
          666  +    SELECT a FROM t10 WHERE b LIKE '12%' ORDER BY a;
          667  +  }
          668  +} {12 123 scan 5 like 6}
          669  +do_test like-10.2 {
          670  +  count {
          671  +    SELECT a FROM t10 WHERE c LIKE '12%' ORDER BY a;
          672  +  }
          673  +} {12 123 scan 5 like 6}
          674  +do_test like-10.3 {
          675  +  count {
          676  +    SELECT a FROM t10 WHERE d LIKE '12%' ORDER BY a;
          677  +  }
          678  +} {12 123 scan 5 like 6}
          679  +do_test like-10.4 {
          680  +  count {
          681  +    SELECT a FROM t10 WHERE e LIKE '12%' ORDER BY a;
          682  +  }
          683  +} {12 123 scan 5 like 6}
          684  +do_test like-10.5 {
          685  +  count {
          686  +    SELECT a FROM t10 WHERE f LIKE '12%' ORDER BY a;
          687  +  }
          688  +} {12 123 scan 3 like 0}
          689  +do_test like-10.6 {
          690  +  count {
          691  +    SELECT a FROM t10 WHERE a LIKE '12%' ORDER BY a;
          692  +  }
          693  +} {12 123 scan 5 like 6}
          694  +do_test like-10.10 {
          695  +  execsql {
          696  +    CREATE TABLE t10b(
          697  +      a INTEGER PRIMARY KEY,
          698  +      b INTEGER UNIQUE,
          699  +      c NUMBER UNIQUE,
          700  +      d BLOB UNIQUE,
          701  +      e UNIQUE,
          702  +      f TEXT UNIQUE
          703  +    );
          704  +    INSERT INTO t10b SELECT * FROM t10;
          705  +  }
          706  +  count {
          707  +    SELECT a FROM t10b WHERE b GLOB '12*' ORDER BY a;
          708  +  }
          709  +} {12 123 scan 5 like 6}
          710  +do_test like-10.11 {
          711  +  count {
          712  +    SELECT a FROM t10b WHERE c GLOB '12*' ORDER BY a;
          713  +  }
          714  +} {12 123 scan 5 like 6}
          715  +do_test like-10.12 {
          716  +  count {
          717  +    SELECT a FROM t10b WHERE d GLOB '12*' ORDER BY a;
          718  +  }
          719  +} {12 123 scan 5 like 6}
          720  +do_test like-10.13 {
          721  +  count {
          722  +    SELECT a FROM t10b WHERE e GLOB '12*' ORDER BY a;
          723  +  }
          724  +} {12 123 scan 5 like 6}
          725  +do_test like-10.14 {
          726  +  count {
          727  +    SELECT a FROM t10b WHERE f GLOB '12*' ORDER BY a;
          728  +  }
          729  +} {12 123 scan 3 like 0}
          730  +do_test like-10.15 {
          731  +  count {
          732  +    SELECT a FROM t10b WHERE a GLOB '12*' ORDER BY a;
          733  +  }
          734  +} {12 123 scan 5 like 6}
   632    735   
   633    736   
   634    737   finish_test

Changes to test/where7.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the multi-index OR clause optimizer.
    13     13   #
    14         -# $Id: where7.test,v 1.8 2009/04/21 09:02:47 danielk1977 Exp $
           14  +# $Id: where7.test,v 1.9 2009/06/07 23:45:11 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   ifcapable !or_opt {
    20     20     finish_test
    21     21     return
................................................................................
   177    177     append sql " ORDER BY a"
   178    178     count_steps $sql
   179    179   } {scan 0 sort 1}
   180    180   
   181    181   
   182    182   do_test where7-2.1 {
   183    183     db eval {
   184         -    CREATE TABLE t2(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
          184  +    CREATE TABLE t2(a INTEGER PRIMARY KEY,b,c,d,e,f TEXT,g);
   185    185       INSERT INTO t2 VALUES(1,11,1001,1.001,100.1,'bcdefghij','yxwvuts');
   186    186       INSERT INTO t2 VALUES(2,22,1001,2.002,100.1,'cdefghijk','yxwvuts');
   187    187       INSERT INTO t2 VALUES(3,33,1001,3.0029999999999997,100.1,'defghijkl','xwvutsr');
   188    188       INSERT INTO t2 VALUES(4,44,2002,4.004,200.2,'efghijklm','xwvutsr');
   189    189       INSERT INTO t2 VALUES(5,55,2002,5.004999999999999,200.2,'fghijklmn','xwvutsr');
   190    190       INSERT INTO t2 VALUES(6,66,2002,6.005999999999999,200.2,'ghijklmno','xwvutsr');
   191    191       INSERT INTO t2 VALUES(7,77,3003,7.007,300.29999999999995,'hijklmnop','xwvutsr');
................................................................................
   284    284       INSERT INTO t2 VALUES(100,1100,34034,100.1,3403.3999999999996,'wxyzabcde','edcbazy');
   285    285       CREATE INDEX t2b ON t2(b);
   286    286       CREATE INDEX t2c ON t2(c);
   287    287       CREATE INDEX t2d ON t2(d);
   288    288       CREATE INDEX t2e ON t2(e);
   289    289       CREATE INDEX t2f ON t2(f);
   290    290       CREATE INDEX t2g ON t2(g);
   291         -    CREATE TABLE t3(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
          291  +    CREATE TABLE t3(a INTEGER PRIMARY KEY,b,c,d,e,f TEXT,g);
   292    292       INSERT INTO t3 SELECT * FROM t2;
   293    293       CREATE INDEX t3b ON t3(b,c);
   294    294       CREATE INDEX t3c ON t3(c,e);
   295    295       CREATE INDEX t3d ON t3(d,g);
   296    296       CREATE INDEX t3e ON t3(e,f,g);
   297    297       CREATE INDEX t3f ON t3(f,b,d,c);
   298    298       CREATE INDEX t3g ON t3(g,f);

Changes to test/where8.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library. The focus
    12     12   # is testing of where.c. More specifically, the focus is the optimization
    13     13   # of WHERE clauses that feature the OR operator.
    14     14   #
    15         -# $Id: where8.test,v 1.7 2009/06/05 17:09:12 drh Exp $
           15  +# $Id: where8.test,v 1.8 2009/06/07 23:45:11 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Test organization:
    21     21   #
    22     22   #   where8-1.*: Tests to demonstrate simple cases work with a single table
................................................................................
    36     36     set ::sqlite_search_count 0
    37     37     set result [uplevel [list execsql_status $sql $db]]
    38     38     concat $result $::sqlite_search_count
    39     39   }
    40     40   
    41     41   do_test where8-1.1 {
    42     42     execsql {
    43         -    CREATE TABLE t1(a, b, c);
           43  +    CREATE TABLE t1(a, b TEXT, c);
    44     44       CREATE INDEX i1 ON t1(a);
    45     45       CREATE INDEX i2 ON t1(b);
    46     46   
    47     47       INSERT INTO t1 VALUES(1,  'one',   'I');
    48     48       INSERT INTO t1 VALUES(2,  'two',   'II');
    49     49       INSERT INTO t1 VALUES(3,  'three', 'III');
    50     50       INSERT INTO t1 VALUES(4,  'four',  'IV');