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