SQLite

Check-in [3b30b75b34]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3b30b75b342bb6b424ad2bf7cd841b2c88bdad44
User & Date: drh 2013-07-08 21:12:57.346
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: 776e65f98c 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: 3b30b75b34 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: 9415db6ef2 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
3331
3332
3333
3334
3335
3336
3337

3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354

3355
3356
3357
3358
3359
3360
3361
3362
           /* TK_LT */  OP_SeekLt,
           /* TK_GE */  OP_SeekGe
      };
      assert( TK_LE==TK_GT+1 );      /* Make sure the ordering.. */
      assert( TK_LT==TK_GT+2 );      /*  ... of the TK_xx values... */
      assert( TK_GE==TK_GT+3 );      /*  ... is correcct. */


      testcase( pStart->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
      pX = pStart->pExpr;
      assert( pX!=0 );
      assert( pStart->leftCursor==iCur );
      r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
      sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
      VdbeComment((v, "pk"));
      sqlite3ExprCacheAffinityChange(pParse, r1, 1);
      sqlite3ReleaseTempReg(pParse, rTemp);
      disableTerm(pLevel, pStart);
    }else{
      sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk);
    }
    if( pEnd ){
      Expr *pX;
      pX = pEnd->pExpr;
      assert( pX!=0 );

      assert( pEnd->leftCursor==iCur );
      testcase( pEnd->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
      memEndValue = ++pParse->nMem;
      sqlite3ExprCode(pParse, pX->pRight, memEndValue);
      if( pX->op==TK_LT || pX->op==TK_GT ){
        testOp = bRev ? OP_Le : OP_Ge;
      }else{
        testOp = bRev ? OP_Lt : OP_Gt;







>



|













>
|







3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
           /* TK_LT */  OP_SeekLt,
           /* TK_GE */  OP_SeekGe
      };
      assert( TK_LE==TK_GT+1 );      /* Make sure the ordering.. */
      assert( TK_LT==TK_GT+2 );      /*  ... of the TK_xx values... */
      assert( TK_GE==TK_GT+3 );      /*  ... is correcct. */

      assert( (pStart->wtFlags & TERM_VNULL)==0 );
      testcase( pStart->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
      pX = pStart->pExpr;
      assert( pX!=0 );
      testcase( pStart->leftCursor!=iCur ); /* transitive constraints */
      r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
      sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
      VdbeComment((v, "pk"));
      sqlite3ExprCacheAffinityChange(pParse, r1, 1);
      sqlite3ReleaseTempReg(pParse, rTemp);
      disableTerm(pLevel, pStart);
    }else{
      sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk);
    }
    if( pEnd ){
      Expr *pX;
      pX = pEnd->pExpr;
      assert( pX!=0 );
      assert( (pEnd->wtFlags & TERM_VNULL)==0 );
      testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */
      testcase( pEnd->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
      memEndValue = ++pParse->nMem;
      sqlite3ExprCode(pParse, pX->pRight, memEndValue);
      if( pX->op==TK_LT || pX->op==TK_GT ){
        testOp = bRev ? OP_Le : OP_Ge;
      }else{
        testOp = bRev ? OP_Lt : OP_Gt;
4278
4279
4280
4281
4282
4283
4284





4285
4286
4287
4288
4289
4290
4291
  saved_prereq = pNew->prereq;
  saved_nOut = pNew->nOut;
  pNew->rSetup = 0;
  rLogSize = estLog(whereCost(pProbe->aiRowEst[0]));
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    int nIn = 0;
    if( pTerm->prereqRight & pNew->maskSelf ) continue;





    pNew->wsFlags = saved_wsFlags;
    pNew->u.btree.nEq = saved_nEq;
    pNew->nLTerm = saved_nLTerm;
    if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
    pNew->aLTerm[pNew->nLTerm++] = pTerm;
    pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
    pNew->rRun = rLogSize; /* Baseline cost is log2(N).  Adjustments below */







>
>
>
>
>







4280
4281
4282
4283
4284
4285
4286
4287
4288
4289
4290
4291
4292
4293
4294
4295
4296
4297
4298
  saved_prereq = pNew->prereq;
  saved_nOut = pNew->nOut;
  pNew->rSetup = 0;
  rLogSize = estLog(whereCost(pProbe->aiRowEst[0]));
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    int nIn = 0;
    if( pTerm->prereqRight & pNew->maskSelf ) continue;
#ifdef SQLITE_ENABLE_STAT3
    if( (pTerm->wtFlags & TERM_VNULL)!=0 && pSrc->pTab->aCol[iCol].notNull ){
      continue; /* skip IS NOT NULL constraints on a NOT NULL column */
    }
#endif
    pNew->wsFlags = saved_wsFlags;
    pNew->u.btree.nEq = saved_nEq;
    pNew->nLTerm = saved_nLTerm;
    if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
    pNew->aLTerm[pNew->nLTerm++] = pTerm;
    pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
    pNew->rRun = rLogSize; /* Baseline cost is log2(N).  Adjustments below */
Changes to test/transitive1.test.
42
43
44
45
46
47
48























































49
50
} {20 20 20}
do_execsql_test transitive1-210 {
  SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
} {3 3 3 20 20 20}
do_execsql_test transitive1-220 {
  SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
} {20 20 20 100 100 100}
























































finish_test







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


42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
} {20 20 20}
do_execsql_test transitive1-210 {
  SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
} {3 3 3 20 20 20}
do_execsql_test transitive1-220 {
  SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
} {20 20 20 100 100 100}

# Test cases for ticket [[d805526eae253103] 2013-07-08
# "Incorrect join result or assertion fault due to transitive constraints"
#
do_execsql_test transitive1-300 {
  CREATE TABLE t301(w INTEGER PRIMARY KEY, x);
  CREATE TABLE t302(y INTEGER UNIQUE, z);
  INSERT INTO t301 VALUES(1,2),(3,4),(5,6);
  INSERT INTO t302 VALUES(1,3),(3,6),(5,7);
  SELECT *
    FROM t301 CROSS JOIN t302
   WHERE w=y AND y IS NOT NULL
   ORDER BY +w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-301 {
  SELECT *
    FROM t301 CROSS JOIN t302
   WHERE w=y AND y IS NOT NULL
   ORDER BY w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-310 {
  SELECT *
    FROM t301 CROSS JOIN t302 ON w=y
   WHERE y>1
   ORDER BY +w
} {3 4 3 6 5 6 5 7}
do_execsql_test transitive1-311 {
  SELECT *
    FROM t301 CROSS JOIN t302 ON w=y
   WHERE y>1
   ORDER BY w
} {3 4 3 6 5 6 5 7}
do_execsql_test transitive1-312 {
  SELECT *
    FROM t301 CROSS JOIN t302 ON w=y
   WHERE y>1
   ORDER BY w DESC
} {5 6 5 7 3 4 3 6}
do_execsql_test transitive1-320 {
  SELECT *
    FROM t301 CROSS JOIN t302 ON w=y
   WHERE y BETWEEN 2 AND 4;
} {3 4 3 6}
do_execsql_test transitive1-331 {
  SELECT *
    FROM t301 CROSS JOIN t302 ON w=y
   WHERE y BETWEEN 1 AND 4
   ORDER BY w;
} {1 2 1 3 3 4 3 6}
do_execsql_test transitive1-332 {
  SELECT *
    FROM t301 CROSS JOIN t302 ON w=y
   WHERE y BETWEEN 1 AND 4
   ORDER BY w DESC;
} {3 4 3 6 1 2 1 3}

finish_test