/ Check-in [5061d85f]
Login

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: 5061d85ff934db3c217c97acbbbed3286d9e02aa
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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