Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Minor cleanups of the compound-subquery flattening logic. New test cases added for joins the compound subquery. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
5061d85ff934db3c217c97acbbbed328 |
| User & Date: | drh 2011-12-10 23:18:32 |
Context
|
2011-12-11
| ||
| 02:29 | Fix harmless compiler warnings. check-in: 1e6a698a user: drh tags: trunk | |
|
2011-12-10
| ||
| 23:18 | Minor cleanups of the compound-subquery flattening logic. New test cases added for joins the compound subquery. check-in: 5061d85f user: drh tags: trunk | |
| 17:17 | Allow UNION ALL compounds to be promoted up to replace a simple wrapper SELECT even if the compounds are joins. check-in: 3d4b4f4f user: drh tags: trunk | |
Changes
Changes to src/expr.c.
866 866 for(i=0; i<p->nExpr; i++, pItem++, pOldItem++){ 867 867 Expr *pOldExpr = pOldItem->pExpr; 868 868 pItem->pExpr = sqlite3ExprDup(db, pOldExpr, flags); 869 869 pItem->zName = sqlite3DbStrDup(db, pOldItem->zName); 870 870 pItem->zSpan = sqlite3DbStrDup(db, pOldItem->zSpan); 871 871 pItem->sortOrder = pOldItem->sortOrder; 872 872 pItem->done = 0; 873 - pItem->iCol = pOldItem->iCol; 873 + pItem->iOrderByCol = pOldItem->iOrderByCol; 874 874 pItem->iAlias = pOldItem->iAlias; 875 875 } 876 876 return pNew; 877 877 } 878 878 879 879 /* 880 880 ** If cursors, triggers, views and subqueries are all omitted from
Changes to src/resolve.c.
795 795 int flags = pE->flags & EP_ExpCollate; 796 796 sqlite3ExprDelete(db, pE); 797 797 pItem->pExpr = pE = sqlite3Expr(db, TK_INTEGER, 0); 798 798 if( pE==0 ) return 1; 799 799 pE->pColl = pColl; 800 800 pE->flags |= EP_IntValue | flags; 801 801 pE->u.iValue = iCol; 802 - pItem->iCol = (u16)iCol; 802 + pItem->iOrderByCol = (u16)iCol; 803 803 pItem->done = 1; 804 804 }else{ 805 805 moreToDo = 1; 806 806 } 807 807 } 808 808 pSelect = pSelect->pNext; 809 809 } ................................................................................ 844 844 sqlite3ErrorMsg(pParse, "too many terms in %s BY clause", zType); 845 845 return 1; 846 846 } 847 847 #endif 848 848 pEList = pSelect->pEList; 849 849 assert( pEList!=0 ); /* sqlite3SelectNew() guarantees this */ 850 850 for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){ 851 - if( pItem->iCol ){ 852 - if( pItem->iCol>pEList->nExpr ){ 851 + if( pItem->iOrderByCol ){ 852 + if( pItem->iOrderByCol>pEList->nExpr ){ 853 853 resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr); 854 854 return 1; 855 855 } 856 - resolveAlias(pParse, pEList, pItem->iCol-1, pItem->pExpr, zType); 856 + resolveAlias(pParse, pEList, pItem->iOrderByCol-1, pItem->pExpr, zType); 857 857 } 858 858 } 859 859 return 0; 860 860 } 861 861 862 862 /* 863 863 ** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect. ................................................................................ 896 896 Expr *pE = pItem->pExpr; 897 897 iCol = resolveAsName(pParse, pSelect->pEList, pE); 898 898 if( iCol>0 ){ 899 899 /* If an AS-name match is found, mark this ORDER BY column as being 900 900 ** a copy of the iCol-th result-set column. The subsequent call to 901 901 ** sqlite3ResolveOrderGroupBy() will convert the expression to a 902 902 ** copy of the iCol-th result-set expression. */ 903 - pItem->iCol = (u16)iCol; 903 + pItem->iOrderByCol = (u16)iCol; 904 904 continue; 905 905 } 906 906 if( sqlite3ExprIsInteger(pE, &iCol) ){ 907 907 /* The ORDER BY term is an integer constant. Again, set the column 908 908 ** number so that sqlite3ResolveOrderGroupBy() will convert the 909 909 ** order-by term to a copy of the result-set expression */ 910 910 if( iCol<1 ){ 911 911 resolveOutOfRangeError(pParse, zType, i+1, nResult); 912 912 return 1; 913 913 } 914 - pItem->iCol = (u16)iCol; 914 + pItem->iOrderByCol = (u16)iCol; 915 915 continue; 916 916 } 917 917 918 918 /* Otherwise, treat the ORDER BY term as an ordinary expression */ 919 - pItem->iCol = 0; 919 + pItem->iOrderByCol = 0; 920 920 if( sqlite3ResolveExprNames(pNC, pE) ){ 921 921 return 1; 922 922 } 923 923 } 924 924 return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType); 925 925 } 926 926
Changes to src/select.c.
2215 2215 ** the ORDER BY clause covers every term of the result set. Add 2216 2216 ** terms to the ORDER BY clause as necessary. 2217 2217 */ 2218 2218 if( op!=TK_ALL ){ 2219 2219 for(i=1; db->mallocFailed==0 && i<=p->pEList->nExpr; i++){ 2220 2220 struct ExprList_item *pItem; 2221 2221 for(j=0, pItem=pOrderBy->a; j<nOrderBy; j++, pItem++){ 2222 - assert( pItem->iCol>0 ); 2223 - if( pItem->iCol==i ) break; 2222 + assert( pItem->iOrderByCol>0 ); 2223 + if( pItem->iOrderByCol==i ) break; 2224 2224 } 2225 2225 if( j==nOrderBy ){ 2226 2226 Expr *pNew = sqlite3Expr(db, TK_INTEGER, 0); 2227 2227 if( pNew==0 ) return SQLITE_NOMEM; 2228 2228 pNew->flags |= EP_IntValue; 2229 2229 pNew->u.iValue = i; 2230 2230 pOrderBy = sqlite3ExprListAppend(pParse, pOrderBy, pNew); 2231 - pOrderBy->a[nOrderBy++].iCol = (u16)i; 2231 + pOrderBy->a[nOrderBy++].iOrderByCol = (u16)i; 2232 2232 } 2233 2233 } 2234 2234 } 2235 2235 2236 2236 /* Compute the comparison permutation and keyinfo that is used with 2237 2237 ** the permutation used to determine if the next 2238 2238 ** row of results comes from selectA or selectB. Also add explicit ................................................................................ 2240 2240 ** to the right and the left are evaluated, they use the correct 2241 2241 ** collation. 2242 2242 */ 2243 2243 aPermute = sqlite3DbMallocRaw(db, sizeof(int)*nOrderBy); 2244 2244 if( aPermute ){ 2245 2245 struct ExprList_item *pItem; 2246 2246 for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){ 2247 - assert( pItem->iCol>0 && pItem->iCol<=p->pEList->nExpr ); 2248 - aPermute[i] = pItem->iCol - 1; 2247 + assert( pItem->iOrderByCol>0 && pItem->iOrderByCol<=p->pEList->nExpr ); 2248 + aPermute[i] = pItem->iOrderByCol - 1; 2249 2249 } 2250 2250 pKeyMerge = 2251 2251 sqlite3DbMallocRaw(db, sizeof(*pKeyMerge)+nOrderBy*(sizeof(CollSeq*)+1)); 2252 2252 if( pKeyMerge ){ 2253 2253 pKeyMerge->aSortOrder = (u8*)&pKeyMerge->aColl[nOrderBy]; 2254 2254 pKeyMerge->nField = (u16)nOrderBy; 2255 2255 pKeyMerge->enc = ENC(db); ................................................................................ 2806 2806 } 2807 2807 if( isAgg || (p->selFlags & SF_Distinct)!=0 || pSrc->nSrc!=1 ){ 2808 2808 return 0; 2809 2809 } 2810 2810 for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){ 2811 2811 testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ); 2812 2812 testcase( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))==SF_Aggregate ); 2813 + assert( pSub->pSrc!=0 ); 2813 2814 if( (pSub1->selFlags & (SF_Distinct|SF_Aggregate))!=0 2814 2815 || (pSub1->pPrior && pSub1->op!=TK_ALL) 2815 - || NEVER(pSub1->pSrc==0) || pSub1->pSrc->nSrc<1 2816 + || pSub1->pSrc->nSrc<1 2816 2817 ){ 2817 2818 return 0; 2818 2819 } 2820 + testcase( pSub1->pSrc->nSrc>1 ); 2819 2821 } 2820 2822 2821 2823 /* Restriction 18. */ 2822 2824 if( p->pOrderBy ){ 2823 2825 int ii; 2824 2826 for(ii=0; ii<p->pOrderBy->nExpr; ii++){ 2825 - if( p->pOrderBy->a[ii].iCol==0 ) return 0; 2827 + if( p->pOrderBy->a[ii].iOrderByCol==0 ) return 0; 2826 2828 } 2827 2829 } 2828 2830 } 2829 2831 2830 2832 /***** If we reach this point, flattening is permitted. *****/ 2831 2833 2832 2834 /* Authorize the subquery */
Changes to src/sqliteInt.h.
1768 1768 int iECursor; /* VDBE Cursor associated with this ExprList */ 1769 1769 struct ExprList_item { 1770 1770 Expr *pExpr; /* The list of expressions */ 1771 1771 char *zName; /* Token associated with this expression */ 1772 1772 char *zSpan; /* Original text of the expression */ 1773 1773 u8 sortOrder; /* 1 for DESC or 0 for ASC */ 1774 1774 u8 done; /* A flag to indicate when processing is finished */ 1775 - u16 iCol; /* For ORDER BY, column number in result set */ 1775 + u16 iOrderByCol; /* For ORDER BY, column number in result set */ 1776 1776 u16 iAlias; /* Index into Parse.aAlias[] for zName */ 1777 1777 } *a; /* One entry for each expression */ 1778 1778 }; 1779 1779 1780 1780 /* 1781 1781 ** An instance of this structure is used by the parser to record both 1782 1782 ** the parse tree for an expression and the span of input text for an
Changes to test/selectB.test.
190 190 do_test selectB-3.0 { 191 191 execsql { 192 192 DROP INDEX i1; 193 193 DROP INDEX i2; 194 194 } 195 195 } {} 196 196 197 -for {set ii 3} {$ii <= 4} {incr ii} { 197 +for {set ii 3} {$ii <= 6} {incr ii} { 198 198 199 - if {$ii == 4} { 200 - do_test selectB-4.0 { 201 - execsql { 202 - CREATE INDEX i1 ON t1(a); 203 - CREATE INDEX i2 ON t1(b); 204 - CREATE INDEX i3 ON t1(c); 205 - CREATE INDEX i4 ON t2(d); 206 - CREATE INDEX i5 ON t2(e); 207 - CREATE INDEX i6 ON t2(f); 208 - } 209 - } {} 199 + switch $ii { 200 + 4 { 201 + optimization_control db query-flattener off 202 + } 203 + 5 { 204 + optimization_control db query-flattener on 205 + do_test selectB-5.0 { 206 + execsql { 207 + CREATE INDEX i1 ON t1(a); 208 + CREATE INDEX i2 ON t1(b); 209 + CREATE INDEX i3 ON t1(c); 210 + CREATE INDEX i4 ON t2(d); 211 + CREATE INDEX i5 ON t2(e); 212 + CREATE INDEX i6 ON t2(f); 213 + } 214 + } {} 215 + } 216 + 6 { 217 + optimization_control db query-flattener off 218 + } 210 219 } 211 220 212 221 do_test selectB-$ii.1 { 213 222 execsql { 214 223 SELECT DISTINCT * FROM 215 224 (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 216 225 ORDER BY 1; ................................................................................ 367 376 368 377 do_test selectB-$ii.21 { 369 378 execsql { 370 379 SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b 371 380 } 372 381 } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27} 373 382 374 - do_test selectB-$ii.21 { 383 + do_test selectB-$ii.22 { 375 384 execsql { 376 385 SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1; 377 386 } 378 387 } {3 12 21 345} 388 + 389 + do_test selectB-$ii.23 { 390 + execsql { 391 + SELECT x, y FROM ( 392 + SELECT a AS x, b AS y FROM t1 393 + UNION ALL 394 + SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d) 395 + UNION ALL 396 + SELECT a*100, b*100 FROM t1 397 + ) ORDER BY 1; 398 + } 399 + } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600} 400 + 401 + do_test selectB-$ii.24 { 402 + execsql { 403 + SELECT x, y FROM ( 404 + SELECT a AS x, b AS y FROM t1 405 + UNION ALL 406 + SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d) 407 + UNION ALL 408 + SELECT a*100, b*100 FROM t1 409 + ) ORDER BY 1; 410 + } 411 + } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600} 412 + 413 + do_test selectB-$ii.25 { 414 + execsql { 415 + SELECT x+y FROM ( 416 + SELECT a AS x, b AS y FROM t1 417 + UNION ALL 418 + SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d) 419 + UNION ALL 420 + SELECT a*100, b*100 FROM t1 421 + ) WHERE y+x NOT NULL ORDER BY 1; 422 + } 423 + } {6 18 30 260.2 600 1800 3000} 379 424 } 380 425 381 426 finish_test