SQLite

Check-in [eb40248c]
Login

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

Overview
Comment:Fix the IN-early-out optimization so that it works even for the corner case where the NULL bypass fires before the affinity of the LHS operator has been set. Fix for the problem described in forum post 6a3ec138e9.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: eb40248ce606b792a02e4e0b7dd826a82891c5f4c9793f3ca5d332e593109525
User & Date: drh 2021-04-29 15:49:34
Context
2021-04-29
18:03
Fix the operation of the "-" argument to --load-dbsql in the fuzzcheck program. (check-in: 1f18b3cb user: drh tags: trunk)
15:49
Fix the IN-early-out optimization so that it works even for the corner case where the NULL bypass fires before the affinity of the LHS operator has been set. Fix for the problem described in forum post 6a3ec138e9. (check-in: eb40248c user: drh tags: trunk)
13:58
Enhanced "PRAGMA vdbe_trace=on" output associated with the seekHit flag. (check-in: 68395000 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

5431
5432
5433
5434
5435
5436
5437


5438
5439
5440
5441
5442
5443
5444
      sqlite3VdbeResolveLabel(v, pLevel->addrCont);
    }
    if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
      struct InLoop *pIn;
      int j;
      sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
      for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){


        sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
        if( pIn->eEndLoopOp!=OP_Noop ){
          if( pIn->nPrefix ){
            int bEarlyOut = 
                (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0
                 && (pLoop->wsFlags & WHERE_IN_EARLYOUT)!=0;
            if( pLevel->iLeftJoin ){







>
>







5431
5432
5433
5434
5435
5436
5437
5438
5439
5440
5441
5442
5443
5444
5445
5446
      sqlite3VdbeResolveLabel(v, pLevel->addrCont);
    }
    if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
      struct InLoop *pIn;
      int j;
      sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
      for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
        assert( sqlite3VdbeGetOp(v, pIn->addrInTop+1)->opcode==OP_IsNull
                 || pParse->db->mallocFailed );
        sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
        if( pIn->eEndLoopOp!=OP_Noop ){
          if( pIn->nPrefix ){
            int bEarlyOut = 
                (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0
                 && (pLoop->wsFlags & WHERE_IN_EARLYOUT)!=0;
            if( pLevel->iLeftJoin ){
5455
5456
5457
5458
5459
5460
5461





5462
5463
5464
5465
5466
5467
5468
              VdbeCoverage(v);
            }
            if( bEarlyOut ){
              sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur,
                  sqlite3VdbeCurrentAddr(v)+2,
                  pIn->iBase, pIn->nPrefix);
              VdbeCoverage(v);





            }
          }
          sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
          VdbeCoverage(v);
          VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Prev);
          VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Next);
        }







>
>
>
>
>







5457
5458
5459
5460
5461
5462
5463
5464
5465
5466
5467
5468
5469
5470
5471
5472
5473
5474
5475
              VdbeCoverage(v);
            }
            if( bEarlyOut ){
              sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur,
                  sqlite3VdbeCurrentAddr(v)+2,
                  pIn->iBase, pIn->nPrefix);
              VdbeCoverage(v);
              /* Retarget the OP_IsNull against the left operand of IN so 
              ** it jumps past the OP_IfNoHope.  This is because the
              ** OP_IsNull also bypasses the OP_Affinity opcode that is
              ** required by OP_IfNoHope. */
              sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
            }
          }
          sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
          VdbeCoverage(v);
          VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Prev);
          VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Next);
        }

Changes to test/in6.test.

92
93
94
95
96
97
98



















99
100
do_execsql_test in6-3.110 {
  CREATE TABLE v0(v1);
  CREATE TABLE v3(v5, v4);
  INSERT INTO v0 VALUES(0);
  CREATE INDEX v9 ON v3(v4, v4, v5);
  SELECT quote(v5) FROM v0 LEFT JOIN v3 ON v4 = NULL AND v5 IN(0);
} {NULL}




















finish_test







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


92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
do_execsql_test in6-3.110 {
  CREATE TABLE v0(v1);
  CREATE TABLE v3(v5, v4);
  INSERT INTO v0 VALUES(0);
  CREATE INDEX v9 ON v3(v4, v4, v5);
  SELECT quote(v5) FROM v0 LEFT JOIN v3 ON v4 = NULL AND v5 IN(0);
} {NULL}

# 2021-04-29 forum https://sqlite.org/forum/forumpost/6a3ec138e9
# An early OP_IsNull bypass might skip over the OP_Affinity and
# cause the OP_IfNoHope to jump on a false-positive, resulting in
# incomplete output.
#
reset_db
do_execsql_test in6-3.120 {
  CREATE TABLE t1(a TEXT, b TEXT);
  INSERT INTO t1 VALUES(null,10),(0,10),(10,10);
  CREATE INDEX t1ab ON t1(a,b);
  SELECT quote(a), quote(b), '|' FROM t1 WHERE b in (SELECT a FROM t1) AND a=0;
} {'0' '10' |}
do_execsql_test in6-3.130 {
  CREATE TABLE t2(x TEXT);
  INSERT INTO t2(x) VALUES(NULL),(0),(10);
  SELECT quote(x), quote(a), quote(b), 'x'
    FROM t2 LEFT JOIN t1 ON a=x AND b in (null,0,10);
} {NULL NULL NULL x '0' '0' '10' x '10' '10' '10' x}

finish_test