SQLite

Check-in [468e730036]
Login

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

Overview
Comment:Improved detection and handling of NULL values on the RHS of a IN operator.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 468e730036edac22cfeb9ea3515aa16e6bcd6650
User & Date: drh 2014-08-01 21:00:53.855
Context
2014-08-01
21:12
A better comment on the generated code for the NULL-in-RHS-of-IN detection logic. (check-in: 9bc1c730a3 user: drh tags: trunk)
21:00
Improved detection and handling of NULL values on the RHS of a IN operator. (check-in: 468e730036 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: c11e55fabb user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
1470
1471
1472
1473
1474
1475
1476
















1477
1478
1479
1480
1481
1482
1483
** Code an OP_Once instruction and allocate space for its flag. Return the 
** address of the new instruction.
*/
int sqlite3CodeOnce(Parse *pParse){
  Vdbe *v = sqlite3GetVdbe(pParse);      /* Virtual machine being coded */
  return sqlite3VdbeAddOp1(v, OP_Once, pParse->nOnce++);
}

















/*
** This function is used by the implementation of the IN (...) operator.
** The pX parameter is the expression on the RHS of the IN operator, which
** might be either a list of expressions or a subquery.
**
** The job of this routine is to find or create a b-tree object that can







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







1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
** Code an OP_Once instruction and allocate space for its flag. Return the 
** address of the new instruction.
*/
int sqlite3CodeOnce(Parse *pParse){
  Vdbe *v = sqlite3GetVdbe(pParse);      /* Virtual machine being coded */
  return sqlite3VdbeAddOp1(v, OP_Once, pParse->nOnce++);
}

/*
** Generate code that checks the single-column index table iCur to see if
** contains any NULL entries.  Cause the register at regHasNull to be set
** to a non-NULL value if iCur contains no NULLs.  Cause register regHasNull
** to be set to NULL if iCur contains one or more NULL values.
*/
static void sqlite3SetHasNullFlag(Vdbe *v, int iCur, int regHasNull){
  int j1;
  sqlite3VdbeAddOp2(v, OP_Integer, 0, regHasNull);
  j1 = sqlite3VdbeAddOp1(v, OP_Rewind, iCur); VdbeCoverage(v);
  sqlite3VdbeAddOp3(v, OP_Column, iCur, 0, regHasNull);
  sqlite3VdbeChangeP5(v, OPFLAG_TYPEOFARG);
  VdbeComment((v, "<maybe-NULL?>"));
  sqlite3VdbeJumpHere(v, j1);
}

/*
** This function is used by the implementation of the IN (...) operator.
** The pX parameter is the expression on the RHS of the IN operator, which
** might be either a list of expressions or a subquery.
**
** The job of this routine is to find or create a b-tree object that can
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
** contains a NULL.  If prRhsHasNull is not a NULL pointer and 
** if there is any chance that the (...) might contain a NULL value at
** runtime, then a register is allocated and the register number written
** to *prRhsHasNull. If there is no chance that the (...) contains a
** NULL value, then *prRhsHasNull is left unchanged.
**
** If a register is allocated and its location stored in *prRhsHasNull, then
** the value in that register will be:
**
**          0      if the (...) contains no NULL values
**          1      if the (...) does not contain NULL values
**       NULL      if we do not yet know if (...) contains NULLs
**
** If the (...) does not remain constant for the duration of the query
** (i.e. the SELECT within the (...) is a correlated subquery) then the
** value of the allocated register is reset to NULL each time the subquery
** is rerun. This allows the caller to use vdbe code equivalent to the
** following:
**
**   if( r[*prRhsHasNull] IS NULL ){
**     r[*prRhsHasNull] = <test if data structure contains null>
**   }
**
** in order to avoid running the <test if data structure contains null>
** test more often than is necessary.
*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3FindInIndex(Parse *pParse, Expr *pX, u32 inFlags, int *prRhsHasNull){
  Select *p;                            /* SELECT to the right of IN operator */
  int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
  int iTab = pParse->nTab++;            /* Cursor of the RHS table */
  int mustBeUnique;                     /* True if RHS must be unique */







|
<
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<







1544
1545
1546
1547
1548
1549
1550
1551

1552
1553














1554
1555
1556
1557
1558
1559
1560
** contains a NULL.  If prRhsHasNull is not a NULL pointer and 
** if there is any chance that the (...) might contain a NULL value at
** runtime, then a register is allocated and the register number written
** to *prRhsHasNull. If there is no chance that the (...) contains a
** NULL value, then *prRhsHasNull is left unchanged.
**
** If a register is allocated and its location stored in *prRhsHasNull, then
** the value in that register will be NULL if the b-tree contains one or more

** NULL values, and it will be some non-NULL value if the b-tree contains no
** NULL values.














*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3FindInIndex(Parse *pParse, Expr *pX, u32 inFlags, int *prRhsHasNull){
  Select *p;                            /* SELECT to the right of IN operator */
  int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
  int iTab = pParse->nTab++;            /* Cursor of the RHS table */
  int mustBeUnique;                     /* True if RHS must be unique */
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
          sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
          VdbeComment((v, "%s", pIdx->zName));
          assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );
          eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];

          if( prRhsHasNull && !pTab->aCol[iCol].notNull ){
            *prRhsHasNull = ++pParse->nMem;
            sqlite3VdbeAddOp2(v, OP_Null, 0, *prRhsHasNull);
          }
          sqlite3VdbeJumpHere(v, iAddr);
        }
      }
    }
  }








|







1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
          sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
          VdbeComment((v, "%s", pIdx->zName));
          assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );
          eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];

          if( prRhsHasNull && !pTab->aCol[iCol].notNull ){
            *prRhsHasNull = ++pParse->nMem;
            sqlite3SetHasNullFlag(v, iTab, *prRhsHasNull);
          }
          sqlite3VdbeJumpHere(v, iAddr);
        }
      }
    }
  }

1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
** For a SELECT or EXISTS operator, return the register that holds the
** result.  For IN operators or if an error occurs, the return value is 0.
*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3CodeSubselect(
  Parse *pParse,          /* Parsing context */
  Expr *pExpr,            /* The IN, SELECT, or EXISTS operator */
  int rMayHaveNull,       /* Register that records whether NULLs exist in RHS */
  int isRowid             /* If true, LHS of IN operator is a rowid */
){
  int testAddr = -1;                      /* One-time test address */
  int rReg = 0;                           /* Register storing resulting */
  Vdbe *v = sqlite3GetVdbe(pParse);
  if( NEVER(v==0) ) return 0;
  sqlite3ExprCachePush(pParse);

  /* This code must be run in its entirety every time it is encountered
  ** if any of the following is true:
  **
  **    *  The right-hand side is a correlated subquery
  **    *  The right-hand side is an expression list containing variables
  **    *  We are inside a trigger
  **
  ** If all of the above are false, then we can run this code just once
  ** save the results, and reuse the same result on subsequent invocations.
  */
  if( !ExprHasProperty(pExpr, EP_VarSelect) ){
    testAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
  }

#ifndef SQLITE_OMIT_EXPLAIN
  if( pParse->explain==2 ){
    char *zMsg = sqlite3MPrintf(
        pParse->db, "EXECUTE %s%s SUBQUERY %d", testAddr>=0?"":"CORRELATED ",
        pExpr->op==TK_IN?"LIST":"SCALAR", pParse->iNextSelectId
    );
    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
  }
#endif

  switch( pExpr->op ){
    case TK_IN: {
      char affinity;              /* Affinity of the LHS of the IN */
      int addr;                   /* Address of OP_OpenEphemeral instruction */
      Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */
      KeyInfo *pKeyInfo = 0;      /* Key information */

      if( rMayHaveNull ){
        sqlite3VdbeAddOp2(v, OP_Null, 0, rMayHaveNull);
      }

      affinity = sqlite3ExprAffinity(pLeft);

      /* Whether this is an 'x IN(SELECT...)' or an 'x IN(<exprlist>)'
      ** expression it is handled the same way.  An ephemeral table is 
      ** filled with single-field index keys representing the results
      ** from the SELECT or the <exprlist>.
      **







|


|
















|





|













<
<
<
<







1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734




1735
1736
1737
1738
1739
1740
1741
** For a SELECT or EXISTS operator, return the register that holds the
** result.  For IN operators or if an error occurs, the return value is 0.
*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3CodeSubselect(
  Parse *pParse,          /* Parsing context */
  Expr *pExpr,            /* The IN, SELECT, or EXISTS operator */
  int rHasNullFlag,       /* Register that records whether NULLs exist in RHS */
  int isRowid             /* If true, LHS of IN operator is a rowid */
){
  int jmpIfDynamic = -1;                      /* One-time test address */
  int rReg = 0;                           /* Register storing resulting */
  Vdbe *v = sqlite3GetVdbe(pParse);
  if( NEVER(v==0) ) return 0;
  sqlite3ExprCachePush(pParse);

  /* This code must be run in its entirety every time it is encountered
  ** if any of the following is true:
  **
  **    *  The right-hand side is a correlated subquery
  **    *  The right-hand side is an expression list containing variables
  **    *  We are inside a trigger
  **
  ** If all of the above are false, then we can run this code just once
  ** save the results, and reuse the same result on subsequent invocations.
  */
  if( !ExprHasProperty(pExpr, EP_VarSelect) ){
    jmpIfDynamic = sqlite3CodeOnce(pParse); VdbeCoverage(v);
  }

#ifndef SQLITE_OMIT_EXPLAIN
  if( pParse->explain==2 ){
    char *zMsg = sqlite3MPrintf(
        pParse->db, "EXECUTE %s%s SUBQUERY %d", jmpIfDynamic>=0?"":"CORRELATED ",
        pExpr->op==TK_IN?"LIST":"SCALAR", pParse->iNextSelectId
    );
    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
  }
#endif

  switch( pExpr->op ){
    case TK_IN: {
      char affinity;              /* Affinity of the LHS of the IN */
      int addr;                   /* Address of OP_OpenEphemeral instruction */
      Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */
      KeyInfo *pKeyInfo = 0;      /* Key information */





      affinity = sqlite3ExprAffinity(pLeft);

      /* Whether this is an 'x IN(SELECT...)' or an 'x IN(<exprlist>)'
      ** expression it is handled the same way.  An ephemeral table is 
      ** filled with single-field index keys representing the results
      ** from the SELECT or the <exprlist>.
      **
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
          int iValToIns;

          /* If the expression is not constant then we will need to
          ** disable the test that was generated above that makes sure
          ** this code only executes once.  Because for a non-constant
          ** expression we need to rerun this code each time.
          */
          if( testAddr>=0 && !sqlite3ExprIsConstant(pE2) ){
            sqlite3VdbeChangeToNoop(v, testAddr);
            testAddr = -1;
          }

          /* Evaluate the expression and insert it into the temp table */
          if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){
            sqlite3VdbeAddOp3(v, OP_InsertInt, pExpr->iTable, r2, iValToIns);
          }else{
            r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);







|
|
|







1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
          int iValToIns;

          /* If the expression is not constant then we will need to
          ** disable the test that was generated above that makes sure
          ** this code only executes once.  Because for a non-constant
          ** expression we need to rerun this code each time.
          */
          if( jmpIfDynamic>=0 && !sqlite3ExprIsConstant(pE2) ){
            sqlite3VdbeChangeToNoop(v, jmpIfDynamic);
            jmpIfDynamic = -1;
          }

          /* Evaluate the expression and insert it into the temp table */
          if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){
            sqlite3VdbeAddOp3(v, OP_InsertInt, pExpr->iTable, r2, iValToIns);
          }else{
            r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
1885
1886
1887
1888
1889
1890
1891



1892

1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
      }
      rReg = dest.iSDParm;
      ExprSetVVAProperty(pExpr, EP_NoReduce);
      break;
    }
  }




  if( testAddr>=0 ){

    sqlite3VdbeJumpHere(v, testAddr);
  }
  sqlite3ExprCachePop(pParse);

  return rReg;
}
#endif /* SQLITE_OMIT_SUBQUERY */

#ifndef SQLITE_OMIT_SUBQUERY
/*
** Generate code for an IN expression.
**
**      x IN (SELECT ...)
**      x IN (value, value, ...)
**
** The left-hand side (LHS) is a scalar expression.  The right-hand side (RHS)
** is an array of zero or more values.  The expression is true if the LHS is
** contained within the RHS.  The value of the expression is unknown (NULL)
** if the LHS is NULL or if the LHS is not contained within the RHS and the
** RHS contains one or more NULL values.
**
** This routine generates code will jump to destIfFalse if the LHS is not 
** contained within the RHS.  If due to NULLs we cannot determine if the LHS
** is contained in the RHS then jump to destIfNull.  If the LHS is contained
** within the RHS then fall through.
*/
static void sqlite3ExprCodeIN(
  Parse *pParse,        /* Parsing and code generating context */
  Expr *pExpr,          /* The IN expression */







>
>
>
|
>
|




















|







1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
      }
      rReg = dest.iSDParm;
      ExprSetVVAProperty(pExpr, EP_NoReduce);
      break;
    }
  }

  if( rHasNullFlag ){
    sqlite3SetHasNullFlag(v, pExpr->iTable, rHasNullFlag);
  }

  if( jmpIfDynamic>=0 ){
    sqlite3VdbeJumpHere(v, jmpIfDynamic);
  }
  sqlite3ExprCachePop(pParse);

  return rReg;
}
#endif /* SQLITE_OMIT_SUBQUERY */

#ifndef SQLITE_OMIT_SUBQUERY
/*
** Generate code for an IN expression.
**
**      x IN (SELECT ...)
**      x IN (value, value, ...)
**
** The left-hand side (LHS) is a scalar expression.  The right-hand side (RHS)
** is an array of zero or more values.  The expression is true if the LHS is
** contained within the RHS.  The value of the expression is unknown (NULL)
** if the LHS is NULL or if the LHS is not contained within the RHS and the
** RHS contains one or more NULL values.
**
** This routine generates code that jumps to destIfFalse if the LHS is not 
** contained within the RHS.  If due to NULLs we cannot determine if the LHS
** is contained in the RHS then jump to destIfNull.  If the LHS is contained
** within the RHS then fall through.
*/
static void sqlite3ExprCodeIN(
  Parse *pParse,        /* Parsing and code generating context */
  Expr *pExpr,          /* The IN expression */
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003


2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
      sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse, r1, 1);
      VdbeCoverage(v);
    }else{
      /* In this branch, the RHS of the IN might contain a NULL and
      ** the presence of a NULL on the RHS makes a difference in the
      ** outcome.
      */
      int j1, j2;

      /* First check to see if the LHS is contained in the RHS.  If so,
      ** then the presence of NULLs in the RHS does not matter, so jump


      ** over all of the code that follows.
      */
      j1 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, r1, 1);
      VdbeCoverage(v);

      /* Here we begin generating code that runs if the LHS is not
      ** contained within the RHS.  Generate additional code that
      ** tests the RHS for NULLs.  If the RHS contains a NULL then
      ** jump to destIfNull.  If there are no NULLs in the RHS then
      ** jump to destIfFalse.
      */
      sqlite3VdbeAddOp2(v, OP_If, rRhsHasNull, destIfNull); VdbeCoverage(v);
      sqlite3VdbeAddOp2(v, OP_IfNot, rRhsHasNull, destIfFalse); VdbeCoverage(v);
      j2 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, rRhsHasNull, 1);
      VdbeCoverage(v);
      sqlite3VdbeAddOp2(v, OP_Integer, 0, rRhsHasNull);
      sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse);
      sqlite3VdbeJumpHere(v, j2);
      sqlite3VdbeAddOp2(v, OP_Integer, 1, rRhsHasNull);
      sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfNull);

      /* The OP_Found at the top of this branch jumps here when true, 
      ** causing the overall IN expression evaluation to fall through.
      */
      sqlite3VdbeJumpHere(v, j1);
    }
  }
  sqlite3ReleaseTempReg(pParse, r1);
  sqlite3ExprCachePop(pParse);
  VdbeComment((v, "end IN expr"));
}







|


|
>
>
|



<
<
<
<
<
<
<
|
<
<

<

<
<
<
<
<
<
<







1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010







2011


2012

2013







2014
2015
2016
2017
2018
2019
2020
      sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse, r1, 1);
      VdbeCoverage(v);
    }else{
      /* In this branch, the RHS of the IN might contain a NULL and
      ** the presence of a NULL on the RHS makes a difference in the
      ** outcome.
      */
      int j1;

      /* First check to see if the LHS is contained in the RHS.  If so,
      ** then the answer is TRUE the presence of NULLs in the RHS does
      ** not matter.  If the LHS is not contained in the RHS, then the
      ** answer is NULL if the RHS contains NULLs and the answer is
      ** FALSE if the RHS is NULL-free.
      */
      j1 = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0, r1, 1);
      VdbeCoverage(v);







      sqlite3VdbeAddOp2(v, OP_IsNull, rRhsHasNull, destIfNull);


      VdbeCoverage(v);

      sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse);







      sqlite3VdbeJumpHere(v, j1);
    }
  }
  sqlite3ReleaseTempReg(pParse, r1);
  sqlite3ExprCachePop(pParse);
  VdbeComment((v, "end IN expr"));
}