/ Check-in [c11e55fa]
Login

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

Overview
Comment:Remove an unnecessary OP_Null in the IN-operator logic. Attempt to clarify comments explaining the IN-operator code, though it is not clear that the comments are correct even yet - more work to be done.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c11e55fabbc718cb324ecd3540453c25db98f50c
User & Date: drh 2014-08-01 18:00:24
Context
2014-08-01
21:00
Improved detection and handling of NULL values on the RHS of a IN operator. check-in: 468e7300 user: drh tags: trunk
18:00
Remove an unnecessary OP_Null in the IN-operator logic. Attempt to clarify comments explaining the IN-operator code, though it is not clear that the comments are correct even yet - more work to be done. check-in: c11e55fa user: drh tags: trunk
15:51
Clean up the IN operator code generation logic to make it easier to reason about. In the process, improve code generation to omit some unused OP_Null operations. check-in: 7c6fbcfe user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1521   1521   ** When IN_INDEX_MEMBERSHIP is used (and the b-tree will be used 
  1522   1522   ** for fast set membership tests) then an epheremal table must 
  1523   1523   ** be used unless <column> is an INTEGER PRIMARY KEY or an index can 
  1524   1524   ** be found with <column> as its left-most column.
  1525   1525   **
  1526   1526   ** When the b-tree is being used for membership tests, the calling function
  1527   1527   ** might need to know whether or not the RHS side of the IN operator
  1528         -** contains a NULL.  If prNotFound is not NULL and 
         1528  +** contains a NULL.  If prRhsHasNull is not a NULL pointer and 
  1529   1529   ** if there is any chance that the (...) might contain a NULL value at
  1530   1530   ** runtime, then a register is allocated and the register number written
  1531         -** to *prNotFound. If there is no chance that the (...) contains a
  1532         -** NULL value, then *prNotFound is left unchanged.
         1531  +** to *prRhsHasNull. If there is no chance that the (...) contains a
         1532  +** NULL value, then *prRhsHasNull is left unchanged.
  1533   1533   **
  1534         -** If a register is allocated and its location stored in *prNotFound, then
  1535         -** its initial value is NULL.  If the (...) does not remain constant
  1536         -** for the duration of the query (i.e. the SELECT within the (...)
  1537         -** is a correlated subquery) then the value of the allocated register is
  1538         -** reset to NULL each time the subquery is rerun. This allows the
  1539         -** caller to use vdbe code equivalent to the following:
         1534  +** If a register is allocated and its location stored in *prRhsHasNull, then
         1535  +** the value in that register will be:
  1540   1536   **
  1541         -**   if( register==NULL ){
  1542         -**     has_null = <test if data structure contains null>
  1543         -**     register = 1
         1537  +**          0      if the (...) contains no NULL values
         1538  +**          1      if the (...) does not contain NULL values
         1539  +**       NULL      if we do not yet know if (...) contains NULLs
         1540  +**
         1541  +** If the (...) does not remain constant for the duration of the query
         1542  +** (i.e. the SELECT within the (...) is a correlated subquery) then the
         1543  +** value of the allocated register is reset to NULL each time the subquery
         1544  +** is rerun. This allows the caller to use vdbe code equivalent to the
         1545  +** following:
         1546  +**
         1547  +**   if( r[*prRhsHasNull] IS NULL ){
         1548  +**     r[*prRhsHasNull] = <test if data structure contains null>
  1544   1549   **   }
  1545   1550   **
  1546   1551   ** in order to avoid running the <test if data structure contains null>
  1547   1552   ** test more often than is necessary.
  1548   1553   */
  1549   1554   #ifndef SQLITE_OMIT_SUBQUERY
  1550         -int sqlite3FindInIndex(Parse *pParse, Expr *pX, u32 inFlags, int *prNotFound){
         1555  +int sqlite3FindInIndex(Parse *pParse, Expr *pX, u32 inFlags, int *prRhsHasNull){
  1551   1556     Select *p;                            /* SELECT to the right of IN operator */
  1552   1557     int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
  1553   1558     int iTab = pParse->nTab++;            /* Cursor of the RHS table */
  1554   1559     int mustBeUnique;                     /* True if RHS must be unique */
  1555   1560     Vdbe *v = sqlite3GetVdbe(pParse);     /* Virtual machine being coded */
  1556   1561   
  1557   1562     assert( pX->op==TK_IN );
................................................................................
  1617   1622             int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
  1618   1623             sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb);
  1619   1624             sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
  1620   1625             VdbeComment((v, "%s", pIdx->zName));
  1621   1626             assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );
  1622   1627             eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];
  1623   1628   
  1624         -          if( prNotFound && !pTab->aCol[iCol].notNull ){
  1625         -            *prNotFound = ++pParse->nMem;
  1626         -            sqlite3VdbeAddOp2(v, OP_Null, 0, *prNotFound);
         1629  +          if( prRhsHasNull && !pTab->aCol[iCol].notNull ){
         1630  +            *prRhsHasNull = ++pParse->nMem;
         1631  +            sqlite3VdbeAddOp2(v, OP_Null, 0, *prRhsHasNull);
  1627   1632             }
  1628   1633             sqlite3VdbeJumpHere(v, iAddr);
  1629   1634           }
  1630   1635         }
  1631   1636       }
  1632   1637     }
  1633   1638   
................................................................................
  1639   1644       int rMayHaveNull = 0;
  1640   1645       eType = IN_INDEX_EPH;
  1641   1646       if( inFlags & IN_INDEX_LOOP ){
  1642   1647         pParse->nQueryLoop = 0;
  1643   1648         if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){
  1644   1649           eType = IN_INDEX_ROWID;
  1645   1650         }
  1646         -    }else if( prNotFound ){
  1647         -      *prNotFound = rMayHaveNull = ++pParse->nMem;
  1648         -      sqlite3VdbeAddOp2(v, OP_Null, 0, rMayHaveNull);
         1651  +    }else if( prRhsHasNull ){
         1652  +      *prRhsHasNull = rMayHaveNull = ++pParse->nMem;
  1649   1653       }
  1650   1654       sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
  1651   1655       pParse->nQueryLoop = savedNQueryLoop;
  1652   1656     }else{
  1653   1657       pX->iTable = iTab;
  1654   1658     }
  1655   1659     return eType;

Changes to src/vdbe.c.

  2225   2225     break;
  2226   2226   }
  2227   2227   
  2228   2228   /* Opcode: If P1 P2 P3 * *
  2229   2229   **
  2230   2230   ** Jump to P2 if the value in register P1 is true.  The value
  2231   2231   ** is considered true if it is numeric and non-zero.  If the value
  2232         -** in P1 is NULL then take the jump if P3 is non-zero.
         2232  +** in P1 is NULL then take the jump if and only if P3 is non-zero.
  2233   2233   */
  2234   2234   /* Opcode: IfNot P1 P2 P3 * *
  2235   2235   **
  2236   2236   ** Jump to P2 if the value in register P1 is False.  The value
  2237   2237   ** is considered false if it has a numeric value of zero.  If the value
  2238         -** in P1 is NULL then take the jump if P3 is zero.
         2238  +** in P1 is NULL then take the jump if and only if P3 is non-zero.
  2239   2239   */
  2240   2240   case OP_If:                 /* jump, in1 */
  2241   2241   case OP_IfNot: {            /* jump, in1 */
  2242   2242     int c;
  2243   2243     pIn1 = &aMem[pOp->p1];
  2244   2244     if( pIn1->flags & MEM_Null ){
  2245   2245       c = pOp->p3;