/ Check-in [3d0bd95e]
Login

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

Overview
Comment:Improved substitution logic in the query flattener. Saves code space, and (more importantly) works correctly with table-valued functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3d0bd95e977db50c314d33ec292f99e0539d7b4a
User & Date: drh 2015-10-11 19:46:59
Context
2015-10-11
20:08
Remove an unreachable branch in the query flattener substitution logic. check-in: 46ee3453 user: drh tags: trunk
19:46
Improved substitution logic in the query flattener. Saves code space, and (more importantly) works correctly with table-valued functions. check-in: 3d0bd95e user: drh tags: trunk
2015-10-10
16:41
Work around a "security feature" bug in memcpy() on OpenBSD. check-in: fab6f090 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  3078   3078     return pParse->nErr!=0;
  3079   3079   }
  3080   3080   #endif
  3081   3081   
  3082   3082   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  3083   3083   /* Forward Declarations */
  3084   3084   static void substExprList(sqlite3*, ExprList*, int, ExprList*);
  3085         -static void substSelect(sqlite3*, Select *, int, ExprList *);
         3085  +static void substSelect(sqlite3*, Select *, int, ExprList*, int);
  3086   3086   
  3087   3087   /*
  3088   3088   ** Scan through the expression pExpr.  Replace every reference to
  3089   3089   ** a column in table number iTable with a copy of the iColumn-th
  3090   3090   ** entry in pEList.  (But leave references to the ROWID column 
  3091   3091   ** unchanged.)
  3092   3092   **
................................................................................
  3115   3115         sqlite3ExprDelete(db, pExpr);
  3116   3116         pExpr = pNew;
  3117   3117       }
  3118   3118     }else{
  3119   3119       pExpr->pLeft = substExpr(db, pExpr->pLeft, iTable, pEList);
  3120   3120       pExpr->pRight = substExpr(db, pExpr->pRight, iTable, pEList);
  3121   3121       if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  3122         -      substSelect(db, pExpr->x.pSelect, iTable, pEList);
         3122  +      substSelect(db, pExpr->x.pSelect, iTable, pEList, 1);
  3123   3123       }else{
  3124   3124         substExprList(db, pExpr->x.pList, iTable, pEList);
  3125   3125       }
  3126   3126     }
  3127   3127     return pExpr;
  3128   3128   }
  3129   3129   static void substExprList(
................................................................................
  3138   3138       pList->a[i].pExpr = substExpr(db, pList->a[i].pExpr, iTable, pEList);
  3139   3139     }
  3140   3140   }
  3141   3141   static void substSelect(
  3142   3142     sqlite3 *db,         /* Report malloc errors here */
  3143   3143     Select *p,           /* SELECT statement in which to make substitutions */
  3144   3144     int iTable,          /* Table to be replaced */
  3145         -  ExprList *pEList     /* Substitute values */
         3145  +  ExprList *pEList,    /* Substitute values */
         3146  +  int doPrior          /* Do substitutes on p->pPrior too */
  3146   3147   ){
  3147   3148     SrcList *pSrc;
  3148   3149     struct SrcList_item *pItem;
  3149   3150     int i;
  3150   3151     if( !p ) return;
  3151         -  substExprList(db, p->pEList, iTable, pEList);
  3152         -  substExprList(db, p->pGroupBy, iTable, pEList);
  3153         -  substExprList(db, p->pOrderBy, iTable, pEList);
  3154         -  p->pHaving = substExpr(db, p->pHaving, iTable, pEList);
  3155         -  p->pWhere = substExpr(db, p->pWhere, iTable, pEList);
  3156         -  substSelect(db, p->pPrior, iTable, pEList);
  3157         -  pSrc = p->pSrc;
  3158         -  assert( pSrc );  /* Even for (SELECT 1) we have: pSrc!=0 but pSrc->nSrc==0 */
  3159         -  if( ALWAYS(pSrc) ){
  3160         -    for(i=pSrc->nSrc, pItem=pSrc->a; i>0; i--, pItem++){
  3161         -      substSelect(db, pItem->pSelect, iTable, pEList);
         3152  +  do{
         3153  +    substExprList(db, p->pEList, iTable, pEList);
         3154  +    substExprList(db, p->pGroupBy, iTable, pEList);
         3155  +    substExprList(db, p->pOrderBy, iTable, pEList);
         3156  +    p->pHaving = substExpr(db, p->pHaving, iTable, pEList);
         3157  +    p->pWhere = substExpr(db, p->pWhere, iTable, pEList);
         3158  +    pSrc = p->pSrc;
         3159  +    if( pSrc ){
         3160  +      for(i=pSrc->nSrc, pItem=pSrc->a; i>0; i--, pItem++){
         3161  +        substSelect(db, pItem->pSelect, iTable, pEList, 1);
         3162  +        if( pItem->fg.isTabFunc ){
         3163  +          substExprList(db, pItem->u1.pFuncArg, iTable, pEList);
         3164  +        }
         3165  +      }
  3162   3166       }
  3163         -  }
         3167  +  }while( doPrior && (p = p->pPrior)!=0 );
  3164   3168   }
  3165   3169   #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
  3166   3170   
  3167   3171   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  3168   3172   /*
  3169   3173   ** This routine attempts to flatten subqueries as a performance optimization.
  3170   3174   ** This routine returns 1 if it makes changes and 0 if no flattening occurs.
................................................................................
  3308   3312     Parse *pParse,       /* Parsing context */
  3309   3313     Select *p,           /* The parent or outer SELECT statement */
  3310   3314     int iFrom,           /* Index in p->pSrc->a[] of the inner subquery */
  3311   3315     int isAgg,           /* True if outer SELECT uses aggregate functions */
  3312   3316     int subqueryIsAgg    /* True if the subquery uses aggregate functions */
  3313   3317   ){
  3314   3318     const char *zSavedAuthContext = pParse->zAuthContext;
  3315         -  Select *pParent;
         3319  +  Select *pParent;    /* Current UNION ALL term of the other query */
  3316   3320     Select *pSub;       /* The inner query or "subquery" */
  3317   3321     Select *pSub1;      /* Pointer to the rightmost select in sub-query */
  3318   3322     SrcList *pSrc;      /* The FROM clause of the outer query */
  3319   3323     SrcList *pSubSrc;   /* The FROM clause of the subquery */
  3320   3324     ExprList *pList;    /* The result set of the outer query */
  3321   3325     int iParent;        /* VDBE cursor number of the pSub result set temp table */
  3322   3326     int i;              /* Loop counter */
................................................................................
  3603   3607       **
  3604   3608       ** Example:
  3605   3609       **
  3606   3610       **    SELECT * FROM tabA, (SELECT * FROM sub1, sub2), tabB;
  3607   3611       **
  3608   3612       ** The outer query has 3 slots in its FROM clause.  One slot of the
  3609   3613       ** outer query (the middle slot) is used by the subquery.  The next
  3610         -    ** block of code will expand the out query to 4 slots.  The middle
  3611         -    ** slot is expanded to two slots in order to make space for the
  3612         -    ** two elements in the FROM clause of the subquery.
         3614  +    ** block of code will expand the outer query FROM clause to 4 slots.
         3615  +    ** The middle slot is expanded to two slots in order to make space
         3616  +    ** for the two elements in the FROM clause of the subquery.
  3613   3617       */
  3614   3618       if( nSubSrc>1 ){
  3615   3619         pParent->pSrc = pSrc = sqlite3SrcListEnlarge(db, pSrc, nSubSrc-1,iFrom+1);
  3616   3620         if( db->mallocFailed ){
  3617   3621           break;
  3618   3622         }
  3619   3623       }
................................................................................
  3644   3648       for(i=0; i<pList->nExpr; i++){
  3645   3649         if( pList->a[i].zName==0 ){
  3646   3650           char *zName = sqlite3DbStrDup(db, pList->a[i].zSpan);
  3647   3651           sqlite3Dequote(zName);
  3648   3652           pList->a[i].zName = zName;
  3649   3653         }
  3650   3654       }
  3651         -    substExprList(db, pParent->pEList, iParent, pSub->pEList);
  3652         -    if( isAgg ){
  3653         -      substExprList(db, pParent->pGroupBy, iParent, pSub->pEList);
  3654         -      pParent->pHaving = substExpr(db, pParent->pHaving, iParent, pSub->pEList);
  3655         -    }
  3656   3655       if( pSub->pOrderBy ){
  3657   3656         /* At this point, any non-zero iOrderByCol values indicate that the
  3658   3657         ** ORDER BY column expression is identical to the iOrderByCol'th
  3659   3658         ** expression returned by SELECT statement pSub. Since these values
  3660   3659         ** do not necessarily correspond to columns in SELECT statement pParent,
  3661   3660         ** zero them before transfering the ORDER BY clause.
  3662   3661         **
................................................................................
  3668   3667         for(i=0; i<pOrderBy->nExpr; i++){
  3669   3668           pOrderBy->a[i].u.x.iOrderByCol = 0;
  3670   3669         }
  3671   3670         assert( pParent->pOrderBy==0 );
  3672   3671         assert( pSub->pPrior==0 );
  3673   3672         pParent->pOrderBy = pOrderBy;
  3674   3673         pSub->pOrderBy = 0;
  3675         -    }else if( pParent->pOrderBy ){
  3676         -      substExprList(db, pParent->pOrderBy, iParent, pSub->pEList);
  3677   3674       }
  3678         -    if( pSub->pWhere ){
  3679         -      pWhere = sqlite3ExprDup(db, pSub->pWhere, 0);
  3680         -    }else{
  3681         -      pWhere = 0;
  3682         -    }
         3675  +    pWhere = sqlite3ExprDup(db, pSub->pWhere, 0);
  3683   3676       if( subqueryIsAgg ){
  3684   3677         assert( pParent->pHaving==0 );
  3685   3678         pParent->pHaving = pParent->pWhere;
  3686   3679         pParent->pWhere = pWhere;
  3687         -      pParent->pHaving = substExpr(db, pParent->pHaving, iParent, pSub->pEList);
  3688   3680         pParent->pHaving = sqlite3ExprAnd(db, pParent->pHaving, 
  3689   3681                                     sqlite3ExprDup(db, pSub->pHaving, 0));
  3690   3682         assert( pParent->pGroupBy==0 );
  3691   3683         pParent->pGroupBy = sqlite3ExprListDup(db, pSub->pGroupBy, 0);
  3692   3684       }else{
  3693         -      pParent->pWhere = substExpr(db, pParent->pWhere, iParent, pSub->pEList);
  3694   3685         pParent->pWhere = sqlite3ExprAnd(db, pParent->pWhere, pWhere);
  3695   3686       }
         3687  +    substSelect(db, pParent, iParent, pSub->pEList, 0);
  3696   3688     
  3697   3689       /* The flattened query is distinct if either the inner or the
  3698   3690       ** outer query is distinct. 
  3699   3691       */
  3700   3692       pParent->selFlags |= pSub->selFlags & SF_Distinct;
  3701   3693     
  3702   3694       /*

Changes to test/tabfunc01.test.

    71     71   } {1 {'v2' is not a function}}
    72     72   
    73     73   do_execsql_test tabfunc01-2.1 {
    74     74     CREATE TABLE t1(x);
    75     75     INSERT INTO t1(x) VALUES(2),(3);
    76     76     SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
    77     77   } {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
    78         -
    79     78   do_execsql_test tabfunc01-2.2 {
           79  +  SELECT *, '|' FROM (SELECT x FROM t1) AS y, generate_series(1,y.x)
           80  +  ORDER BY 1, 2;
           81  +} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
           82  +
           83  +do_execsql_test tabfunc01-2.50 {
    80     84     SELECT * FROM generate_series() LIMIT 5;
    81     85   } {0 1 2 3 4}
    82     86   
    83     87   do_execsql_test tabfunc01-3.1 {
    84     88     SELECT DISTINCT value FROM generate_series(1,x), t1 ORDER BY 1;
    85     89   } {1 2 3}
    86     90