/ Check-in [335863e4]
Login

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

Overview
Comment:Make sure the IS NULL optimization introduced by check-in (3494) correctly handles a LEFT JOIN where the a term from the right table of the join uses an IS NULL constraint. Ticket #2177. This check-in also adds the new test cases that were suppose to have been added with (3494) but which were mistakenly omitted. (CVS 3595)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:335863e4d16113fb9ecebce35d2db043771d98b1
User & Date: drh 2007-01-19 01:06:02
Context
2007-01-19
22:59
http://www.sqlite.org/cvstrac/tktview?tn=2166,35

Calling UPDATE against an fts table in a UTF-16 database inserts corrupted data into the database. The UTF-8 data is being inserted directly. This appears to happen because sqlite3_ value_text() destructively coerces a value to UTF-8, and it's never converted back when updating the table. This works around the problem by rearranging things so that the update happens before the coercion. (CVS 3596) check-in: 4f2ab4b6 user: shess tags: trunk

01:06
Make sure the IS NULL optimization introduced by check-in (3494) correctly handles a LEFT JOIN where the a term from the right table of the join uses an IS NULL constraint. Ticket #2177. This check-in also adds the new test cases that were suppose to have been added with (3494) but which were mistakenly omitted. (CVS 3595) check-in: 335863e4 user: drh tags: trunk
2007-01-16
18:19
Additional memory initialization in lemon - bug reported from wireshark. Ticket #2172. Note this problem only comes up with certain grammars, and does not impact SQLite. On the other hand, it might cause SQLite to run slower. So we might want to revisit this change at some point. (CVS 3594) check-in: d537aa5e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.532 2006/12/21 01:29:23 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.533 2007/01/19 01:06:02 drh Exp $
    15     15   */
    16     16   #ifndef _SQLITEINT_H_
    17     17   #define _SQLITEINT_H_
    18     18   
    19     19   /*
    20     20   ** Extra interface definitions for those who need them
    21     21   */
................................................................................
  1087   1087   ** the SrcList.a[] array.
  1088   1088   **
  1089   1089   ** With the addition of multiple database support, the following structure
  1090   1090   ** can also be used to describe a particular table such as the table that
  1091   1091   ** is modified by an INSERT, DELETE, or UPDATE statement.  In standard SQL,
  1092   1092   ** such a table must be a simple name: ID.  But in SQLite, the table can
  1093   1093   ** now be identified by a database name, a dot, then the table name: ID.ID.
         1094  +**
         1095  +** The jointype starts out showing the join type between the current table
         1096  +** and the next table on the list.  The parser builds the list this way.
         1097  +** But sqlite3SrcListShiftJoinType() later shifts the jointypes so that each
         1098  +** jointype expresses the join between the table and the previous table.
  1094   1099   */
  1095   1100   struct SrcList {
  1096   1101     i16 nSrc;        /* Number of tables or subqueries in the FROM clause */
  1097   1102     i16 nAlloc;      /* Number of entries allocated in a[] below */
  1098   1103     struct SrcList_item {
  1099   1104       char *zDatabase;  /* Name of database holding this table */
  1100   1105       char *zName;      /* Name of the table */
  1101   1106       char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
  1102   1107       Table *pTab;      /* An SQL table corresponding to zName */
  1103   1108       Select *pSelect;  /* A SELECT statement used in place of a table name */
  1104   1109       u8 isPopulated;   /* Temporary table associated with SELECT is populated */
  1105         -    u8 jointype;      /* Type of join between this table and the next */
         1110  +    u8 jointype;      /* Type of join between this able and the previous */
  1106   1111       i16 iCursor;      /* The VDBE cursor number used to access this table */
  1107   1112       Expr *pOn;        /* The ON clause of a join */
  1108   1113       IdList *pUsing;   /* The USING clause of a join */
  1109   1114       Bitmask colUsed;  /* Bit N (1<<N) set if column N or pTab is used */
  1110   1115     } a[1];             /* One entry for each identifier on the list */
  1111   1116   };
  1112   1117   

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 reponsible 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.234 2006/12/20 03:24:19 drh Exp $
           19  +** $Id: where.c,v 1.235 2007/01/19 01:06:03 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
    25     25   */
    26     26   #define BMS  (sizeof(Bitmask)*8)
................................................................................
  1271   1271     int bestFlags = 0;          /* Flags associated with bestIdx */
  1272   1272     int bestNEq = 0;            /* Best value for nEq */
  1273   1273     int iCur = pSrc->iCursor;   /* The cursor of the table to be accessed */
  1274   1274     Index *pProbe;              /* An index we are evaluating */
  1275   1275     int rev;                    /* True to scan in reverse order */
  1276   1276     int flags;                  /* Flags associated with pProbe */
  1277   1277     int nEq;                    /* Number of == or IN constraints */
         1278  +  int eqTermMask;             /* Mask of valid equality operators */
  1278   1279     double cost;                /* Cost of using pProbe */
  1279   1280   
  1280   1281     TRACE(("bestIndex: tbl=%s notReady=%x\n", pSrc->pTab->zName, notReady));
  1281   1282     lowestCost = SQLITE_BIG_DBL;
  1282   1283     pProbe = pSrc->pTab->pIndex;
  1283   1284   
  1284   1285     /* If the table has no indices and there are no terms in the where
................................................................................
  1361   1362         TRACE(("... sorting increases cost to %.9g\n", cost));
  1362   1363       }
  1363   1364     }
  1364   1365     if( cost<lowestCost ){
  1365   1366       lowestCost = cost;
  1366   1367       bestFlags = flags;
  1367   1368     }
         1369  +
         1370  +  /* If the pSrc table is the right table of a LEFT JOIN then we may not
         1371  +  ** use an index to satisfy IS NULL constraints on that table.  This is
         1372  +  ** because columns might end up being NULL if the table does not match -
         1373  +  ** a circumstance which the index cannot help us discover.  Ticket #2177.
         1374  +  */
         1375  +  if( (pSrc->jointype & JT_LEFT)!=0 ){
         1376  +    eqTermMask = WO_EQ|WO_IN;
         1377  +  }else{
         1378  +    eqTermMask = WO_EQ|WO_IN|WO_ISNULL;
         1379  +  }
  1368   1380   
  1369   1381     /* Look at each index.
  1370   1382     */
  1371   1383     for(; pProbe; pProbe=pProbe->pNext){
  1372   1384       int i;                       /* Loop counter */
  1373   1385       double inMultiplier = 1;
  1374   1386   
................................................................................
  1376   1388   
  1377   1389       /* Count the number of columns in the index that are satisfied
  1378   1390       ** by x=EXPR constraints or x IN (...) constraints.
  1379   1391       */
  1380   1392       flags = 0;
  1381   1393       for(i=0; i<pProbe->nColumn; i++){
  1382   1394         int j = pProbe->aiColumn[i];
  1383         -      pTerm = findTerm(pWC, iCur, j, notReady, WO_EQ|WO_IN|WO_ISNULL, pProbe);
         1395  +      pTerm = findTerm(pWC, iCur, j, notReady, eqTermMask, pProbe);
  1384   1396         if( pTerm==0 ) break;
  1385   1397         flags |= WHERE_COLUMN_EQ;
  1386   1398         if( pTerm->eOperator & WO_IN ){
  1387   1399           Expr *pExpr = pTerm->pExpr;
  1388   1400           flags |= WHERE_COLUMN_IN;
  1389   1401           if( pExpr->pSelect!=0 ){
  1390   1402             inMultiplier *= 25;
................................................................................
  1632   1644     if( pLevel->flags & WHERE_COLUMN_IN ){
  1633   1645       pParse->nMem += pLevel->nEq;
  1634   1646       termsInMem = 1;
  1635   1647     }
  1636   1648   
  1637   1649     /* Evaluate the equality constraints
  1638   1650     */
  1639         -  for(j=0; j<pIdx->nColumn; j++){
         1651  +  assert( pIdx->nColumn>=nEq );
         1652  +  for(j=0; j<nEq; j++){
  1640   1653       int k = pIdx->aiColumn[j];
  1641   1654       pTerm = findTerm(pWC, iCur, k, notReady, WO_EQ|WO_IN|WO_ISNULL, pIdx);
  1642   1655       if( pTerm==0 ) break;
  1643   1656       assert( (pTerm->flags & TERM_CODED)==0 );
  1644   1657       codeEqualityTerm(pParse, pTerm, brk, pLevel);
  1645   1658       if( (pTerm->eOperator & WO_ISNULL)==0 ){
  1646   1659         sqlite3VdbeAddOp(v, OP_IsNull, termsInMem ? -1 : -(j+1), brk);
  1647   1660       }
  1648   1661       if( termsInMem ){
  1649   1662         sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem+j+1, 1);
  1650   1663       }
  1651   1664     }
  1652         -  assert( j==nEq );
  1653   1665   
  1654   1666     /* Make sure all the constraint values are on the top of the stack
  1655   1667     */
  1656   1668     if( termsInMem ){
  1657   1669       for(j=0; j<nEq; j++){
  1658   1670         sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem+j+1, 0);
  1659   1671       }

Added test/where4.test.

            1  +# 2006 October 27
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the use of indices in WHERE clauses.
           13  +# This file was created when support for optimizing IS NULL phrases
           14  +# was added.  And so the principle purpose of this file is to test
           15  +# that IS NULL phrases are correctly optimized.  But you can never
           16  +# have too many tests, so some other tests are thrown in as well.
           17  +#
           18  +# $Id: where4.test,v 1.1 2007/01/19 01:06:03 drh Exp $
           19  +
           20  +set testdir [file dirname $argv0]
           21  +source $testdir/tester.tcl
           22  +
           23  +# Build some test data
           24  +#
           25  +do_test where4-1.0 {
           26  +  execsql {
           27  +    CREATE TABLE t1(w, x, y);
           28  +    CREATE INDEX i1wxy ON t1(w,x,y);
           29  +    INSERT INTO t1 VALUES(1,2,3);
           30  +    INSERT INTO t1 VALUES(1,NULL,3);
           31  +    INSERT INTO t1 VALUES('a','b','c');
           32  +    INSERT INTO t1 VALUES('a',NULL,'c');
           33  +    INSERT INTO t1 VALUES(X'78',x'79',x'7a');
           34  +    INSERT INTO t1 VALUES(X'78',NULL,X'7A');
           35  +    INSERT INTO t1 VALUES(NULL,NULL,NULL);
           36  +    SELECT count(*) FROM t1;
           37  +  }
           38  +} {7}
           39  +
           40  +# Do an SQL statement.  Append the search count to the end of the result.
           41  +#
           42  +proc count sql {
           43  +  set ::sqlite_search_count 0
           44  +  return [concat [execsql $sql] $::sqlite_search_count]
           45  +}
           46  +
           47  +# Verify that queries use an index.  We are using the special variable
           48  +# "sqlite_search_count" which tallys the number of executions of MoveTo
           49  +# and Next operators in the VDBE.  By verifing that the search count is
           50  +# small we can be assured that indices are being used properly.
           51  +#
           52  +do_test where4-1.1 {
           53  +  count {SELECT rowid FROM t1 WHERE w IS NULL}
           54  +} {7 2}
           55  +do_test where4-1.2 {
           56  +  count {SELECT rowid FROM t1 WHERE +w IS NULL}
           57  +} {7 6}
           58  +do_test where4-1.3 {
           59  +  count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL}
           60  +} {2 2}
           61  +do_test where4-1.4 {
           62  +  count {SELECT rowid FROM t1 WHERE w=1 AND +x IS NULL}
           63  +} {2 3}
           64  +do_test where4-1.5 {
           65  +  count {SELECT rowid FROM t1 WHERE w=1 AND x>0}
           66  +} {1 2}
           67  +do_test where4-1.6 {
           68  +  count {SELECT rowid FROM t1 WHERE w=1 AND x<9}
           69  +} {1 3}
           70  +do_test where4-1.7 {
           71  +  count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y=3}
           72  +} {2 2}
           73  +do_test where4-1.8 {
           74  +  count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL AND y>2}
           75  +} {2 2}
           76  +do_test where4-1.9 {
           77  +  count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'}
           78  +} {4 2}
           79  +do_test where4-1.10 {
           80  +  count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL}
           81  +} {6 2}
           82  +do_test where4-1.11 {
           83  +  count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123}
           84  +} {1}
           85  +do_test where4-1.12 {
           86  +  count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=x'7A'}
           87  +} {6 2}
           88  +do_test where4-1.13 {
           89  +  count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL}
           90  +} {7 2}
           91  +do_test where4-1.14 {
           92  +  count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y IS NULL}
           93  +} {7 2}
           94  +do_test where4-1.15 {
           95  +  count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y<0}
           96  +} {2}
           97  +do_test where4-1.16 {
           98  +  count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL AND y>=0}
           99  +} {1}
          100  +
          101  +do_test where4-2.1 {
          102  +  execsql {SELECT rowid FROM t1 ORDER BY w, x, y}
          103  +} {7 2 1 4 3 6 5}
          104  +do_test where4-2.2 {
          105  +  execsql {SELECT rowid FROM t1 ORDER BY w DESC, x, y}
          106  +} {6 5 4 3 2 1 7}
          107  +do_test where4-2.3 {
          108  +  execsql {SELECT rowid FROM t1 ORDER BY w, x DESC, y}
          109  +} {7 1 2 3 4 5 6}
          110  +
          111  +
          112  +# Ticket #2177
          113  +#
          114  +# Suppose you have a left join where the right table of the left
          115  +# join (the one that can be NULL) has an index on two columns.
          116  +# The first indexed column is used in the ON clause of the join.
          117  +# The second indexed column is used in the WHERE clause with an IS NULL
          118  +# constraint.  It is not allowed to use the IS NULL optimization to
          119  +# optimize the query because the second column might be NULL because
          120  +# the right table did not match - something the index does not know
          121  +# about.
          122  +#
          123  +do_test where4-3.1 {
          124  +  execsql {
          125  +    CREATE TABLE t2(a);
          126  +    INSERT INTO t2 VALUES(1);
          127  +    INSERT INTO t2 VALUES(2);
          128  +    INSERT INTO t2 VALUES(3);
          129  +    CREATE TABLE t3(x,y,UNIQUE(x,y));
          130  +    INSERT INTO t3 VALUES(1,11);
          131  +    INSERT INTO t3 VALUES(2,NULL);
          132  + 
          133  +    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
          134  +  }
          135  +} {2 2 {} 3 {} {}}
          136  +do_test where4-3.2 {
          137  +  execsql {
          138  +    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
          139  +  }
          140  +} {2 2 {} 3 {} {}}
          141  +
          142  +integrity_check {where4-99.0}
          143  +
          144  +finish_test