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: |
67bfd59d9087a987f15f6148efa1ff10 |
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
Changes to src/expr.c.
︙ | ︙ | |||
1056 1057 1058 1059 1060 1061 1062 | if( pPrior ) pPrior->pNext = pNew; pNew->pNext = 0; pNew->pLimit = sqlite3ExprDup(db, p->pLimit, flags); pNew->pOffset = sqlite3ExprDup(db, p->pOffset, flags); pNew->iLimit = 0; pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; | < | 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 | if( pPrior ) pPrior->pNext = pNew; pNew->pNext = 0; pNew->pLimit = sqlite3ExprDup(db, p->pLimit, flags); pNew->pOffset = sqlite3ExprDup(db, p->pOffset, flags); pNew->iLimit = 0; pNew->iOffset = 0; pNew->selFlags = p->selFlags & ~SF_UsesEphemeral; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->addrOpenEphm[2] = -1; pNew->nSelectRow = p->nSelectRow; pNew->pWith = withDup(db, p->pWith); return pNew; } |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
408 409 410 411 412 413 414 | %type select {Select*} %destructor select {sqlite3SelectDelete(pParse->db, $$);} %type selectnowith {Select*} %destructor selectnowith {sqlite3SelectDelete(pParse->db, $$);} %type oneselect {Select*} %destructor oneselect {sqlite3SelectDelete(pParse->db, $$);} | | > | > | > > > > > > > > > > > | | 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 | %type select {Select*} %destructor select {sqlite3SelectDelete(pParse->db, $$);} %type selectnowith {Select*} %destructor selectnowith {sqlite3SelectDelete(pParse->db, $$);} %type oneselect {Select*} %destructor oneselect {sqlite3SelectDelete(pParse->db, $$);} select(A) ::= with(W) selectnowith(X). { Select *p = X, *pNext, *pLoop; if( p ){ int cnt = 0, mxSelect; p->pWith = W; if( p->pPrior ){ pNext = 0; for(pLoop=p; pLoop; pNext=pLoop, pLoop=pLoop->pPrior, cnt++){ pLoop->pNext = pNext; pLoop->selFlags |= SF_Compound; } mxSelect = pParse->db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT]; if( mxSelect && cnt>mxSelect ){ sqlite3ErrorMsg(pParse, "too many terms in compound SELECT"); } } }else{ sqlite3WithDelete(pParse->db, W); } A = p; } selectnowith(A) ::= oneselect(X). {A = X;} %ifndef SQLITE_OMIT_COMPOUND_SELECT selectnowith(A) ::= selectnowith(X) multiselect_op(Y) oneselect(Z). { if( Z ){ Z->op = (u8)Y; |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
104 105 106 107 108 109 110 111 112 113 114 115 116 117 | */ void sqlite3SelectDelete(sqlite3 *db, Select *p){ if( p ){ clearSelect(db, p); sqlite3DbFree(db, p); } } /* ** Given 1 to 3 identifiers preceding the JOIN keyword, determine the ** type of join. Return an integer constant that expresses that type ** in terms of the following bit values: ** ** JT_INNER | > > > > > > > > | 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | */ void sqlite3SelectDelete(sqlite3 *db, Select *p){ if( p ){ clearSelect(db, p); sqlite3DbFree(db, p); } } /* ** Return a pointer to the right-most SELECT statement in a compound. */ static Select *findRightmost(Select *p){ while( p->pNext ) p = p->pNext; return p; } /* ** Given 1 to 3 identifiers preceding the JOIN keyword, determine the ** type of join. Return an integer constant that expresses that type ** in terms of the following bit values: ** ** JT_INNER |
︙ | ︙ | |||
1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 | p->selFlags |= SF_UsesEphemeral; } /* Detach the ORDER BY clause from the compound SELECT */ p->pOrderBy = 0; /* Store the results of the setup-query in Queue. */ rc = sqlite3Select(pParse, pSetup, &destQueue); if( rc ) goto end_of_recursive_query; /* Find the next row in the Queue and output that row */ addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak); /* Transfer the next row in Queue over to Current */ sqlite3VdbeAddOp1(v, OP_NullRow, iCurrent); /* To reset column cache */ | > > | 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 | p->selFlags |= SF_UsesEphemeral; } /* Detach the ORDER BY clause from the compound SELECT */ p->pOrderBy = 0; /* Store the results of the setup-query in Queue. */ pSetup->pNext = 0; rc = sqlite3Select(pParse, pSetup, &destQueue); pSetup->pNext = p; if( rc ) goto end_of_recursive_query; /* Find the next row in the Queue and output that row */ addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak); /* Transfer the next row in Queue over to Current */ sqlite3VdbeAddOp1(v, OP_NullRow, iCurrent); /* To reset column cache */ |
︙ | ︙ | |||
1978 1979 1980 1981 1982 1983 1984 | /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT. */ assert( p && p->pPrior ); /* Calling function guarantees this much */ assert( (p->selFlags & SF_Recursive)==0 || p->op==TK_ALL || p->op==TK_UNION ); db = pParse->db; pPrior = p->pPrior; | < < | 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 | /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs. Only ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT. */ assert( p && p->pPrior ); /* Calling function guarantees this much */ assert( (p->selFlags & SF_Recursive)==0 || p->op==TK_ALL || p->op==TK_UNION ); db = pParse->db; pPrior = p->pPrior; dest = *pDest; if( pPrior->pOrderBy ){ sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before", selectOpName(p->op)); rc = 1; goto multi_select_end; } |
︙ | ︙ | |||
2087 2088 2089 2090 2091 2092 2093 | Expr *pLimit, *pOffset; /* Saved values of p->nLimit and p->nOffset */ int addr; SelectDest uniondest; testcase( p->op==TK_EXCEPT ); testcase( p->op==TK_UNION ); priorOp = SRT_Union; | | < < | | 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 | Expr *pLimit, *pOffset; /* Saved values of p->nLimit and p->nOffset */ int addr; SelectDest uniondest; testcase( p->op==TK_EXCEPT ); testcase( p->op==TK_UNION ); priorOp = SRT_Union; if( dest.eDest==priorOp ){ /* We can reuse a temporary table generated by a SELECT to our ** right. */ assert( p->pLimit==0 ); /* Not allowed on leftward elements */ assert( p->pOffset==0 ); /* Not allowed on leftward elements */ unionTab = dest.iSDParm; }else{ /* We will need to create our own temporary table to hold the ** intermediate results. */ unionTab = pParse->nTab++; assert( p->pOrderBy==0 ); addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, unionTab, 0); assert( p->addrOpenEphm[0] == -1 ); p->addrOpenEphm[0] = addr; findRightmost(p)->selFlags |= SF_UsesEphemeral; assert( p->pEList ); } /* Code the SELECT statements to our left */ assert( !pPrior->pOrderBy ); sqlite3SelectDestInit(&uniondest, priorOp, unionTab); |
︙ | ︙ | |||
2194 2195 2196 2197 2198 2199 2200 | tab1 = pParse->nTab++; tab2 = pParse->nTab++; assert( p->pOrderBy==0 ); addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab1, 0); assert( p->addrOpenEphm[0] == -1 ); p->addrOpenEphm[0] = addr; | | | 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 | tab1 = pParse->nTab++; tab2 = pParse->nTab++; assert( p->pOrderBy==0 ); addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab1, 0); assert( p->addrOpenEphm[0] == -1 ); p->addrOpenEphm[0] = addr; findRightmost(p)->selFlags |= SF_UsesEphemeral; assert( p->pEList ); /* Code the SELECTs to our left into temporary table "tab1". */ sqlite3SelectDestInit(&intersectdest, SRT_Union, tab1); explainSetInteger(iSub1, pParse->iNextSelectId); rc = sqlite3Select(pParse, pPrior, &intersectdest); |
︙ | ︙ | |||
2273 2274 2275 2276 2277 2278 2279 | if( p->selFlags & SF_UsesEphemeral ){ int i; /* Loop counter */ KeyInfo *pKeyInfo; /* Collating sequence for the result set */ Select *pLoop; /* For looping through SELECT statements */ CollSeq **apColl; /* For looping through pKeyInfo->aColl[] */ int nCol; /* Number of columns in result set */ | | | 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 | if( p->selFlags & SF_UsesEphemeral ){ int i; /* Loop counter */ KeyInfo *pKeyInfo; /* Collating sequence for the result set */ Select *pLoop; /* For looping through SELECT statements */ CollSeq **apColl; /* For looping through pKeyInfo->aColl[] */ int nCol; /* Number of columns in result set */ assert( p->pNext==0 ); nCol = p->pEList->nExpr; pKeyInfo = sqlite3KeyInfoAlloc(db, nCol, 1); if( !pKeyInfo ){ rc = SQLITE_NOMEM; goto multi_select_end; } for(i=0, apColl=pKeyInfo->aColl; i<nCol; i++, apColl++){ |
︙ | ︙ | |||
2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 | } } } /* Separate the left and the right query from one another */ p->pPrior = 0; sqlite3ResolveOrderGroupBy(pParse, p, p->pOrderBy, "ORDER"); if( pPrior->pPrior==0 ){ sqlite3ResolveOrderGroupBy(pParse, pPrior, pPrior->pOrderBy, "ORDER"); } /* Compute the limit registers */ computeLimitRegisters(pParse, p, labelEnd); | > | 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 | } } } /* Separate the left and the right query from one another */ p->pPrior = 0; pPrior->pNext = 0; sqlite3ResolveOrderGroupBy(pParse, p, p->pOrderBy, "ORDER"); if( pPrior->pPrior==0 ){ sqlite3ResolveOrderGroupBy(pParse, pPrior, pPrior->pOrderBy, "ORDER"); } /* Compute the limit registers */ computeLimitRegisters(pParse, p, labelEnd); |
︙ | ︙ | |||
2854 2855 2856 2857 2858 2859 2860 2861 2862 2863 2864 2865 2866 2867 | /* Reassembly the compound query so that it will be freed correctly ** by the calling function */ if( p->pPrior ){ sqlite3SelectDelete(db, p->pPrior); } p->pPrior = pPrior; /*** TBD: Insert subroutine calls to close cursors on incomplete **** subqueries ****/ explainComposite(pParse, p->op, iSub1, iSub2, 0); return SQLITE_OK; } #endif | > | 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 | /* Reassembly the compound query so that it will be freed correctly ** by the calling function */ if( p->pPrior ){ sqlite3SelectDelete(db, p->pPrior); } p->pPrior = pPrior; pPrior->pNext = p; /*** TBD: Insert subroutine calls to close cursors on incomplete **** subqueries ****/ explainComposite(pParse, p->op, iSub1, iSub2, 0); return SQLITE_OK; } #endif |
︙ | ︙ | |||
3119 3120 3121 3122 3123 3124 3125 | /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants, ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET ** because they could be computed at compile-time. But when LIMIT and OFFSET ** became arbitrary expressions, we were forced to add restrictions (13) ** and (14). */ if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */ if( pSub->pOffset ) return 0; /* Restriction (14) */ | | | 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 | /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants, ** not arbitrary expresssions, we allowed some combining of LIMIT and OFFSET ** because they could be computed at compile-time. But when LIMIT and OFFSET ** became arbitrary expressions, we were forced to add restrictions (13) ** and (14). */ if( pSub->pLimit && p->pLimit ) return 0; /* Restriction (13) */ if( pSub->pOffset ) return 0; /* Restriction (14) */ if( (p->selFlags & SF_Compound)!=0 && pSub->pLimit ){ return 0; /* Restriction (15) */ } if( pSubSrc->nSrc==0 ) return 0; /* Restriction (7) */ if( pSub->selFlags & SF_Distinct ) return 0; /* Restriction (5) */ if( pSub->pLimit && (pSrc->nSrc>1 || isAgg) ){ return 0; /* Restrictions (8)(9) */ } |
︙ | ︙ | |||
3270 3271 3272 3273 3274 3275 3276 | p->pOffset = 0; pNew = sqlite3SelectDup(db, p, 0); p->pOffset = pOffset; p->pLimit = pLimit; p->pOrderBy = pOrderBy; p->pSrc = pSrc; p->op = TK_ALL; | < | > | < | > | 3278 3279 3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 | p->pOffset = 0; pNew = sqlite3SelectDup(db, p, 0); p->pOffset = pOffset; p->pLimit = pLimit; p->pOrderBy = pOrderBy; p->pSrc = pSrc; p->op = TK_ALL; if( pNew==0 ){ p->pPrior = pPrior; }else{ pNew->pPrior = pPrior; if( pPrior ) pPrior->pNext = pNew; pNew->pNext = p; p->pPrior = pNew; } if( db->mallocFailed ) return 1; } /* Begin flattening the iFrom-th entry of the FROM clause ** in the outer query. */ pSub = pSub1 = pSubitem->pSelect; |
︙ | ︙ | |||
3803 3804 3805 3806 3807 3808 3809 | ** ** This function is used as the xSelectCallback2() callback by ** sqlite3SelectExpand() when walking a SELECT tree to resolve table ** names and other FROM clause elements. */ static void selectPopWith(Walker *pWalker, Select *p){ Parse *pParse = pWalker->pParse; | > | | | | 3811 3812 3813 3814 3815 3816 3817 3818 3819 3820 3821 3822 3823 3824 3825 3826 3827 3828 | ** ** This function is used as the xSelectCallback2() callback by ** sqlite3SelectExpand() when walking a SELECT tree to resolve table ** names and other FROM clause elements. */ static void selectPopWith(Walker *pWalker, Select *p){ Parse *pParse = pWalker->pParse; With *pWith = findRightmost(p)->pWith; if( pWith!=0 ){ assert( pParse->pWith==pWith ); pParse->pWith = pWith->pOuter; } } #else #define selectPopWith 0 #endif /* |
︙ | ︙ | |||
3855 3856 3857 3858 3859 3860 3861 | return WRC_Abort; } if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){ return WRC_Prune; } pTabList = p->pSrc; pEList = p->pEList; | | | 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 3876 3877 3878 | return WRC_Abort; } if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){ return WRC_Prune; } pTabList = p->pSrc; pEList = p->pEList; sqlite3WithPush(pParse, findRightmost(p)->pWith, 0); /* Make sure cursor numbers have been assigned to all entries in ** the FROM clause of the SELECT statement. */ sqlite3SrcListAssignCursors(pParse, pTabList); /* Look up every table named in the FROM clause of the select. If |
︙ | ︙ | |||
4599 4600 4601 4602 4603 4604 4605 | pHaving = p->pHaving; sDistinct.isTnct = (p->selFlags & SF_Distinct)!=0; #ifndef SQLITE_OMIT_COMPOUND_SELECT /* If there is are a sequence of queries, do the earlier ones first. */ if( p->pPrior ){ | < < < < < < < < < < < < < < < | 4608 4609 4610 4611 4612 4613 4614 4615 4616 4617 4618 4619 4620 4621 | pHaving = p->pHaving; sDistinct.isTnct = (p->selFlags & SF_Distinct)!=0; #ifndef SQLITE_OMIT_COMPOUND_SELECT /* If there is are a sequence of queries, do the earlier ones first. */ if( p->pPrior ){ rc = multiSelect(pParse, p, pDest); explainSetInteger(pParse->iSelectId, iRestoreSelectId); return rc; } #endif /* If there is both a GROUP BY and an ORDER BY clause and they are |
︙ | ︙ | |||
5275 5276 5277 5278 5279 5280 5281 | } } void sqlite3ExplainSelect(Vdbe *pVdbe, Select *p){ if( p==0 ){ sqlite3ExplainPrintf(pVdbe, "(null-select)"); return; } | < < < < | 5269 5270 5271 5272 5273 5274 5275 5276 5277 5278 5279 5280 5281 5282 | } } void sqlite3ExplainSelect(Vdbe *pVdbe, Select *p){ if( p==0 ){ sqlite3ExplainPrintf(pVdbe, "(null-select)"); return; } sqlite3ExplainPush(pVdbe); while( p ){ explainOneSelect(pVdbe, p); p = p->pNext; if( p==0 ) break; sqlite3ExplainNL(pVdbe); sqlite3ExplainPrintf(pVdbe, "%s\n", selectOpName(p->op)); |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2144 2145 2146 2147 2148 2149 2150 | SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ Select *pNext; /* Next select to the left in a compound */ | < | 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 | SrcList *pSrc; /* The FROM clause */ Expr *pWhere; /* The WHERE clause */ ExprList *pGroupBy; /* The GROUP BY clause */ Expr *pHaving; /* The HAVING clause */ ExprList *pOrderBy; /* The ORDER BY clause */ Select *pPrior; /* Prior select in a compound select statement */ Select *pNext; /* Next select to the left in a compound */ Expr *pLimit; /* LIMIT expression. NULL means not used. */ Expr *pOffset; /* OFFSET expression. NULL means not used. */ With *pWith; /* WITH clause attached to this select. Or NULL. */ }; /* ** Allowed values for Select.selFlags. The "SF" prefix stands for |
︙ | ︙ | |||
2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 | #define SF_HasTypeInfo 0x0020 /* FROM subqueries have Table metadata */ #define SF_UseSorter 0x0040 /* Sort using a sorter */ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ #define SF_Materialize 0x0100 /* NOT USED */ #define SF_NestedFrom 0x0200 /* Part of a parenthesized FROM clause */ #define SF_MaybeConvert 0x0400 /* Need convertCompoundSelectToSubquery() */ #define SF_Recursive 0x0800 /* The recursive part of a recursive CTE */ /* ** The results of a SELECT can be distributed in several ways, as defined ** by one of the following macros. The "SRT" prefix means "SELECT Result ** Type". ** | > | 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 | #define SF_HasTypeInfo 0x0020 /* FROM subqueries have Table metadata */ #define SF_UseSorter 0x0040 /* Sort using a sorter */ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ #define SF_Materialize 0x0100 /* NOT USED */ #define SF_NestedFrom 0x0200 /* Part of a parenthesized FROM clause */ #define SF_MaybeConvert 0x0400 /* Need convertCompoundSelectToSubquery() */ #define SF_Recursive 0x0800 /* The recursive part of a recursive CTE */ #define SF_Compound 0x1000 /* Part of a compound query */ /* ** The results of a SELECT can be distributed in several ways, as defined ** by one of the following macros. The "SRT" prefix means "SELECT Result ** Type". ** |
︙ | ︙ |
Changes to test/with1.test.
︙ | ︙ | |||
812 813 814 815 816 817 818 | .........Jim .........Kate .........Lanny .........Mary .........Noland .........Olivia}} | > > > > > > > > > > | > | 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 | .........Jim .........Kate .........Lanny .........Mary .........Noland .........Olivia}} #-------------------------------------------------------------------------- # Ticket [31a19d11b97088296ac104aaff113a9790394927] (2014-02-09) # Name resolution issue with compound SELECTs and Common Table Expressions # do_execsql_test 12.1 { WITH RECURSIVE t1(x) AS (VALUES(2) UNION ALL SELECT x+2 FROM t1 WHERE x<20), t2(y) AS (VALUES(3) UNION ALL SELECT y+3 FROM t2 WHERE y<20) SELECT x FROM t1 EXCEPT SELECT y FROM t2 ORDER BY 1; } {2 4 8 10 14 16 20} finish_test |