/ Check-in [67bfd59d]
Login

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

Overview
Comment:Use the WITH clause to help resolve names for SELECT statements on the left of a compound query. Proposed fix for ticket [31a19d11b97088296a].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 67bfd59d9087a987f15f6148efa1ff104983e1fb
User & Date: drh 2014-02-09 18:02:09
References
2019-05-29
16:04 New ticket [c41afac3] LIMIT applied globally rather than locally. artifact: 67d7ba17 user: drh
Context
2014-02-09
23:59
Add a compound-query test to the speedtest1 test program. check-in: 53299575 user: drh tags: trunk
18:02
Use the WITH clause to help resolve names for SELECT statements on the left of a compound query. Proposed fix for ticket [31a19d11b97088296a]. check-in: 67bfd59d user: drh tags: trunk
00:52
Add the Mandelbrot Set testcase to the "cte" testset of speedtest1. check-in: 56febbeb user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1056   1056     if( pPrior ) pPrior->pNext = pNew;
  1057   1057     pNew->pNext = 0;
  1058   1058     pNew->pLimit = sqlite3ExprDup(db, p->pLimit, flags);
  1059   1059     pNew->pOffset = sqlite3ExprDup(db, p->pOffset, flags);
  1060   1060     pNew->iLimit = 0;
  1061   1061     pNew->iOffset = 0;
  1062   1062     pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  1063         -  pNew->pRightmost = 0;
  1064   1063     pNew->addrOpenEphm[0] = -1;
  1065   1064     pNew->addrOpenEphm[1] = -1;
  1066   1065     pNew->addrOpenEphm[2] = -1;
  1067   1066     pNew->nSelectRow = p->nSelectRow;
  1068   1067     pNew->pWith = withDup(db, p->pWith);
  1069   1068     return pNew;
  1070   1069   }

Changes to src/parse.y.

   408    408   %type select {Select*}
   409    409   %destructor select {sqlite3SelectDelete(pParse->db, $$);}
   410    410   %type selectnowith {Select*}
   411    411   %destructor selectnowith {sqlite3SelectDelete(pParse->db, $$);}
   412    412   %type oneselect {Select*}
   413    413   %destructor oneselect {sqlite3SelectDelete(pParse->db, $$);}
   414    414   
   415         -select(A) ::= with(W) selectnowith(X). { 
   416         -  if( X ){
   417         -    X->pWith = W; 
          415  +select(A) ::= with(W) selectnowith(X). {
          416  +  Select *p = X, *pNext, *pLoop;
          417  +  if( p ){
          418  +    int cnt = 0, mxSelect;
          419  +    p->pWith = W;
          420  +    if( p->pPrior ){
          421  +      pNext = 0;
          422  +      for(pLoop=p; pLoop; pNext=pLoop, pLoop=pLoop->pPrior, cnt++){
          423  +        pLoop->pNext = pNext;
          424  +        pLoop->selFlags |= SF_Compound;
          425  +      }
          426  +      mxSelect = pParse->db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT];
          427  +      if( mxSelect && cnt>mxSelect ){
          428  +        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
          429  +      }
          430  +    }
   418    431     }else{
   419    432       sqlite3WithDelete(pParse->db, W);
   420    433     }
   421         -  A = X; 
          434  +  A = p;
   422    435   }
   423    436   
   424    437   selectnowith(A) ::= oneselect(X).                      {A = X;}
   425    438   %ifndef SQLITE_OMIT_COMPOUND_SELECT
   426    439   selectnowith(A) ::= selectnowith(X) multiselect_op(Y) oneselect(Z).  {
   427    440     if( Z ){
   428    441       Z->op = (u8)Y;

Changes to src/select.c.

   104    104   */
   105    105   void sqlite3SelectDelete(sqlite3 *db, Select *p){
   106    106     if( p ){
   107    107       clearSelect(db, p);
   108    108       sqlite3DbFree(db, p);
   109    109     }
   110    110   }
          111  +
          112  +/*
          113  +** Return a pointer to the right-most SELECT statement in a compound.
          114  +*/
          115  +static Select *findRightmost(Select *p){
          116  +  while( p->pNext ) p = p->pNext;
          117  +  return p;
          118  +}
   111    119   
   112    120   /*
   113    121   ** Given 1 to 3 identifiers preceding the JOIN keyword, determine the
   114    122   ** type of join.  Return an integer constant that expresses that type
   115    123   ** in terms of the following bit values:
   116    124   **
   117    125   **     JT_INNER
................................................................................
  1873   1881       p->selFlags |= SF_UsesEphemeral;
  1874   1882     }
  1875   1883   
  1876   1884     /* Detach the ORDER BY clause from the compound SELECT */
  1877   1885     p->pOrderBy = 0;
  1878   1886   
  1879   1887     /* Store the results of the setup-query in Queue. */
         1888  +  pSetup->pNext = 0;
  1880   1889     rc = sqlite3Select(pParse, pSetup, &destQueue);
         1890  +  pSetup->pNext = p;
  1881   1891     if( rc ) goto end_of_recursive_query;
  1882   1892   
  1883   1893     /* Find the next row in the Queue and output that row */
  1884   1894     addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak);
  1885   1895   
  1886   1896     /* Transfer the next row in Queue over to Current */
  1887   1897     sqlite3VdbeAddOp1(v, OP_NullRow, iCurrent); /* To reset column cache */
................................................................................
  1978   1988     /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  1979   1989     ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  1980   1990     */
  1981   1991     assert( p && p->pPrior );  /* Calling function guarantees this much */
  1982   1992     assert( (p->selFlags & SF_Recursive)==0 || p->op==TK_ALL || p->op==TK_UNION );
  1983   1993     db = pParse->db;
  1984   1994     pPrior = p->pPrior;
  1985         -  assert( pPrior->pRightmost!=pPrior );
  1986         -  assert( pPrior->pRightmost==p->pRightmost );
  1987   1995     dest = *pDest;
  1988   1996     if( pPrior->pOrderBy ){
  1989   1997       sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
  1990   1998         selectOpName(p->op));
  1991   1999       rc = 1;
  1992   2000       goto multi_select_end;
  1993   2001     }
................................................................................
  2087   2095         Expr *pLimit, *pOffset; /* Saved values of p->nLimit and p->nOffset */
  2088   2096         int addr;
  2089   2097         SelectDest uniondest;
  2090   2098   
  2091   2099         testcase( p->op==TK_EXCEPT );
  2092   2100         testcase( p->op==TK_UNION );
  2093   2101         priorOp = SRT_Union;
  2094         -      if( dest.eDest==priorOp && ALWAYS(!p->pLimit &&!p->pOffset) ){
         2102  +      if( dest.eDest==priorOp ){
  2095   2103           /* We can reuse a temporary table generated by a SELECT to our
  2096   2104           ** right.
  2097   2105           */
  2098         -        assert( p->pRightmost!=p );  /* Can only happen for leftward elements
  2099         -                                     ** of a 3-way or more compound */
  2100   2106           assert( p->pLimit==0 );      /* Not allowed on leftward elements */
  2101   2107           assert( p->pOffset==0 );     /* Not allowed on leftward elements */
  2102   2108           unionTab = dest.iSDParm;
  2103   2109         }else{
  2104   2110           /* We will need to create our own temporary table to hold the
  2105   2111           ** intermediate results.
  2106   2112           */
  2107   2113           unionTab = pParse->nTab++;
  2108   2114           assert( p->pOrderBy==0 );
  2109   2115           addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, unionTab, 0);
  2110   2116           assert( p->addrOpenEphm[0] == -1 );
  2111   2117           p->addrOpenEphm[0] = addr;
  2112         -        p->pRightmost->selFlags |= SF_UsesEphemeral;
         2118  +        findRightmost(p)->selFlags |= SF_UsesEphemeral;
  2113   2119           assert( p->pEList );
  2114   2120         }
  2115   2121   
  2116   2122         /* Code the SELECT statements to our left
  2117   2123         */
  2118   2124         assert( !pPrior->pOrderBy );
  2119   2125         sqlite3SelectDestInit(&uniondest, priorOp, unionTab);
................................................................................
  2194   2200         tab1 = pParse->nTab++;
  2195   2201         tab2 = pParse->nTab++;
  2196   2202         assert( p->pOrderBy==0 );
  2197   2203   
  2198   2204         addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab1, 0);
  2199   2205         assert( p->addrOpenEphm[0] == -1 );
  2200   2206         p->addrOpenEphm[0] = addr;
  2201         -      p->pRightmost->selFlags |= SF_UsesEphemeral;
         2207  +      findRightmost(p)->selFlags |= SF_UsesEphemeral;
  2202   2208         assert( p->pEList );
  2203   2209   
  2204   2210         /* Code the SELECTs to our left into temporary table "tab1".
  2205   2211         */
  2206   2212         sqlite3SelectDestInit(&intersectdest, SRT_Union, tab1);
  2207   2213         explainSetInteger(iSub1, pParse->iNextSelectId);
  2208   2214         rc = sqlite3Select(pParse, pPrior, &intersectdest);
................................................................................
  2273   2279     if( p->selFlags & SF_UsesEphemeral ){
  2274   2280       int i;                        /* Loop counter */
  2275   2281       KeyInfo *pKeyInfo;            /* Collating sequence for the result set */
  2276   2282       Select *pLoop;                /* For looping through SELECT statements */
  2277   2283       CollSeq **apColl;             /* For looping through pKeyInfo->aColl[] */
  2278   2284       int nCol;                     /* Number of columns in result set */
  2279   2285   
  2280         -    assert( p->pRightmost==p );
         2286  +    assert( p->pNext==0 );
  2281   2287       nCol = p->pEList->nExpr;
  2282   2288       pKeyInfo = sqlite3KeyInfoAlloc(db, nCol, 1);
  2283   2289       if( !pKeyInfo ){
  2284   2290         rc = SQLITE_NOMEM;
  2285   2291         goto multi_select_end;
  2286   2292       }
  2287   2293       for(i=0, apColl=pKeyInfo->aColl; i<nCol; i++, apColl++){
................................................................................
  2689   2695         }
  2690   2696       }
  2691   2697     }
  2692   2698    
  2693   2699     /* Separate the left and the right query from one another
  2694   2700     */
  2695   2701     p->pPrior = 0;
         2702  +  pPrior->pNext = 0;
  2696   2703     sqlite3ResolveOrderGroupBy(pParse, p, p->pOrderBy, "ORDER");
  2697   2704     if( pPrior->pPrior==0 ){
  2698   2705       sqlite3ResolveOrderGroupBy(pParse, pPrior, pPrior->pOrderBy, "ORDER");
  2699   2706     }
  2700   2707   
  2701   2708     /* Compute the limit registers */
  2702   2709     computeLimitRegisters(pParse, p, labelEnd);
................................................................................
  2854   2861   
  2855   2862     /* Reassembly the compound query so that it will be freed correctly
  2856   2863     ** by the calling function */
  2857   2864     if( p->pPrior ){
  2858   2865       sqlite3SelectDelete(db, p->pPrior);
  2859   2866     }
  2860   2867     p->pPrior = pPrior;
         2868  +  pPrior->pNext = p;
  2861   2869   
  2862   2870     /*** TBD:  Insert subroutine calls to close cursors on incomplete
  2863   2871     **** subqueries ****/
  2864   2872     explainComposite(pParse, p->op, iSub1, iSub2, 0);
  2865   2873     return SQLITE_OK;
  2866   2874   }
  2867   2875   #endif
................................................................................
  3119   3127     /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
  3120   3128     ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET
  3121   3129     ** because they could be computed at compile-time.  But when LIMIT and OFFSET
  3122   3130     ** became arbitrary expressions, we were forced to add restrictions (13)
  3123   3131     ** and (14). */
  3124   3132     if( pSub->pLimit && p->pLimit ) return 0;              /* Restriction (13) */
  3125   3133     if( pSub->pOffset ) return 0;                          /* Restriction (14) */
  3126         -  if( p->pRightmost && pSub->pLimit ){
         3134  +  if( (p->selFlags & SF_Compound)!=0 && pSub->pLimit ){
  3127   3135       return 0;                                            /* Restriction (15) */
  3128   3136     }
  3129   3137     if( pSubSrc->nSrc==0 ) return 0;                       /* Restriction (7)  */
  3130   3138     if( pSub->selFlags & SF_Distinct ) return 0;           /* Restriction (5)  */
  3131   3139     if( pSub->pLimit && (pSrc->nSrc>1 || isAgg) ){
  3132   3140        return 0;         /* Restrictions (8)(9) */
  3133   3141     }
................................................................................
  3270   3278       p->pOffset = 0;
  3271   3279       pNew = sqlite3SelectDup(db, p, 0);
  3272   3280       p->pOffset = pOffset;
  3273   3281       p->pLimit = pLimit;
  3274   3282       p->pOrderBy = pOrderBy;
  3275   3283       p->pSrc = pSrc;
  3276   3284       p->op = TK_ALL;
  3277         -    p->pRightmost = 0;
  3278   3285       if( pNew==0 ){
  3279         -      pNew = pPrior;
         3286  +      p->pPrior = pPrior;
  3280   3287       }else{
  3281   3288         pNew->pPrior = pPrior;
  3282         -      pNew->pRightmost = 0;
         3289  +      if( pPrior ) pPrior->pNext = pNew;
         3290  +      pNew->pNext = p;
         3291  +      p->pPrior = pNew;
  3283   3292       }
  3284         -    p->pPrior = pNew;
  3285   3293       if( db->mallocFailed ) return 1;
  3286   3294     }
  3287   3295   
  3288   3296     /* Begin flattening the iFrom-th entry of the FROM clause 
  3289   3297     ** in the outer query.
  3290   3298     */
  3291   3299     pSub = pSub1 = pSubitem->pSelect;
................................................................................
  3803   3811   **
  3804   3812   ** This function is used as the xSelectCallback2() callback by
  3805   3813   ** sqlite3SelectExpand() when walking a SELECT tree to resolve table
  3806   3814   ** names and other FROM clause elements. 
  3807   3815   */
  3808   3816   static void selectPopWith(Walker *pWalker, Select *p){
  3809   3817     Parse *pParse = pWalker->pParse;
  3810         -  if( p->pWith ){
  3811         -    assert( pParse->pWith==p->pWith );
  3812         -    pParse->pWith = p->pWith->pOuter;
         3818  +  With *pWith = findRightmost(p)->pWith;
         3819  +  if( pWith!=0 ){
         3820  +    assert( pParse->pWith==pWith );
         3821  +    pParse->pWith = pWith->pOuter;
  3813   3822     }
  3814   3823   }
  3815   3824   #else
  3816   3825   #define selectPopWith 0
  3817   3826   #endif
  3818   3827   
  3819   3828   /*
................................................................................
  3855   3864       return WRC_Abort;
  3856   3865     }
  3857   3866     if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){
  3858   3867       return WRC_Prune;
  3859   3868     }
  3860   3869     pTabList = p->pSrc;
  3861   3870     pEList = p->pEList;
  3862         -  sqlite3WithPush(pParse, p->pWith, 0);
         3871  +  sqlite3WithPush(pParse, findRightmost(p)->pWith, 0);
  3863   3872   
  3864   3873     /* Make sure cursor numbers have been assigned to all entries in
  3865   3874     ** the FROM clause of the SELECT statement.
  3866   3875     */
  3867   3876     sqlite3SrcListAssignCursors(pParse, pTabList);
  3868   3877   
  3869   3878     /* Look up every table named in the FROM clause of the select.  If
................................................................................
  4599   4608     pHaving = p->pHaving;
  4600   4609     sDistinct.isTnct = (p->selFlags & SF_Distinct)!=0;
  4601   4610   
  4602   4611   #ifndef SQLITE_OMIT_COMPOUND_SELECT
  4603   4612     /* If there is are a sequence of queries, do the earlier ones first.
  4604   4613     */
  4605   4614     if( p->pPrior ){
  4606         -    if( p->pRightmost==0 ){
  4607         -      Select *pLoop, *pRight = 0;
  4608         -      int cnt = 0;
  4609         -      int mxSelect;
  4610         -      for(pLoop=p; pLoop; pLoop=pLoop->pPrior, cnt++){
  4611         -        pLoop->pRightmost = p;
  4612         -        pLoop->pNext = pRight;
  4613         -        pRight = pLoop;
  4614         -      }
  4615         -      mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT];
  4616         -      if( mxSelect && cnt>mxSelect ){
  4617         -        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
  4618         -        goto select_end;
  4619         -      }
  4620         -    }
  4621   4615       rc = multiSelect(pParse, p, pDest);
  4622   4616       explainSetInteger(pParse->iSelectId, iRestoreSelectId);
  4623   4617       return rc;
  4624   4618     }
  4625   4619   #endif
  4626   4620   
  4627   4621     /* If there is both a GROUP BY and an ORDER BY clause and they are
................................................................................
  5275   5269     }
  5276   5270   }
  5277   5271   void sqlite3ExplainSelect(Vdbe *pVdbe, Select *p){
  5278   5272     if( p==0 ){
  5279   5273       sqlite3ExplainPrintf(pVdbe, "(null-select)");
  5280   5274       return;
  5281   5275     }
  5282         -  while( p->pPrior ){
  5283         -    p->pPrior->pNext = p;
  5284         -    p = p->pPrior;
  5285         -  }
  5286   5276     sqlite3ExplainPush(pVdbe);
  5287   5277     while( p ){
  5288   5278       explainOneSelect(pVdbe, p);
  5289   5279       p = p->pNext;
  5290   5280       if( p==0 ) break;
  5291   5281       sqlite3ExplainNL(pVdbe);
  5292   5282       sqlite3ExplainPrintf(pVdbe, "%s\n", selectOpName(p->op));

Changes to src/sqliteInt.h.

  2144   2144     SrcList *pSrc;         /* The FROM clause */
  2145   2145     Expr *pWhere;          /* The WHERE clause */
  2146   2146     ExprList *pGroupBy;    /* The GROUP BY clause */
  2147   2147     Expr *pHaving;         /* The HAVING clause */
  2148   2148     ExprList *pOrderBy;    /* The ORDER BY clause */
  2149   2149     Select *pPrior;        /* Prior select in a compound select statement */
  2150   2150     Select *pNext;         /* Next select to the left in a compound */
  2151         -  Select *pRightmost;    /* Right-most select in a compound select statement */
  2152   2151     Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  2153   2152     Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  2154   2153     With *pWith;           /* WITH clause attached to this select. Or NULL. */
  2155   2154   };
  2156   2155   
  2157   2156   /*
  2158   2157   ** Allowed values for Select.selFlags.  The "SF" prefix stands for
................................................................................
  2166   2165   #define SF_HasTypeInfo     0x0020  /* FROM subqueries have Table metadata */
  2167   2166   #define SF_UseSorter       0x0040  /* Sort using a sorter */
  2168   2167   #define SF_Values          0x0080  /* Synthesized from VALUES clause */
  2169   2168   #define SF_Materialize     0x0100  /* NOT USED */
  2170   2169   #define SF_NestedFrom      0x0200  /* Part of a parenthesized FROM clause */
  2171   2170   #define SF_MaybeConvert    0x0400  /* Need convertCompoundSelectToSubquery() */
  2172   2171   #define SF_Recursive       0x0800  /* The recursive part of a recursive CTE */
         2172  +#define SF_Compound        0x1000  /* Part of a compound query */
  2173   2173   
  2174   2174   
  2175   2175   /*
  2176   2176   ** The results of a SELECT can be distributed in several ways, as defined
  2177   2177   ** by one of the following macros.  The "SRT" prefix means "SELECT Result
  2178   2178   ** Type".
  2179   2179   **

Changes to test/with1.test.

   812    812   .........Jim
   813    813   .........Kate
   814    814   .........Lanny
   815    815   .........Mary
   816    816   .........Noland
   817    817   .........Olivia}}
   818    818   
   819         -finish_test
          819  +#--------------------------------------------------------------------------
          820  +# Ticket [31a19d11b97088296ac104aaff113a9790394927] (2014-02-09)
          821  +# Name resolution issue with compound SELECTs and Common Table Expressions 
          822  +#
          823  +do_execsql_test 12.1 {
          824  +WITH RECURSIVE
          825  +  t1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM t1 WHERE x<20),
          826  +  t2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM t2 WHERE y<20)
          827  +SELECT x FROM t1 EXCEPT SELECT y FROM t2 ORDER BY 1;
          828  +} {2 4 8 10 14 16 20}
          829  +
   820    830   
          831  +finish_test