/ Check-in [1bee1bb9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix for ticket #1062: Correctly handle redundant terms in a WHERE clause. (CVS 2198)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1bee1bb91d39ff8a33efe662b6cab6dbd2d50e3c
User & Date: drh 2005-01-11 18:13:56
Context
2005-01-12
00:08
Fix a bug in tclsqlite.c. (CVS 2199) check-in: 50f1e229 user: drh tags: trunk
2005-01-11
18:13
Fix for ticket #1062: Correctly handle redundant terms in a WHERE clause. (CVS 2198) check-in: 1bee1bb9 user: drh tags: trunk
17:59
Allow '$' in the middle of identifiers. Ticket #1066. This is an experimental change. It might be backed out at a later date. (CVS 2197) check-in: 00a352ea 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
....
1011
1012
1013
1014
1015
1016
1017

1018
1019
1020
1021
1022
1023
1024
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible 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.127 2005/01/03 18:13:18 tpoindex Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
      for(j=0; j<nColumn; j++){
        for(pTerm=aExpr, k=0; k<nExpr; k++, pTerm++){
          Expr *pX = pTerm->p;
          if( pX==0 ) continue;
          if( pTerm->idxLeft==iCur
             && (pTerm->prereqRight & loopMask)==pTerm->prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]

          ){
            char idxaff = pIdx->pTable->aCol[pX->pLeft->iColumn].affinity;
            if( sqlite3IndexAffinityOk(pX, idxaff) ){
              codeEqualityTerm(pParse, pTerm, brk, pLevel);
              break;
            }
          }







|







 







>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible 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.128 2005/01/11 18:13:56 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
      for(j=0; j<nColumn; j++){
        for(pTerm=aExpr, k=0; k<nExpr; k++, pTerm++){
          Expr *pX = pTerm->p;
          if( pX==0 ) continue;
          if( pTerm->idxLeft==iCur
             && (pTerm->prereqRight & loopMask)==pTerm->prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
             && (pX->op==TK_EQ || pX->op==TK_IN)
          ){
            char idxaff = pIdx->pTable->aCol[pX->pLeft->iColumn].affinity;
            if( sqlite3IndexAffinityOk(pX, idxaff) ){
              codeEqualityTerm(pParse, pTerm, brk, pLevel);
              break;
            }
          }

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
154
155
156
157
158
159
160















161
162
163
164
165
166
167
...
418
419
420
421
422
423
424










425
426
427
428
429
430
431
#    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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.26 2004/12/19 00:11:36 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
} {98 99 100 3}
do_test where-1.31 {
  count {SELECT w FROM t1 WHERE w>=97}
} {97 98 99 100 4}
do_test where-1.33 {
  count {SELECT w FROM t1 WHERE w==97}
} {97 2}















do_test where-1.34 {
  count {SELECT w FROM t1 WHERE w+1==98}
} {97 99}
do_test where-1.35 {
  count {SELECT w FROM t1 WHERE w<3}
} {1 2 2}
do_test where-1.36 {
................................................................................
    SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
do_test where-6.9.1 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }










} {1 100 4 nosort}
do_test where-6.9.2 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.3 {







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
...
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
#    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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.27 2005/01/11 18:13:57 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
} {98 99 100 3}
do_test where-1.31 {
  count {SELECT w FROM t1 WHERE w>=97}
} {97 98 99 100 4}
do_test where-1.33 {
  count {SELECT w FROM t1 WHERE w==97}
} {97 2}
do_test where-1.33.1  {
  count {SELECT w FROM t1 WHERE w<=97 AND w==97}
} {97 2}
do_test where-1.33.2  {
  count {SELECT w FROM t1 WHERE w<98 AND w==97}
} {97 2}
do_test where-1.33.3  {
  count {SELECT w FROM t1 WHERE w>=97 AND w==97}
} {97 2}
do_test where-1.33.4  {
  count {SELECT w FROM t1 WHERE w>96 AND w==97}
} {97 2}
do_test where-1.33.5  {
  count {SELECT w FROM t1 WHERE w==97 AND w==97}
} {97 2}
do_test where-1.34 {
  count {SELECT w FROM t1 WHERE w+1==98}
} {97 99}
do_test where-1.35 {
  count {SELECT w FROM t1 WHERE w<3}
} {1 2 2}
do_test where-1.36 {
................................................................................
    SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
do_test where-6.9.1 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.1.1 {
  cksort {
    SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.1.2 {
  cksort {
    SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.2 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.3 {