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: |
468e730036edac22cfeb9ea3515aa16e |
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
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 | ** 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 | | < | | < < < < < < < < < < < < < < | 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 | 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; | | | 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 | ** 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 */ | | | | | < < < < | 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 | 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. */ | | | | | 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 | } rReg = dest.iSDParm; ExprSetVVAProperty(pExpr, EP_NoReduce); break; } } | > > > | > | | | 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 | 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. */ | | | > > | < < < < < < < | < < < < < < < < < < | 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")); } |
︙ | ︙ |