/ Check-in [a255c645]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
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
** 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.401 2009/06/06 15:17:28 drh Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
................................................................................
  }
  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 ){
    /* No collation is defined for the ROWID.  Use the default. */
    pColl = db->pDfltColl;
  }
  if( (pColl->type!=SQLITE_COLL_BINARY || *pnoCase) &&
      (pColl->type!=SQLITE_COLL_NOCASE || !*pnoCase) ){
    return 0;
  }

  z = pRight->u.zToken;
  cnt = 0;
  if( 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;
  }







|







 







|
<
<
<




>


<
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
650
651
652
653
654
655
656
657



658
659
660
661
662
663
664

665
666
667
668
669
670
671
672
** 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)
................................................................................
  }
  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
16
17
18
19
20
21
22
23
...
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
...
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
...
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
...
625
626
627
628
629
630
631
632

633






































































































634
#
#***********************************************************************
# 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.12 2009/01/09 21:41:17 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create some sample data to work with.
#
do_test like-1.0 {
................................................................................
  }
} {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 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
  }
................................................................................
  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);
    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%';
  }
................................................................................
  execsql {
    SELECT * FROM t2 WHERE x LIKE '''a%'
  }
} {'abc 'ax}

do_test like-7.1 {
  execsql {
    SELECT * FROM t1 WHERE rowid GLOB '1*';
  }
} {a}

# 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 {
................................................................................
      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







|







 







|







 







|







 







|

|







 








>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
...
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
...
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
...
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
#
#***********************************************************************
# 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 {
................................................................................
  }
} {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
  }
................................................................................
  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%';
  }
................................................................................
  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 {
................................................................................
      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.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
...
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
#    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.8 2009/04/21 09:02:47 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !or_opt {
  finish_test
  return
................................................................................
  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,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');
................................................................................
    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,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);







|







 







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
...
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
#    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
................................................................................
  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');
................................................................................
    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
15
16
17
18
19
20
21
22
..
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#    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.7 2009/06/05 17:09:12 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
................................................................................
  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, 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');







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#    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
................................................................................
  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');