/ Check-in [8d663bfa]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Disable the use of coroutines for subqueries within a query that is the RHS of an IN operator, as the IN operator might be evaluated more than once. Possible fix for [787fa716be3a7f65], unless we can come up with something better. Later: Counter-example found.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | tkt787fa716-deadend
Files: files | file ages | folders
SHA3-256: 8d663bfaaa4656c4f4ff115017404aa88da4931a6aacc8069060f121e43d0240
User & Date: drh 2018-11-09 00:02:02
Original Comment: Disable the use of coroutines for subqueries within a query that is the RHS of an IN operator, as the IN operator might be evaluated more than once. Possible fix for [787fa716be3a7f65], unless we can come up with something better.
References
2018-11-09
13:39 Ticket [787fa716] Assertion fault when multi-use subquery implemented by co-routine status still Open with 4 other changes artifact: 23c14e4a user: drh
Context
2018-11-09
00:02
Disable the use of coroutines for subqueries within a query that is the RHS of an IN operator, as the IN operator might be evaluated more than once. Possible fix for [787fa716be3a7f65], unless we can come up with something better. Later: Counter-example found. Closed-Leaf check-in: 8d663bfa user: drh tags: tkt787fa716-deadend
2018-11-08
22:53
In the treeview.c module, break out the display of SrcList into a separate subroutine, so that it can be invoked while debugging. check-in: 8c74065f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2512   2512       if( inFlags & IN_INDEX_LOOP ){
  2513   2513         pParse->nQueryLoop = 0;
  2514   2514         if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){
  2515   2515           eType = IN_INDEX_ROWID;
  2516   2516         }
  2517   2517       }else if( prRhsHasNull ){
  2518   2518         *prRhsHasNull = rMayHaveNull = ++pParse->nMem;
         2519  +    }
         2520  +    if( ExprHasProperty(pX, EP_xIsSelect) ){
         2521  +      pX->x.pSelect->selFlags |= SF_RhsOfIN;
  2519   2522       }
  2520   2523       sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
  2521   2524       pParse->nQueryLoop = savedNQueryLoop;
  2522   2525     }else{
  2523   2526       pX->iTable = iTab;
  2524   2527     }
  2525   2528   

Changes to src/select.c.

  5875   5875       zSavedAuthContext = pParse->zAuthContext;
  5876   5876       pParse->zAuthContext = pItem->zName;
  5877   5877   
  5878   5878       /* Generate code to implement the subquery
  5879   5879       **
  5880   5880       ** The subquery is implemented as a co-routine if the subquery is
  5881   5881       ** guaranteed to be the outer loop (so that it does not need to be
  5882         -    ** computed more than once)
         5882  +    ** computed more than once). (1)
  5883   5883       **
  5884         -    ** TODO: Are there other reasons beside (1) to use a co-routine
  5885         -    ** implementation?
         5884  +    ** Avoid using a co-routines to compute any SELECT that occurs in
         5885  +    ** the RHS of an IN operator, as they can be used multiple times. (2)
  5886   5886       */
  5887   5887       if( i==0
  5888   5888        && (pTabList->nSrc==1
  5889   5889               || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0)  /* (1) */
         5890  +     && (p->selFlags & SF_RhsOfIN)==0                               /* (2) */
  5890   5891       ){
  5891   5892         /* Implement a co-routine that will return a single row of the result
  5892   5893         ** set on each invocation.
  5893   5894         */
  5894   5895         int addrTop = sqlite3VdbeCurrentAddr(v)+1;
  5895   5896        
  5896   5897         pItem->regReturn = ++pParse->nMem;

Changes to src/sqliteInt.h.

  2860   2860   #define SF_MinMaxAgg      0x01000  /* Aggregate containing min() or max() */
  2861   2861   #define SF_Recursive      0x02000  /* The recursive part of a recursive CTE */
  2862   2862   #define SF_FixedLimit     0x04000  /* nSelectRow set by a constant LIMIT */
  2863   2863   #define SF_MaybeConvert   0x08000  /* Need convertCompoundSelectToSubquery() */
  2864   2864   #define SF_Converted      0x10000  /* By convertCompoundSelectToSubquery() */
  2865   2865   #define SF_IncludeHidden  0x20000  /* Include hidden columns in output */
  2866   2866   #define SF_ComplexResult  0x40000  /* Result contains subquery or function */
         2867  +#define SF_RhsOfIN        0x80000  /* Result uses as the RHS of IN operator */
  2867   2868   
  2868   2869   /*
  2869   2870   ** The results of a SELECT can be distributed in several ways, as defined
  2870   2871   ** by one of the following macros.  The "SRT" prefix means "SELECT Result
  2871   2872   ** Type".
  2872   2873   **
  2873   2874   **     SRT_Union       Store results as a key in a temporary index