/ Check-in [3b30b75b]
Login

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

Overview
Comment:Fix an adverse interaction between the IS NOT NULL optimization (available only with SQLITE_ENABLE_STAT3) and the transitive constraint processing. Fix for ticket [d805526eae253]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3b30b75b342bb6b424ad2bf7cd841b2c88bdad44
User & Date: drh 2013-07-08 21:12:57
Context
2013-07-08
22:33
Make sure an adequate number of digits are shown for binary-to-text rendering of very small floating point values. check-in: 776e65f9 user: drh tags: trunk
21:12
Fix an adverse interaction between the IS NOT NULL optimization (available only with SQLITE_ENABLE_STAT3) and the transitive constraint processing. Fix for ticket [d805526eae253] check-in: 3b30b75b user: drh tags: trunk
01:27
Add an optional 5th parameter to the next_char() function that is the collating sequence to use for comparison. check-in: 9415db6e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3331   3331              /* TK_LT */  OP_SeekLt,
  3332   3332              /* TK_GE */  OP_SeekGe
  3333   3333         };
  3334   3334         assert( TK_LE==TK_GT+1 );      /* Make sure the ordering.. */
  3335   3335         assert( TK_LT==TK_GT+2 );      /*  ... of the TK_xx values... */
  3336   3336         assert( TK_GE==TK_GT+3 );      /*  ... is correcct. */
  3337   3337   
         3338  +      assert( (pStart->wtFlags & TERM_VNULL)==0 );
  3338   3339         testcase( pStart->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  3339   3340         pX = pStart->pExpr;
  3340   3341         assert( pX!=0 );
  3341         -      assert( pStart->leftCursor==iCur );
         3342  +      testcase( pStart->leftCursor!=iCur ); /* transitive constraints */
  3342   3343         r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
  3343   3344         sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
  3344   3345         VdbeComment((v, "pk"));
  3345   3346         sqlite3ExprCacheAffinityChange(pParse, r1, 1);
  3346   3347         sqlite3ReleaseTempReg(pParse, rTemp);
  3347   3348         disableTerm(pLevel, pStart);
  3348   3349       }else{
  3349   3350         sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk);
  3350   3351       }
  3351   3352       if( pEnd ){
  3352   3353         Expr *pX;
  3353   3354         pX = pEnd->pExpr;
  3354   3355         assert( pX!=0 );
  3355         -      assert( pEnd->leftCursor==iCur );
         3356  +      assert( (pEnd->wtFlags & TERM_VNULL)==0 );
         3357  +      testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */
  3356   3358         testcase( pEnd->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  3357   3359         memEndValue = ++pParse->nMem;
  3358   3360         sqlite3ExprCode(pParse, pX->pRight, memEndValue);
  3359   3361         if( pX->op==TK_LT || pX->op==TK_GT ){
  3360   3362           testOp = bRev ? OP_Le : OP_Ge;
  3361   3363         }else{
  3362   3364           testOp = bRev ? OP_Lt : OP_Gt;
................................................................................
  4278   4280     saved_prereq = pNew->prereq;
  4279   4281     saved_nOut = pNew->nOut;
  4280   4282     pNew->rSetup = 0;
  4281   4283     rLogSize = estLog(whereCost(pProbe->aiRowEst[0]));
  4282   4284     for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
  4283   4285       int nIn = 0;
  4284   4286       if( pTerm->prereqRight & pNew->maskSelf ) continue;
         4287  +#ifdef SQLITE_ENABLE_STAT3
         4288  +    if( (pTerm->wtFlags & TERM_VNULL)!=0 && pSrc->pTab->aCol[iCol].notNull ){
         4289  +      continue; /* skip IS NOT NULL constraints on a NOT NULL column */
         4290  +    }
         4291  +#endif
  4285   4292       pNew->wsFlags = saved_wsFlags;
  4286   4293       pNew->u.btree.nEq = saved_nEq;
  4287   4294       pNew->nLTerm = saved_nLTerm;
  4288   4295       if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
  4289   4296       pNew->aLTerm[pNew->nLTerm++] = pTerm;
  4290   4297       pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
  4291   4298       pNew->rRun = rLogSize; /* Baseline cost is log2(N).  Adjustments below */

Changes to test/transitive1.test.

    42     42   } {20 20 20}
    43     43   do_execsql_test transitive1-210 {
    44     44     SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
    45     45   } {3 3 3 20 20 20}
    46     46   do_execsql_test transitive1-220 {
    47     47     SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
    48     48   } {20 20 20 100 100 100}
           49  +
           50  +# Test cases for ticket [[d805526eae253103] 2013-07-08
           51  +# "Incorrect join result or assertion fault due to transitive constraints"
           52  +#
           53  +do_execsql_test transitive1-300 {
           54  +  CREATE TABLE t301(w INTEGER PRIMARY KEY, x);
           55  +  CREATE TABLE t302(y INTEGER UNIQUE, z);
           56  +  INSERT INTO t301 VALUES(1,2),(3,4),(5,6);
           57  +  INSERT INTO t302 VALUES(1,3),(3,6),(5,7);
           58  +  SELECT *
           59  +    FROM t301 CROSS JOIN t302
           60  +   WHERE w=y AND y IS NOT NULL
           61  +   ORDER BY +w;
           62  +} {1 2 1 3 3 4 3 6 5 6 5 7}
           63  +do_execsql_test transitive1-301 {
           64  +  SELECT *
           65  +    FROM t301 CROSS JOIN t302
           66  +   WHERE w=y AND y IS NOT NULL
           67  +   ORDER BY w;
           68  +} {1 2 1 3 3 4 3 6 5 6 5 7}
           69  +do_execsql_test transitive1-310 {
           70  +  SELECT *
           71  +    FROM t301 CROSS JOIN t302 ON w=y
           72  +   WHERE y>1
           73  +   ORDER BY +w
           74  +} {3 4 3 6 5 6 5 7}
           75  +do_execsql_test transitive1-311 {
           76  +  SELECT *
           77  +    FROM t301 CROSS JOIN t302 ON w=y
           78  +   WHERE y>1
           79  +   ORDER BY w
           80  +} {3 4 3 6 5 6 5 7}
           81  +do_execsql_test transitive1-312 {
           82  +  SELECT *
           83  +    FROM t301 CROSS JOIN t302 ON w=y
           84  +   WHERE y>1
           85  +   ORDER BY w DESC
           86  +} {5 6 5 7 3 4 3 6}
           87  +do_execsql_test transitive1-320 {
           88  +  SELECT *
           89  +    FROM t301 CROSS JOIN t302 ON w=y
           90  +   WHERE y BETWEEN 2 AND 4;
           91  +} {3 4 3 6}
           92  +do_execsql_test transitive1-331 {
           93  +  SELECT *
           94  +    FROM t301 CROSS JOIN t302 ON w=y
           95  +   WHERE y BETWEEN 1 AND 4
           96  +   ORDER BY w;
           97  +} {1 2 1 3 3 4 3 6}
           98  +do_execsql_test transitive1-332 {
           99  +  SELECT *
          100  +    FROM t301 CROSS JOIN t302 ON w=y
          101  +   WHERE y BETWEEN 1 AND 4
          102  +   ORDER BY w DESC;
          103  +} {3 4 3 6 1 2 1 3}
    49    104   
    50    105   finish_test