/ Check-in [d741e1cc]
Login

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

Overview
Comment:Avoid an unnecessary key comparison when doing an indexed lookup against an equality constraint.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:d741e1ccdce96e6142e294fbdd20a0282296dcea
User & Date: drh 2015-11-06 20:22:25
Context
2015-11-07
00:51
Fix a harmless sanitizer warning in the ieee754 extension. check-in: dd9a26ec user: drh tags: trunk
2015-11-06
20:22
Avoid an unnecessary key comparison when doing an indexed lookup against an equality constraint. check-in: d741e1cc user: drh tags: trunk
20:13
Work around a sign-extension bug in the optimizer on the HP C compiler that comes with HP/UX. check-in: 46c36b15 user: drh tags: trunk
2015-11-05
22:30
Improvements and simplifications to the equality seek logic. Tests are adjusted so that they all pass now. Closed-Leaf check-in: 997ce6c9 user: drh tags: seekeq-experiment
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  5037   5037   **
  5038   5038   **     *pRes==0     The cursor is left pointing at an entry that
  5039   5039   **                  exactly matches intKey/pIdxKey.
  5040   5040   **
  5041   5041   **     *pRes>0      The cursor is left pointing at an entry that
  5042   5042   **                  is larger than intKey/pIdxKey.
  5043   5043   **
         5044  +** For index tables, the pIdxKey->eqSeen field is set to 1 if there
         5045  +** exists an entry in the table that exactly matches pIdxKey.  
  5044   5046   */
  5045   5047   int sqlite3BtreeMovetoUnpacked(
  5046   5048     BtCursor *pCur,          /* The cursor to be moved */
  5047   5049     UnpackedRecord *pIdxKey, /* Unpacked index key */
  5048   5050     i64 intKey,              /* The table key */
  5049   5051     int biasRight,           /* If true, bias the search to the high end */
  5050   5052     int *pRes                /* Write search results here */
................................................................................
  9640   9642       }
  9641   9643     }
  9642   9644   
  9643   9645     pBt->btsFlags &= ~BTS_NO_WAL;
  9644   9646     return rc;
  9645   9647   }
  9646   9648   
  9647         -#ifdef SQLITE_DEBUG
  9648   9649   /*
  9649   9650   ** Return true if the cursor has a hint specified.  This routine is
  9650   9651   ** only used from within assert() statements
  9651   9652   */
  9652   9653   int sqlite3BtreeCursorHasHint(BtCursor *pCsr, unsigned int mask){
  9653   9654     return (pCsr->hints & mask)!=0;
  9654   9655   }
  9655         -#endif
  9656   9656   
  9657   9657   /*
  9658   9658   ** Return true if the given Btree is read-only.
  9659   9659   */
  9660   9660   int sqlite3BtreeIsReadonly(Btree *p){
  9661   9661     return (p->pBt->btsFlags & BTS_READ_ONLY)!=0;
  9662   9662   }
  9663   9663   
  9664   9664   /*
  9665   9665   ** Return the size of the header added to each page by this module.
  9666   9666   */
  9667   9667   int sqlite3HeaderSizeBtree(void){ return ROUND8(sizeof(MemPage)); }

Changes to src/btree.h.

   253    253   char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*);
   254    254   struct Pager *sqlite3BtreePager(Btree*);
   255    255   
   256    256   int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*);
   257    257   void sqlite3BtreeIncrblobCursor(BtCursor *);
   258    258   void sqlite3BtreeClearCursor(BtCursor *);
   259    259   int sqlite3BtreeSetVersion(Btree *pBt, int iVersion);
   260         -#ifdef SQLITE_DEBUG
   261    260   int sqlite3BtreeCursorHasHint(BtCursor*, unsigned int mask);
   262         -#endif
   263    261   int sqlite3BtreeIsReadonly(Btree *pBt);
   264    262   int sqlite3HeaderSizeBtree(void);
   265    263   
   266    264   #ifndef NDEBUG
   267    265   int sqlite3BtreeCursorIsValid(BtCursor*);
   268    266   #endif
   269    267   

Changes to src/sqliteInt.h.

  1808   1808     u16 nXField;        /* Number of columns beyond the key columns */
  1809   1809     sqlite3 *db;        /* The database connection */
  1810   1810     u8 *aSortOrder;     /* Sort order for each column. */
  1811   1811     CollSeq *aColl[1];  /* Collating sequence for each term of the key */
  1812   1812   };
  1813   1813   
  1814   1814   /*
  1815         -** An instance of the following structure holds information about a
  1816         -** single index record that has already been parsed out into individual
  1817         -** values.
         1815  +** This object holds a record which has been parsed out into individual
         1816  +** fields, for the purposes of doing a comparison.
  1818   1817   **
  1819   1818   ** A record is an object that contains one or more fields of data.
  1820   1819   ** Records are used to store the content of a table row and to store
  1821   1820   ** the key of an index.  A blob encoding of a record is created by
  1822   1821   ** the OP_MakeRecord opcode of the VDBE and is disassembled by the
  1823   1822   ** OP_Column opcode.
  1824   1823   **
  1825         -** This structure holds a record that has already been disassembled
  1826         -** into its constituent fields.
         1824  +** An instance of this object serves as a "key" for doing a search on
         1825  +** an index b+tree. The goal of the search is to find the entry that
         1826  +** is closed to the key described by this object.  This object might hold
         1827  +** just a prefix of the key.  The number of fields is given by
         1828  +** pKeyInfo->nField.
  1827   1829   **
  1828         -** The r1 and r2 member variables are only used by the optimized comparison
  1829         -** functions vdbeRecordCompareInt() and vdbeRecordCompareString().
         1830  +** The r1 and r2 fields are the values to return if this key is less than
         1831  +** or greater than a key in the btree, respectively.  These are normally
         1832  +** -1 and +1 respectively, but might be inverted to +1 and -1 if the b-tree
         1833  +** is in DESC order.
         1834  +**
         1835  +** The key comparison functions actually return default_rc when they find
         1836  +** an equals comparison.  default_rc can be -1, 0, or +1.  If there are
         1837  +** multiple entries in the b-tree with the same key (when only looking
         1838  +** at the first pKeyInfo->nFields,) then default_rc can be set to -1 to 
         1839  +** cause the search to find the last match, or +1 to cause the search to
         1840  +** find the first match.
         1841  +**
         1842  +** The key comparison functions will set eqSeen to true if they ever
         1843  +** get and equal results when comparing this structure to a b-tree record.
         1844  +** When default_rc!=0, the search might end up on the record immediately
         1845  +** before the first match or immediately after the last match.  The
         1846  +** eqSeen field will indicate whether or not an exact match exists in the
         1847  +** b-tree.
  1830   1848   */
  1831   1849   struct UnpackedRecord {
  1832   1850     KeyInfo *pKeyInfo;  /* Collation and sort-order information */
         1851  +  Mem *aMem;          /* Values */
  1833   1852     u16 nField;         /* Number of entries in apMem[] */
  1834   1853     i8 default_rc;      /* Comparison result if keys are equal */
  1835   1854     u8 errCode;         /* Error detected by xRecordCompare (CORRUPT or NOMEM) */
  1836         -  Mem *aMem;          /* Values */
  1837         -  int r1;             /* Value to return if (lhs > rhs) */
  1838         -  int r2;             /* Value to return if (rhs < lhs) */
         1855  +  i8 r1;              /* Value to return if (lhs > rhs) */
         1856  +  i8 r2;              /* Value to return if (rhs < lhs) */
         1857  +  u8 eqSeen;          /* True if an equality comparison has been seen */
  1839   1858   };
  1840   1859   
  1841   1860   
  1842   1861   /*
  1843   1862   ** Each SQL index is represented in memory by an
  1844   1863   ** instance of the following structure.
  1845   1864   **

Changes to src/vdbe.c.

  3592   3592   ** use the value in register P3 as the key.  If cursor P1 refers 
  3593   3593   ** to an SQL index, then P3 is the first in an array of P4 registers 
  3594   3594   ** that are used as an unpacked index key. 
  3595   3595   **
  3596   3596   ** Reposition cursor P1 so that  it points to the smallest entry that 
  3597   3597   ** is greater than or equal to the key value. If there are no records 
  3598   3598   ** greater than or equal to the key and P2 is not zero, then jump to P2.
         3599  +**
         3600  +** If the cursor P1 was opened using the OPFLAG_SEEKEQ flag, then this
         3601  +** opcode will always land on a record that equally equals the key, or
         3602  +** else jump immediately to P2.  When the cursor is OPFLAG_SEEKEQ, this
         3603  +** opcode must be followed by an IdxLE opcode with the same arguments.
         3604  +** The IdxLE opcode will be skipped if this opcode succeeds, but the
         3605  +** IdxLE opcode will be used on subsequent loop iterations.
  3599   3606   **
  3600   3607   ** This opcode leaves the cursor configured to move in forward order,
  3601   3608   ** from the beginning toward the end.  In other words, the cursor is
  3602   3609   ** configured to use Next, not Prev.
  3603   3610   **
  3604   3611   ** See also: Found, NotFound, SeekLt, SeekGt, SeekLe
  3605   3612   */
................................................................................
  3651   3658   ** is less than or equal to the key value. If there are no records 
  3652   3659   ** less than or equal to the key and P2 is not zero, then jump to P2.
  3653   3660   **
  3654   3661   ** This opcode leaves the cursor configured to move in reverse order,
  3655   3662   ** from the end toward the beginning.  In other words, the cursor is
  3656   3663   ** configured to use Prev, not Next.
  3657   3664   **
         3665  +** If the cursor P1 was opened using the OPFLAG_SEEKEQ flag, then this
         3666  +** opcode will always land on a record that equally equals the key, or
         3667  +** else jump immediately to P2.  When the cursor is OPFLAG_SEEKEQ, this
         3668  +** opcode must be followed by an IdxGE opcode with the same arguments.
         3669  +** The IdxGE opcode will be skipped if this opcode succeeds, but the
         3670  +** IdxGE opcode will be used on subsequent loop iterations.
         3671  +**
  3658   3672   ** See also: Found, NotFound, SeekGt, SeekGe, SeekLt
  3659   3673   */
  3660   3674   case OP_SeekLT:         /* jump, in3 */
  3661   3675   case OP_SeekLE:         /* jump, in3 */
  3662   3676   case OP_SeekGE:         /* jump, in3 */
  3663   3677   case OP_SeekGT: {       /* jump, in3 */
  3664         -  int res;
  3665         -  int oc;
  3666         -  VdbeCursor *pC;
  3667         -  UnpackedRecord r;
  3668         -  int nField;
  3669         -  i64 iKey;      /* The rowid we are to seek to */
         3678  +  int res;           /* Comparison result */
         3679  +  int oc;            /* Opcode */
         3680  +  VdbeCursor *pC;    /* The cursor to seek */
         3681  +  UnpackedRecord r;  /* The key to seek for */
         3682  +  int nField;        /* Number of columns or fields in the key */
         3683  +  i64 iKey;          /* The rowid we are to seek to */
         3684  +  int eqOnly = 0;    /* Only interested in == results */
  3670   3685   
  3671   3686     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  3672   3687     assert( pOp->p2!=0 );
  3673   3688     pC = p->apCsr[pOp->p1];
  3674   3689     assert( pC!=0 );
  3675   3690     assert( pC->pseudoTableReg==0 );
  3676   3691     assert( OP_SeekLE == OP_SeekLT+1 );
................................................................................
  3684   3699     pC->seekOp = pOp->opcode;
  3685   3700   #endif
  3686   3701   
  3687   3702     /* For a cursor with the BTREE_SEEK_EQ hint, only the OP_SeekGE and
  3688   3703     ** OP_SeekLE opcodes are allowed, and these must be immediately followed
  3689   3704     ** by an OP_IdxGT or OP_IdxLT opcode, respectively, with the same key.
  3690   3705     */
  3691         -#ifdef SQLITE_DEBUG
  3692   3706     if( sqlite3BtreeCursorHasHint(pC->pCursor, BTREE_SEEK_EQ) ){
         3707  +    eqOnly = 1;
  3693   3708       assert( pOp->opcode==OP_SeekGE || pOp->opcode==OP_SeekLE );
  3694   3709       assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT );
  3695   3710       assert( pOp[1].p1==pOp[0].p1 );
  3696   3711       assert( pOp[1].p2==pOp[0].p2 );
  3697   3712       assert( pOp[1].p3==pOp[0].p3 );
  3698   3713       assert( pOp[1].p4.i==pOp[0].p4.i );
  3699   3714     }
  3700         -#endif
  3701   3715    
  3702   3716     if( pC->isTable ){
  3703   3717       /* The input value in P3 might be of any type: integer, real, string,
  3704   3718       ** blob, or NULL.  But it needs to be an integer before we can do
  3705   3719       ** the seek, so convert it. */
  3706   3720       pIn3 = &aMem[pOp->p3];
  3707   3721       if( (pIn3->flags & (MEM_Int|MEM_Real|MEM_Str))==MEM_Str ){
................................................................................
  3743   3757         }
  3744   3758       } 
  3745   3759       rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, 0, (u64)iKey, 0, &res);
  3746   3760       pC->movetoTarget = iKey;  /* Used by OP_Delete */
  3747   3761       if( rc!=SQLITE_OK ){
  3748   3762         goto abort_due_to_error;
  3749   3763       }
         3764  +    if( eqOnly && res ) goto seek_not_found;
  3750   3765     }else{
  3751   3766       nField = pOp->p4.i;
  3752   3767       assert( pOp->p4type==P4_INT32 );
  3753   3768       assert( nField>0 );
  3754   3769       r.pKeyInfo = pC->pKeyInfo;
  3755   3770       r.nField = (u16)nField;
  3756   3771   
................................................................................
  3768   3783       assert( oc!=OP_SeekLT || r.default_rc==+1 );
  3769   3784   
  3770   3785       r.aMem = &aMem[pOp->p3];
  3771   3786   #ifdef SQLITE_DEBUG
  3772   3787       { int i; for(i=0; i<r.nField; i++) assert( memIsValid(&r.aMem[i]) ); }
  3773   3788   #endif
  3774   3789       ExpandBlob(r.aMem);
         3790  +    r.eqSeen = 0;
  3775   3791       rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, &r, 0, 0, &res);
  3776   3792       if( rc!=SQLITE_OK ){
  3777   3793         goto abort_due_to_error;
         3794  +    }
         3795  +    if( eqOnly && r.eqSeen==0 ){
         3796  +      assert( res!=0 );
         3797  +      goto seek_not_found;
  3778   3798       }
  3779   3799     }
  3780   3800     pC->deferredMoveto = 0;
  3781   3801     pC->cacheStatus = CACHE_STALE;
  3782   3802   #ifdef SQLITE_TEST
  3783   3803     sqlite3_search_count++;
  3784   3804   #endif
................................................................................
  3799   3819       }else{
  3800   3820         /* res might be negative because the table is empty.  Check to
  3801   3821         ** see if this is the case.
  3802   3822         */
  3803   3823         res = sqlite3BtreeEof(pC->pCursor);
  3804   3824       }
  3805   3825     }
         3826  +seek_not_found:
  3806   3827     assert( pOp->p2>0 );
  3807   3828     VdbeBranchTaken(res!=0,2);
  3808   3829     if( res ){
  3809   3830       goto jump_to_p2;
         3831  +  }else if( eqOnly ){
         3832  +    assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT );
         3833  +    pOp++; /* Skip the OP_IdxLt or OP_IdxGT that follows */
  3810   3834     }
  3811   3835     break;
  3812   3836   }
  3813   3837   
  3814   3838   /* Opcode: Seek P1 P2 * * *
  3815   3839   ** Synopsis:  intkey=r[P2]
  3816   3840   **

Changes to src/vdbeaux.c.

  3989   3989     /* rc==0 here means that one or both of the keys ran out of fields and
  3990   3990     ** all the fields up to that point were equal. Return the default_rc
  3991   3991     ** value.  */
  3992   3992     assert( CORRUPT_DB 
  3993   3993          || vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, pPKey2->default_rc) 
  3994   3994          || pKeyInfo->db->mallocFailed
  3995   3995     );
         3996  +  pPKey2->eqSeen = 1;
  3996   3997     return pPKey2->default_rc;
  3997   3998   }
  3998   3999   int sqlite3VdbeRecordCompare(
  3999   4000     int nKey1, const void *pKey1,   /* Left key */
  4000   4001     UnpackedRecord *pPKey2          /* Right key */
  4001   4002   ){
  4002   4003     return sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 0);
................................................................................
  4088   4089       /* The first fields of the two keys are equal. Compare the trailing 
  4089   4090       ** fields.  */
  4090   4091       res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1);
  4091   4092     }else{
  4092   4093       /* The first fields of the two keys are equal and there are no trailing
  4093   4094       ** fields. Return pPKey2->default_rc in this case. */
  4094   4095       res = pPKey2->default_rc;
         4096  +    pPKey2->eqSeen = 1;
  4095   4097     }
  4096   4098   
  4097   4099     assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, res) );
  4098   4100     return res;
  4099   4101   }
  4100   4102   
  4101   4103   /*
................................................................................
  4135   4137       if( res==0 ){
  4136   4138         res = nStr - pPKey2->aMem[0].n;
  4137   4139         if( res==0 ){
  4138   4140           if( pPKey2->nField>1 ){
  4139   4141             res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1);
  4140   4142           }else{
  4141   4143             res = pPKey2->default_rc;
         4144  +          pPKey2->eqSeen = 1;
  4142   4145           }
  4143   4146         }else if( res>0 ){
  4144   4147           res = pPKey2->r2;
  4145   4148         }else{
  4146   4149           res = pPKey2->r1;
  4147   4150         }
  4148   4151       }else if( res>0 ){

Changes to test/collate4.test.

   348    348   do_test collate4-2.1.2 {
   349    349     execsql {
   350    350       CREATE INDEX collate4i1 ON collate4t1(a);
   351    351     }
   352    352     count {
   353    353       SELECT * FROM collate4t2, collate4t1 WHERE a = b;
   354    354     }
   355         -} {A a A A 5}
          355  +} {A a A A 4}
   356    356   do_test collate4-2.1.3 {
   357    357     count {
   358    358       SELECT * FROM collate4t2, collate4t1 WHERE b = a;
   359    359     }
   360    360   } {A A 19}
   361    361   do_test collate4-2.1.4 {
   362    362     execsql {
................................................................................
   368    368        ORDER BY collate4t2.rowid, collate4t1.rowid
   369    369     }
   370    370   } {A a A A 19}
   371    371   do_test collate4-2.1.5 {
   372    372     count {
   373    373       SELECT * FROM collate4t2, collate4t1 WHERE b = a;
   374    374     }
   375         -} {A A 4}
          375  +} {A A 3}
   376    376   ifcapable subquery {
   377    377     do_test collate4-2.1.6 {
   378    378       count {
   379    379         SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
   380    380          ORDER BY rowid
   381    381       }
   382    382     } {a A 10}
................................................................................
   385    385         DROP INDEX collate4i1;
   386    386         CREATE INDEX collate4i1 ON collate4t1(a);
   387    387       }
   388    388       count {
   389    389         SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
   390    390          ORDER BY rowid
   391    391       }
   392         -  } {a A 6}
          392  +  } {a A 5}
   393    393     do_test collate4-2.1.8 {
   394    394       count {
   395    395         SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
   396    396       }
   397         -  } {a A 5}
          397  +  } {a A 4}
   398    398     do_test collate4-2.1.9 {
   399    399       execsql {
   400    400         DROP INDEX collate4i1;
   401    401         CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
   402    402       }
   403    403       count {
   404    404         SELECT a FROM collate4t1 WHERE a IN ('z', 'a') ORDER BY rowid;

Changes to test/where.test.

   408    408         SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
   409    409       }
   410    410     } {1 0 4 2 1 9 3 1 16 102}
   411    411     do_test where-5.3a {
   412    412       count {
   413    413         SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
   414    414       }
   415         -  } {1 0 4 2 1 9 3 1 16 13}
          415  +  } {1 0 4 2 1 9 3 1 16 12}
   416    416     do_test where-5.3b {
   417    417       count {
   418    418         SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1;
   419    419       }
   420         -  } {1 0 4 2 1 9 3 1 16 13}
          420  +  } {1 0 4 2 1 9 3 1 16 12}
   421    421     do_test where-5.3c {
   422    422       count {
   423    423         SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1;
   424    424       }
   425         -  } {1 0 4 2 1 9 3 1 16 13}
          425  +  } {1 0 4 2 1 9 3 1 16 12}
   426    426     do_test where-5.3d {
   427    427       count {
   428    428         SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC;
   429    429       }
   430         -  } {3 1 16 2 1 9 1 0 4 12}
          430  +  } {3 1 16 2 1 9 1 0 4 11}
   431    431     do_test where-5.4 {
   432    432       count {
   433    433         SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
   434    434       }
   435    435     } {1 0 4 2 1 9 3 1 16 102}
   436    436     do_test where-5.5 {
   437    437       count {
................................................................................
   461    461         ORDER BY 1;
   462    462       }
   463    463     } {2 1 9 4 2 25 103}
   464    464     do_test where-5.9 {
   465    465       count {
   466    466         SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
   467    467       }
   468         -  } {2 1 9 3 1 16 7}
          468  +  } {2 1 9 3 1 16 6}
   469    469     do_test where-5.10 {
   470    470       count {
   471    471         SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
   472    472       }
   473    473     } {2 1 9 3 1 16 199}
   474    474     do_test where-5.11 {
   475    475       count {
................................................................................
   481    481         SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
   482    482       }
   483    483     } {79 6 6400 89 6 8100 7}
   484    484     do_test where-5.13 {
   485    485       count {
   486    486         SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
   487    487       }
   488         -  } {2 1 9 3 1 16 7}
          488  +  } {2 1 9 3 1 16 6}
   489    489     do_test where-5.14 {
   490    490       count {
   491    491         SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
   492    492       }
   493         -  } {2 1 9 8}
          493  +  } {2 1 9 5}
   494    494     do_test where-5.15 {
   495    495       count {
   496    496         SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
   497    497       }
   498         -  } {2 1 9 3 1 16 11}
          498  +  } {2 1 9 3 1 16 9}
   499    499     do_test where-5.100 {
   500    500       db eval {
   501    501         SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969)
   502    502          ORDER BY x, y
   503    503       }
   504    504     } {2 1 9 54 5 3025 62 5 3969}
   505    505     do_test where-5.101 {

Changes to test/where4.test.

    87     87     count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'}
    88     88   } {4 2}
    89     89   do_test where4-1.10 {
    90     90     count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL}
    91     91   } {6 2}
    92     92   do_test where4-1.11 {
    93     93     count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123}
    94         -} {1}
           94  +} {0}
    95     95   do_test where4-1.12 {
    96     96     count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=x'7A'}
    97     97   } {6 2}
    98     98   do_test where4-1.13 {
    99     99     count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL}
   100    100   } {7 2}
   101    101   do_test where4-1.14 {