/ Check-in [5aea8148]
Login

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

Overview
Comment:Do all WHERE clauses tests, even if an index is used for lookup so that we know the test cannot be FALSE. The test might end up being NULL in which case it would need to be treated as false. Ticket #461. (CVS 1103)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:5aea81488b2d3bcdc009ccf0f0ffcda046e38d79
User & Date: drh 2003-09-27 00:41:28
Context
2003-09-27
00:56
Add additional backslash escapes to the COPY command for compatibility with PostgreSQL. Ticket #460. (CVS 1104) check-in: 70a50bdd user: drh tags: trunk
00:41
Do all WHERE clauses tests, even if an index is used for lookup so that we know the test cannot be FALSE. The test might end up being NULL in which case it would need to be treated as false. Ticket #461. (CVS 1103) check-in: 5aea8148 user: drh tags: trunk
2003-09-23
10:25
A failed malloc() could have resulted in a segfault. Fixed by this check-in. (CVS 1102) check-in: 4bb256ee user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
...
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
...
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
...
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
....
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.81 2003/07/16 00:54:31 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.
................................................................................
          if( pX==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            if( pX->op==TK_EQ ){
              sqliteExprCode(pParse, pX->pRight);
              aExpr[k].p = 0;
              break;
            }
            if( pX->op==TK_IN && nColumn==1 ){
              if( pX->pList ){
                sqliteVdbeAddOp(v, OP_SetFirst, pX->iTable, brk);
                pLevel->inOp = OP_SetNext;
                pLevel->inP1 = pX->iTable;
................................................................................
                assert( pX->pSelect );
                sqliteVdbeAddOp(v, OP_Rewind, pX->iTable, brk);
                sqliteVdbeAddOp(v, OP_KeyAsData, pX->iTable, 1);
                pLevel->inP2 = sqliteVdbeAddOp(v, OP_FullKey, pX->iTable, 0);
                pLevel->inOp = OP_Next;
                pLevel->inP1 = pX->iTable;
              }
              aExpr[k].p = 0;
              break;
            }
          }
          if( aExpr[k].idxRight==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_MakeKey, nColumn, 0);
................................................................................
          if( aExpr[k].p==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && aExpr[k].p->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pRight);
            aExpr[k].p = 0;
            break;
          }
          if( aExpr[k].idxRight==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }

      /* Duplicate the equality term values because they will all be
      ** used twice: once to make the termination key and once to make the
................................................................................
          if( aExpr[k].idxLeft==iCur
             && (pExpr->op==TK_LT || pExpr->op==TK_LE)
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pExpr->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pRight);
            leFlag = pExpr->op==TK_LE;
            aExpr[k].p = 0;
            break;
          }
          if( aExpr[k].idxRight==iCur
             && (pExpr->op==TK_GT || pExpr->op==TK_GE)
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && pExpr->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pLeft);
            leFlag = pExpr->op==TK_GE;
            aExpr[k].p = 0;
            break;
          }
        }
        testOp = OP_IdxGE;
      }else{
        testOp = nEqColumn>0 ? OP_IdxGE : OP_Noop;
        leFlag = 1;
................................................................................
          if( aExpr[k].idxLeft==iCur
             && (pExpr->op==TK_GT || pExpr->op==TK_GE)
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pExpr->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pRight);
            geFlag = pExpr->op==TK_GE;
            aExpr[k].p = 0;
            break;
          }
          if( aExpr[k].idxRight==iCur
             && (pExpr->op==TK_LT || pExpr->op==TK_LE)
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && pExpr->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pLeft);
            geFlag = pExpr->op==TK_LE;
            aExpr[k].p = 0;
            break;
          }
        }
      }else{
        geFlag = 1;
      }
      if( nEqColumn>0 || (score&2)!=0 ){







|







 







|







 







|









|







 







|








|







 







|









|







 







|









|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
...
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
...
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
...
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
....
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.82 2003/09/27 00:41:28 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.
................................................................................
          if( pX==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            if( pX->op==TK_EQ ){
              sqliteExprCode(pParse, pX->pRight);
              /* aExpr[k].p = 0; // See ticket #461 */
              break;
            }
            if( pX->op==TK_IN && nColumn==1 ){
              if( pX->pList ){
                sqliteVdbeAddOp(v, OP_SetFirst, pX->iTable, brk);
                pLevel->inOp = OP_SetNext;
                pLevel->inP1 = pX->iTable;
................................................................................
                assert( pX->pSelect );
                sqliteVdbeAddOp(v, OP_Rewind, pX->iTable, brk);
                sqliteVdbeAddOp(v, OP_KeyAsData, pX->iTable, 1);
                pLevel->inP2 = sqliteVdbeAddOp(v, OP_FullKey, pX->iTable, 0);
                pLevel->inOp = OP_Next;
                pLevel->inP1 = pX->iTable;
              }
              /* aExpr[k].p = 0; // See ticket #461 */
              break;
            }
          }
          if( aExpr[k].idxRight==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            /* aExpr[k].p = 0; // See ticket #461 */
            break;
          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      cont = pLevel->cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_MakeKey, nColumn, 0);
................................................................................
          if( aExpr[k].p==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && aExpr[k].p->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pRight);
            /* aExpr[k].p = 0; // See ticket #461 */
            break;
          }
          if( aExpr[k].idxRight==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            /* aExpr[k].p = 0; // See ticket #461 */
            break;
          }
        }
      }

      /* Duplicate the equality term values because they will all be
      ** used twice: once to make the termination key and once to make the
................................................................................
          if( aExpr[k].idxLeft==iCur
             && (pExpr->op==TK_LT || pExpr->op==TK_LE)
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pExpr->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pRight);
            leFlag = pExpr->op==TK_LE;
            /* aExpr[k].p = 0; // See ticket #461 */
            break;
          }
          if( aExpr[k].idxRight==iCur
             && (pExpr->op==TK_GT || pExpr->op==TK_GE)
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && pExpr->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pLeft);
            leFlag = pExpr->op==TK_GE;
            /* aExpr[k].p = 0; // See ticket #461 */
            break;
          }
        }
        testOp = OP_IdxGE;
      }else{
        testOp = nEqColumn>0 ? OP_IdxGE : OP_Noop;
        leFlag = 1;
................................................................................
          if( aExpr[k].idxLeft==iCur
             && (pExpr->op==TK_GT || pExpr->op==TK_GE)
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pExpr->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pRight);
            geFlag = pExpr->op==TK_GE;
            /* aExpr[k].p = 0; // See ticket #461 */
            break;
          }
          if( aExpr[k].idxRight==iCur
             && (pExpr->op==TK_LT || pExpr->op==TK_LE)
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && pExpr->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, pExpr->pLeft);
            geFlag = pExpr->op==TK_LE;
            /* aExpr[k].p = 0; // See ticket #461 */
            break;
          }
        }
      }else{
        geFlag = 1;
      }
      if( nEqColumn>0 || (score&2)!=0 ){

Changes to test/index.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
#    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 CREATE INDEX statement.
#
# $Id: index.test,v 1.23 2003/06/15 23:42:25 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
    SELECT c FROM t6 WHERE a>=123;
  }
} {5 2 1 4}
do_test index-14.8 {
  execsql {
    SELECT c FROM t6 WHERE a<'abc';
  }
} {3 5 2 1}
do_test index-14.9 {
  execsql {
    SELECT c FROM t6 WHERE a<='abc';
  }
} {3 5 2 1 4}
do_test index-14.10 {
  execsql {
    SELECT c FROM t6 WHERE a<='';
  }
} {3 5 2 1}
do_test index-14.11 {
  execsql {
    SELECT c FROM t6 WHERE a<'';
  }
} {3 5}
integrity_check index-14.12

do_test index-15.1 {
  execsql {
    DELETE FROM t1;
    SELECT * FROM t1;
  }







|







 







|




|




|




|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
#    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 CREATE INDEX statement.
#
# $Id: index.test,v 1.24 2003/09/27 00:41:28 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
    SELECT c FROM t6 WHERE a>=123;
  }
} {5 2 1 4}
do_test index-14.8 {
  execsql {
    SELECT c FROM t6 WHERE a<'abc';
  }
} {5 2 1}
do_test index-14.9 {
  execsql {
    SELECT c FROM t6 WHERE a<='abc';
  }
} {5 2 1 4}
do_test index-14.10 {
  execsql {
    SELECT c FROM t6 WHERE a<='';
  }
} {5 2 1}
do_test index-14.11 {
  execsql {
    SELECT c FROM t6 WHERE a<'';
  }
} {5}
integrity_check index-14.12

do_test index-15.1 {
  execsql {
    DELETE FROM t1;
    SELECT * FROM t1;
  }

Changes to test/null.test.

168
169
170
171
172
173
174
175


176






177


















































178
    insert into t3 values(1,1,1);
    insert into t3 values(2,null,1);
    insert into t3 values(3,null,1);
    insert into t3 values(4,1,1);
    select a from t3;
  }
} {1 2 3}





























































finish_test








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

168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
    insert into t3 values(1,1,1);
    insert into t3 values(2,null,1);
    insert into t3 values(3,null,1);
    insert into t3 values(4,1,1);
    select a from t3;
  }
} {1 2 3}

# Ticket #461 - Make sure nulls are handled correctly when doing a
# lookup using an index.
#
do_test null-8.1 {
  execsql {
    CREATE TABLE t4(x,y);
    INSERT INTO t4 VALUES(1,11);
    INSERT INTO t4 VALUES(2,NULL);
    SELECT x FROM t4 WHERE y=NULL;
  }
} {}
do_test null-8.2 {
  execsql {
    SELECT x FROM t4 WHERE y IN (33,NULL);
  }
} {}
do_test null-8.3 {
  execsql {
    SELECT x FROM t4 WHERE y<33 ORDER BY x;
  }
} {1}
do_test null-8.4 {
  execsql {
    SELECT x FROM t4 WHERE y>6 ORDER BY x;
  }
} {1}
do_test null-8.5 {
  execsql {
    SELECT x FROM t4 WHERE y!=33 ORDER BY x;
  }
} {1}
do_test null-8.11 {
  execsql {
    CREATE INDEX t4i1 ON t4(y);
    SELECT x FROM t4 WHERE y=NULL;
  }
} {}
do_test null-8.12 {
  execsql {
    SELECT x FROM t4 WHERE y IN (33,NULL);
  }
} {}
do_test null-8.13 {
  execsql {
    SELECT x FROM t4 WHERE y<33 ORDER BY x;
  }
} {1}
do_test null-8.14 {
  execsql {
    SELECT x FROM t4 WHERE y>6 ORDER BY x;
  }
} {1}
do_test null-8.15 {
  execsql {
    SELECT x FROM t4 WHERE y!=33 ORDER BY x;
  }
} {1}



finish_test