/ Check-in [b5f90bfe]
Login

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

Overview
Comment:Back off the optimization of check-in [e130319317e76119] slightly so that it only applies to IN operators that are used for membership tests. Proposed fix for ticket [df46dfb631f75694].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | tkt-df46dfb631
Files: files | file ages | folders
SHA3-256: b5f90bfe6295ab3a22a8e5828133a6ce296dca7c769a3d0688de6ee107ae10d4
User & Date: drh 2019-02-20 03:38:02
Context
2019-02-20
03:38
Back off the optimization of check-in [e130319317e76119] slightly so that it only applies to IN operators that are used for membership tests. Proposed fix for ticket [df46dfb631f75694]. Closed-Leaf check-in: b5f90bfe user: drh tags: tkt-df46dfb631
2019-02-19
20:29
Enhance the xBestIndex method on delta_parse() to return SQLITE_CONSTRAINT if no delta argument is supplied. check-in: f16d127c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2555   2555         if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){
  2556   2556           eType = IN_INDEX_ROWID;
  2557   2557         }
  2558   2558       }else if( prRhsHasNull ){
  2559   2559         *prRhsHasNull = rMayHaveNull = ++pParse->nMem;
  2560   2560       }
  2561   2561       assert( pX->op==TK_IN );
  2562         -    sqlite3CodeRhsOfIN(pParse, pX, iTab, eType==IN_INDEX_ROWID);
         2562  +    sqlite3CodeRhsOfIN(pParse, pX, iTab, eType==IN_INDEX_ROWID, inFlags);
  2563   2563       if( rMayHaveNull ){
  2564   2564         sqlite3SetHasNullFlag(v, iTab, rMayHaveNull);
  2565   2565       }
  2566   2566       pParse->nQueryLoop = savedNQueryLoop;
  2567   2567     }
  2568   2568   
  2569   2569     if( aiMap && eType!=IN_INDEX_INDEX_ASC && eType!=IN_INDEX_INDEX_DESC ){
................................................................................
  2674   2674   ** 'x' nor the SELECT... statement are columns, then numeric affinity
  2675   2675   ** is used.
  2676   2676   */
  2677   2677   void sqlite3CodeRhsOfIN(
  2678   2678     Parse *pParse,          /* Parsing context */
  2679   2679     Expr *pExpr,            /* The IN operator */
  2680   2680     int iTab,               /* Use this cursor number */
  2681         -  int isRowid             /* If true, LHS is a rowid */
         2681  +  int isRowid,            /* If true, LHS is a rowid */
         2682  +  u32 inFlags             /* IN_INDEX_LOOP or IN_INDEX_MEMBERSHIP */
  2682   2683   ){
  2683   2684     int addrOnce = 0;           /* Address of the OP_Once instruction at top */
  2684   2685     int addr;                   /* Address of OP_OpenEphemeral instruction */
  2685   2686     Expr *pLeft;                /* the LHS of the IN operator */
  2686   2687     KeyInfo *pKeyInfo = 0;      /* Key information */
  2687   2688     int nVal;                   /* Size of vector pLeft */
  2688   2689     Vdbe *v;                    /* The prepared statement under construction */
................................................................................
  2692   2693   
  2693   2694     /* The evaluation of the IN must be repeated every time it
  2694   2695     ** is encountered if any of the following is true:
  2695   2696     **
  2696   2697     **    *  The right-hand side is a correlated subquery
  2697   2698     **    *  The right-hand side is an expression list containing variables
  2698   2699     **    *  We are inside a trigger
         2700  +  **    *  The IN operator will be used to drive a loop instead of being
         2701  +  **       used as a simple membership test.
  2699   2702     **
  2700   2703     ** If all of the above are false, then we can compute the RHS just once
  2701   2704     ** and reuse it many names.
  2702   2705     */
  2703         -  if( !ExprHasProperty(pExpr, EP_VarSelect) && pParse->iSelfTab==0 ){
         2706  +  if( !ExprHasProperty(pExpr, EP_VarSelect)
         2707  +   && pParse->iSelfTab==0
         2708  +   && (inFlags & IN_INDEX_LOOP)==0
         2709  +  ){
  2704   2710       /* Reuse of the RHS is allowed */
  2705   2711       /* If this routine has already been coded, but the previous code
  2706   2712       ** might not have been invoked yet, so invoke it now as a subroutine. 
  2707   2713       */
  2708   2714       if( ExprHasProperty(pExpr, EP_Subrtn) ){
  2709   2715         addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
  2710   2716         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
................................................................................
  2754   2760       ** Generate code to write the results of the select into the temporary
  2755   2761       ** table allocated and opened above.
  2756   2762       */
  2757   2763       Select *pSelect = pExpr->x.pSelect;
  2758   2764       ExprList *pEList = pSelect->pEList;
  2759   2765   
  2760   2766       ExplainQueryPlan((pParse, 1, "%sLIST SUBQUERY %d",
  2761         -        addrOnce?"":"CORRELATED ", pSelect->selId
         2767  +        ExprHasProperty(pExpr, EP_VarSelect)?"CORRELATED ":"", pSelect->selId
  2762   2768       ));
  2763   2769       assert( !isRowid );
  2764   2770       /* If the LHS and RHS of the IN operator do not match, that
  2765   2771       ** error will have been caught long before we reach this point. */
  2766   2772       if( ALWAYS(pEList->nExpr==nVal) ){
  2767   2773         SelectDest dest;
  2768   2774         int i;

Changes to src/sqliteInt.h.

  4277   4277   void sqlite3Reindex(Parse*, Token*, Token*);
  4278   4278   void sqlite3AlterFunctions(void);
  4279   4279   void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
  4280   4280   void sqlite3AlterRenameColumn(Parse*, SrcList*, Token*, Token*);
  4281   4281   int sqlite3GetToken(const unsigned char *, int *);
  4282   4282   void sqlite3NestedParse(Parse*, const char*, ...);
  4283   4283   void sqlite3ExpirePreparedStatements(sqlite3*, int);
  4284         -void sqlite3CodeRhsOfIN(Parse*, Expr*, int, int);
         4284  +void sqlite3CodeRhsOfIN(Parse*, Expr*, int, int, u32);
  4285   4285   int sqlite3CodeSubselect(Parse*, Expr*);
  4286   4286   void sqlite3SelectPrep(Parse*, Select*, NameContext*);
  4287   4287   void sqlite3SelectWrongNumTermsError(Parse *pParse, Select *p);
  4288   4288   int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
  4289   4289   int sqlite3ResolveExprNames(NameContext*, Expr*);
  4290   4290   int sqlite3ResolveExprListNames(NameContext*, ExprList*);
  4291   4291   void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);

Changes to test/in.test.

   646    646   do_execsql_test in-14.0 {
   647    647     CREATE TABLE c1(a);
   648    648     INSERT INTO c1 VALUES(1), (2), (4), (3);
   649    649   }
   650    650   do_execsql_test in-14.1 {
   651    651     SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1
   652    652   } {1 2 3 4}
          653  +
          654  +# 2019-02-20 Ticket https://www.sqlite.org/src/tktview/df46dfb631f75694fbb97033b69
          655  +#
          656  +do_execsql_test in-15.0 {
          657  +  DROP TABLE IF EXISTS t1;
          658  +  CREATE TABLE IF NOT EXISTS t1(id INTEGER PRIMARY KEY);
          659  +  INSERT INTO t1 VALUES(1);
          660  +  SELECT a.id FROM t1 AS a JOIN t1 AS b ON a.id=b.id WHERE a.id IN (1,2,3);
          661  +} {1}
          662  +do_execsql_test in-15.1 {
          663  +  DROP TABLE IF EXISTS t2;
          664  +  CREATE TABLE t2(a INTEGER PRIMARY KEY,b);
          665  +  INSERT INTO t2 VALUES(1,11);
          666  +  INSERT INTO t2 VALUES(2,22);
          667  +  INSERT INTO t2 VALUES(3,33);
          668  +  SELECT b, a IN (3,4,5) FROM t2 ORDER BY b;
          669  +} {11 0 22 0 33 1}
          670  +do_execsql_test in-15.2 {
          671  +  DROP TABLE IF EXISTS t3;
          672  +  CREATE TABLE t3(x INTEGER PRIMARY KEY);
          673  +  INSERT INTO t3 VALUES(8);
          674  +  SELECT CASE WHEN x NOT IN (5,6,7) THEN 'yes' ELSE 'no' END FROM t3;
          675  +  SELECT CASE WHEN x NOT IN (NULL,6,7) THEN 'yes' ELSE 'no' END FROM t3;
          676  +} {yes no}
          677  +do_execsql_test in-15.3 {
          678  +  SELECT CASE WHEN x NOT IN (5,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
          679  +  SELECT CASE WHEN x NOT IN (NULL,6,7) OR x=0 THEN 'yes' ELSE 'no' END FROM t3;
          680  +} {yes no}
          681  +do_execsql_test in-15.4 {
          682  +  DROP TABLE IF EXISTS t4;
          683  +  CREATE TABLE t4(a INTEGER PRIMARY KEY, b INT);
          684  +  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<20)
          685  +    INSERT INTO t4(a,b) SELECT x, x+100 FROM c;
          686  +  SELECT b FROM t4 WHERE a IN (3,null,8) ORDER BY +b;
          687  +} {103 108}
          688  +do_execsql_test in-15.5 {
          689  +  SELECT b FROM t4 WHERE a NOT IN (3,null,8);
          690  +} {}
          691  +
   653    692   
   654    693   finish_test