/ Check-in [433d16ff]
Login

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

Overview
Comment:Prevent the flattening or where-term push-down optimizations from obscuring misuses of SQL row values that can lead to crashes or assert() failures.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 433d16ff3adfede3be53d5b0e0512f37e225591b
User & Date: dan 2016-12-07 15:38:37
Context
2016-12-07
15:49
Rename the SQLITE_OMIT_BUILTIN_TEST compile-time option to SQLITE_UNTESTABLE. check-in: f3608187 user: drh tags: trunk
15:38
Prevent the flattening or where-term push-down optimizations from obscuring misuses of SQL row values that can lead to crashes or assert() failures. check-in: 433d16ff user: dan tags: trunk
13:49
Always honor the sqlite3.dbOptFlags bitmask, regardless of compile-time options. Continuing fix for ticket [da78413751863]. check-in: afab1663 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2350   2350   **   "sub-select returns N columns - expected M"
  2351   2351   */   
  2352   2352   void sqlite3SubselectError(Parse *pParse, int nActual, int nExpect){
  2353   2353     const char *zFmt = "sub-select returns %d columns - expected %d";
  2354   2354     sqlite3ErrorMsg(pParse, zFmt, nActual, nExpect);
  2355   2355   }
  2356   2356   #endif
         2357  +
         2358  +/*
         2359  +** Expression pExpr is a vector that has been used in a context where
         2360  +** it is not permitted. If pExpr is a sub-select vector, this routine 
         2361  +** loads the Parse object with a message of the form:
         2362  +**
         2363  +**   "sub-select returns N columns - expected 1"
         2364  +**
         2365  +** Or, if it is a regular scalar vector:
         2366  +**
         2367  +**   "row value misused"
         2368  +*/   
         2369  +void sqlite3VectorErrorMsg(Parse *pParse, Expr *pExpr){
         2370  +#ifndef SQLITE_OMIT_SUBQUERY
         2371  +  if( pExpr->flags & EP_xIsSelect ){
         2372  +    sqlite3SubselectError(pParse, pExpr->x.pSelect->pEList->nExpr, 1);
         2373  +  }else
         2374  +#endif
         2375  +  {
         2376  +    sqlite3ErrorMsg(pParse, "row value misused");
         2377  +  }
         2378  +}
  2357   2379   
  2358   2380   /*
  2359   2381   ** Generate code for scalar subqueries used as a subquery expression, EXISTS,
  2360   2382   ** or IN operators.  Examples:
  2361   2383   **
  2362   2384   **     (SELECT a FROM b)          -- subquery
  2363   2385   **     EXISTS (SELECT a FROM b)   -- EXISTS subquery
................................................................................
  2633   2655     int nVector = sqlite3ExprVectorSize(pIn->pLeft);
  2634   2656     if( (pIn->flags & EP_xIsSelect) ){
  2635   2657       if( nVector!=pIn->x.pSelect->pEList->nExpr ){
  2636   2658         sqlite3SubselectError(pParse, pIn->x.pSelect->pEList->nExpr, nVector);
  2637   2659         return 1;
  2638   2660       }
  2639   2661     }else if( nVector!=1 ){
  2640         -    if( (pIn->pLeft->flags & EP_xIsSelect) ){
  2641         -      sqlite3SubselectError(pParse, nVector, 1);
  2642         -    }else{
  2643         -      sqlite3ErrorMsg(pParse, "row value misused");
  2644         -    }
         2662  +    sqlite3VectorErrorMsg(pParse, pIn->pLeft);
  2645   2663       return 1;
  2646   2664     }
  2647   2665     return 0;
  2648   2666   }
  2649   2667   #endif
  2650   2668   
  2651   2669   #ifndef SQLITE_OMIT_SUBQUERY

Changes to src/select.c.

  3129   3129     explainComposite(pParse, p->op, iSub1, iSub2, 0);
  3130   3130     return pParse->nErr!=0;
  3131   3131   }
  3132   3132   #endif
  3133   3133   
  3134   3134   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  3135   3135   /* Forward Declarations */
  3136         -static void substExprList(sqlite3*, ExprList*, int, ExprList*);
  3137         -static void substSelect(sqlite3*, Select *, int, ExprList*, int);
         3136  +static void substExprList(Parse*, ExprList*, int, ExprList*);
         3137  +static void substSelect(Parse*, Select *, int, ExprList*, int);
  3138   3138   
  3139   3139   /*
  3140   3140   ** Scan through the expression pExpr.  Replace every reference to
  3141   3141   ** a column in table number iTable with a copy of the iColumn-th
  3142   3142   ** entry in pEList.  (But leave references to the ROWID column 
  3143   3143   ** unchanged.)
  3144   3144   **
................................................................................
  3146   3146   ** whose result set is defined by pEList appears as entry in the
  3147   3147   ** FROM clause of a SELECT such that the VDBE cursor assigned to that
  3148   3148   ** FORM clause entry is iTable.  This routine make the necessary 
  3149   3149   ** changes to pExpr so that it refers directly to the source table
  3150   3150   ** of the subquery rather the result set of the subquery.
  3151   3151   */
  3152   3152   static Expr *substExpr(
  3153         -  sqlite3 *db,        /* Report malloc errors to this connection */
         3153  +  Parse *pParse,      /* Report errors here */
  3154   3154     Expr *pExpr,        /* Expr in which substitution occurs */
  3155   3155     int iTable,         /* Table to be substituted */
  3156   3156     ExprList *pEList    /* Substitute expressions */
  3157   3157   ){
         3158  +  sqlite3 *db = pParse->db;
  3158   3159     if( pExpr==0 ) return 0;
  3159   3160     if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable ){
  3160   3161       if( pExpr->iColumn<0 ){
  3161   3162         pExpr->op = TK_NULL;
  3162   3163       }else{
  3163   3164         Expr *pNew;
         3165  +      Expr *pCopy = pEList->a[pExpr->iColumn].pExpr;
  3164   3166         assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
  3165   3167         assert( pExpr->pLeft==0 && pExpr->pRight==0 );
  3166         -      pNew = sqlite3ExprDup(db, pEList->a[pExpr->iColumn].pExpr, 0);
  3167         -      if( pNew && (pExpr->flags & EP_FromJoin) ){
  3168         -        pNew->iRightJoinTable = pExpr->iRightJoinTable;
  3169         -        pNew->flags |= EP_FromJoin;
         3168  +      if( sqlite3ExprIsVector(pCopy) ){
         3169  +        sqlite3VectorErrorMsg(pParse, pCopy);
         3170  +      }else{
         3171  +        pNew = sqlite3ExprDup(db, pCopy, 0);
         3172  +        if( pNew && (pExpr->flags & EP_FromJoin) ){
         3173  +          pNew->iRightJoinTable = pExpr->iRightJoinTable;
         3174  +          pNew->flags |= EP_FromJoin;
         3175  +        }
         3176  +        sqlite3ExprDelete(db, pExpr);
         3177  +        pExpr = pNew;
  3170   3178         }
  3171         -      sqlite3ExprDelete(db, pExpr);
  3172         -      pExpr = pNew;
  3173   3179       }
  3174   3180     }else{
  3175         -    pExpr->pLeft = substExpr(db, pExpr->pLeft, iTable, pEList);
  3176         -    pExpr->pRight = substExpr(db, pExpr->pRight, iTable, pEList);
         3181  +    pExpr->pLeft = substExpr(pParse, pExpr->pLeft, iTable, pEList);
         3182  +    pExpr->pRight = substExpr(pParse, pExpr->pRight, iTable, pEList);
  3177   3183       if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  3178         -      substSelect(db, pExpr->x.pSelect, iTable, pEList, 1);
         3184  +      substSelect(pParse, pExpr->x.pSelect, iTable, pEList, 1);
  3179   3185       }else{
  3180         -      substExprList(db, pExpr->x.pList, iTable, pEList);
         3186  +      substExprList(pParse, pExpr->x.pList, iTable, pEList);
  3181   3187       }
  3182   3188     }
  3183   3189     return pExpr;
  3184   3190   }
  3185   3191   static void substExprList(
  3186         -  sqlite3 *db,         /* Report malloc errors here */
         3192  +  Parse *pParse,       /* Report errors here */
  3187   3193     ExprList *pList,     /* List to scan and in which to make substitutes */
  3188   3194     int iTable,          /* Table to be substituted */
  3189   3195     ExprList *pEList     /* Substitute values */
  3190   3196   ){
  3191   3197     int i;
  3192   3198     if( pList==0 ) return;
  3193   3199     for(i=0; i<pList->nExpr; i++){
  3194         -    pList->a[i].pExpr = substExpr(db, pList->a[i].pExpr, iTable, pEList);
         3200  +    pList->a[i].pExpr = substExpr(pParse, pList->a[i].pExpr, iTable, pEList);
  3195   3201     }
  3196   3202   }
  3197   3203   static void substSelect(
  3198         -  sqlite3 *db,         /* Report malloc errors here */
         3204  +  Parse *pParse,       /* Report errors here */
  3199   3205     Select *p,           /* SELECT statement in which to make substitutions */
  3200   3206     int iTable,          /* Table to be replaced */
  3201   3207     ExprList *pEList,    /* Substitute values */
  3202   3208     int doPrior          /* Do substitutes on p->pPrior too */
  3203   3209   ){
  3204   3210     SrcList *pSrc;
  3205   3211     struct SrcList_item *pItem;
  3206   3212     int i;
  3207   3213     if( !p ) return;
  3208   3214     do{
  3209         -    substExprList(db, p->pEList, iTable, pEList);
  3210         -    substExprList(db, p->pGroupBy, iTable, pEList);
  3211         -    substExprList(db, p->pOrderBy, iTable, pEList);
  3212         -    p->pHaving = substExpr(db, p->pHaving, iTable, pEList);
  3213         -    p->pWhere = substExpr(db, p->pWhere, iTable, pEList);
         3215  +    substExprList(pParse, p->pEList, iTable, pEList);
         3216  +    substExprList(pParse, p->pGroupBy, iTable, pEList);
         3217  +    substExprList(pParse, p->pOrderBy, iTable, pEList);
         3218  +    p->pHaving = substExpr(pParse, p->pHaving, iTable, pEList);
         3219  +    p->pWhere = substExpr(pParse, p->pWhere, iTable, pEList);
  3214   3220       pSrc = p->pSrc;
  3215   3221       assert( pSrc!=0 );
  3216   3222       for(i=pSrc->nSrc, pItem=pSrc->a; i>0; i--, pItem++){
  3217         -      substSelect(db, pItem->pSelect, iTable, pEList, 1);
         3223  +      substSelect(pParse, pItem->pSelect, iTable, pEList, 1);
  3218   3224         if( pItem->fg.isTabFunc ){
  3219         -        substExprList(db, pItem->u1.pFuncArg, iTable, pEList);
         3225  +        substExprList(pParse, pItem->u1.pFuncArg, iTable, pEList);
  3220   3226         }
  3221   3227       }
  3222   3228     }while( doPrior && (p = p->pPrior)!=0 );
  3223   3229   }
  3224   3230   #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
  3225   3231   
  3226   3232   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
................................................................................
  3737   3743             sqlite3ExprDup(db, pSub->pHaving, 0), pParent->pHaving
  3738   3744         );
  3739   3745         assert( pParent->pGroupBy==0 );
  3740   3746         pParent->pGroupBy = sqlite3ExprListDup(db, pSub->pGroupBy, 0);
  3741   3747       }else{
  3742   3748         pParent->pWhere = sqlite3ExprAnd(db, pWhere, pParent->pWhere);
  3743   3749       }
  3744         -    substSelect(db, pParent, iParent, pSub->pEList, 0);
         3750  +    substSelect(pParse, pParent, iParent, pSub->pEList, 0);
  3745   3751     
  3746   3752       /* The flattened query is distinct if either the inner or the
  3747   3753       ** outer query is distinct. 
  3748   3754       */
  3749   3755       pParent->selFlags |= pSub->selFlags & SF_Distinct;
  3750   3756     
  3751   3757       /*
................................................................................
  3811   3817   **   (5) The WHERE clause expression originates in the ON or USING clause
  3812   3818   **       of a LEFT JOIN.
  3813   3819   **
  3814   3820   ** Return 0 if no changes are made and non-zero if one or more WHERE clause
  3815   3821   ** terms are duplicated into the subquery.
  3816   3822   */
  3817   3823   static int pushDownWhereTerms(
  3818         -  sqlite3 *db,          /* The database connection (for malloc()) */
         3824  +  Parse *pParse,        /* Parse context (for malloc() and error reporting) */
  3819   3825     Select *pSubq,        /* The subquery whose WHERE clause is to be augmented */
  3820   3826     Expr *pWhere,         /* The WHERE clause of the outer query */
  3821   3827     int iCursor           /* Cursor number of the subquery */
  3822   3828   ){
  3823   3829     Expr *pNew;
  3824   3830     int nChng = 0;
  3825   3831     Select *pX;           /* For looping over compound SELECTs in pSubq */
................................................................................
  3832   3838         return 0; /* restrictions (1) and (2) */
  3833   3839       }
  3834   3840     }
  3835   3841     if( pSubq->pLimit!=0 ){
  3836   3842       return 0; /* restriction (3) */
  3837   3843     }
  3838   3844     while( pWhere->op==TK_AND ){
  3839         -    nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
         3845  +    nChng += pushDownWhereTerms(pParse, pSubq, pWhere->pRight, iCursor);
  3840   3846       pWhere = pWhere->pLeft;
  3841   3847     }
  3842   3848     if( ExprHasProperty(pWhere,EP_FromJoin) ) return 0; /* restriction 5 */
  3843   3849     if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
  3844   3850       nChng++;
  3845   3851       while( pSubq ){
  3846         -      pNew = sqlite3ExprDup(db, pWhere, 0);
  3847         -      pNew = substExpr(db, pNew, iCursor, pSubq->pEList);
  3848         -      pSubq->pWhere = sqlite3ExprAnd(db, pSubq->pWhere, pNew);
         3852  +      pNew = sqlite3ExprDup(pParse->db, pWhere, 0);
         3853  +      pNew = substExpr(pParse, pNew, iCursor, pSubq->pEList);
         3854  +      pSubq->pWhere = sqlite3ExprAnd(pParse->db, pSubq->pWhere, pNew);
  3849   3855         pSubq = pSubq->pPrior;
  3850   3856       }
  3851   3857     }
  3852   3858     return nChng;
  3853   3859   }
  3854   3860   #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
  3855   3861   
................................................................................
  4998   5004       */
  4999   5005       pParse->nHeight += sqlite3SelectExprHeight(p);
  5000   5006   
  5001   5007       /* Make copies of constant WHERE-clause terms in the outer query down
  5002   5008       ** inside the subquery.  This can help the subquery to run more efficiently.
  5003   5009       */
  5004   5010       if( (pItem->fg.jointype & JT_OUTER)==0
  5005         -     && pushDownWhereTerms(db, pSub, p->pWhere, pItem->iCursor)
         5011  +     && pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor)
  5006   5012       ){
  5007   5013   #if SELECTTRACE_ENABLED
  5008   5014         if( sqlite3SelectTrace & 0x100 ){
  5009   5015           SELECTTRACE(0x100,pParse,p,("After WHERE-clause push-down:\n"));
  5010   5016           sqlite3TreeViewSelect(0, p, 0);
  5011   5017         }
  5012   5018   #endif

Changes to src/sqliteInt.h.

  4282   4282   int sqlite3DbstatRegister(sqlite3*);
  4283   4283   #endif
  4284   4284   
  4285   4285   int sqlite3ExprVectorSize(Expr *pExpr);
  4286   4286   int sqlite3ExprIsVector(Expr *pExpr);
  4287   4287   Expr *sqlite3VectorFieldSubexpr(Expr*, int);
  4288   4288   Expr *sqlite3ExprForVectorField(Parse*,Expr*,int);
         4289  +void sqlite3VectorErrorMsg(Parse*, Expr*);
  4289   4290   
  4290   4291   #endif /* SQLITEINT_H */

Changes to test/rowvalue.test.

   286    286   do_execsql_test 14.2 "SELECT CASE (2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
   287    287   do_execsql_test 14.3 "SELECT CASE (SELECT 2,2) WHEN (1, 1) THEN 2 ELSE 1 END" 1
   288    288   do_execsql_test 14.4 "SELECT 1 WHERE (SELECT 2,2) BETWEEN (1,1) AND (3,3)" 1
   289    289   do_execsql_test 14.5 "SELECT 1 FROM t12 WHERE (x,1) BETWEEN (1,1) AND (3,3)" 1
   290    290   do_execsql_test 14.6 {
   291    291     SELECT 1 FROM t12 WHERE (1,x) BETWEEN (1,1) AND (3,3)
   292    292   } {1 1}
          293  +
          294  +#-------------------------------------------------------------------------
          295  +# Test that errors are not concealed by the SELECT flattening or
          296  +# WHERE-clause push-down optimizations.
          297  +do_execsql_test 14.1 {
          298  +  CREATE TABLE x1(a PRIMARY KEY, b);
          299  +  CREATE TABLE x2(a INTEGER PRIMARY KEY, b);
          300  +}
          301  +
          302  +foreach {tn n sql} {
          303  +  1 0 "SELECT * FROM (SELECT (1, 1) AS c FROM x1) WHERE c=1"
          304  +  2 2 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9) AS y) WHERE y<1"
          305  +  3 3 "SELECT * FROM (SELECT 1 AS x, (SELECT 8,9,10) AS y) WHERE y<1"
          306  +  4 0 "SELECT * FROM (SELECT (a, b) AS c FROM x1), x2 WHERE c=a"
          307  +  5 0 "SELECT * FROM (SELECT a AS c, (1, 2, 3) FROM x1), x2 WHERE c=a"
          308  +  6 0 "SELECT * FROM (SELECT 1 AS c, (1, 2, 3) FROM x1) WHERE c=1"
          309  +} {
          310  +  if {$n==0} {
          311  +    set err "row value misused"
          312  +  } else {
          313  +    set err "sub-select returns $n columns - expected 1"
          314  +  }
          315  +  do_catchsql_test 14.2.$tn $sql [list 1 $err]
          316  +}
          317  +
   293    318   
   294    319   finish_test
   295    320   
   296    321