/ Check-in [c053448a]
Login

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

Overview
Comment:Propagate the COLLATE operator upward through function calls. Initial fix for ticket [ca0d20b6cdddec5e8].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-2f7170d7
Files: files | file ages | folders
SHA1: c053448a55f9d030e8ffe88cf4fc14ada7f6ec19
User & Date: drh 2015-02-09 16:09:34
Context
2015-02-09
16:34
Add test cases for the query flattener fix for ticket [2f7170d73bf9abf8]. Closed-Leaf check-in: dd8f7f75 user: drh tags: tkt-2f7170d7
16:09
Propagate the COLLATE operator upward through function calls. Initial fix for ticket [ca0d20b6cdddec5e8]. check-in: c053448a user: drh tags: tkt-2f7170d7
15:21
Disable the query flattener for aggregate subqueries if the parent query uses other subqueries in its result set or WHERE clause or ORDER BY clause. Preliminary fix for ticket [2f7170d73bf9abf8]. However it still contains a defect similar to the COLLATE problem of [ca0d20b6cddd]. check-in: 0b7d65e3 user: drh tags: tkt-2f7170d7
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   185    185     pWhereRowid = sqlite3PExpr(pParse, TK_ROW, 0, 0, 0);
   186    186     if( pWhereRowid == 0 ) goto limit_where_cleanup_1;
   187    187     pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0);
   188    188     if( pInClause == 0 ) goto limit_where_cleanup_1;
   189    189   
   190    190     pInClause->x.pSelect = pSelect;
   191    191     pInClause->flags |= EP_xIsSelect;
   192         -  sqlite3ExprSetHeight(pParse, pInClause);
          192  +  sqlite3ExprSetHeightAndFlags(pParse, pInClause);
   193    193     return pInClause;
   194    194   
   195    195     /* something went wrong. clean up anything allocated. */
   196    196   limit_where_cleanup_1:
   197    197     sqlite3SelectDelete(pParse->db, pSelect);
   198    198     return 0;
   199    199   

Changes to src/expr.c.

   142    142         if( j>=0 ){
   143    143           const char *zColl = p->pTab->aCol[j].zColl;
   144    144           pColl = sqlite3FindCollSeq(db, ENC(db), zColl, 0);
   145    145         }
   146    146         break;
   147    147       }
   148    148       if( p->flags & EP_Collate ){
   149         -      if( ALWAYS(p->pLeft) && (p->pLeft->flags & EP_Collate)!=0 ){
          149  +      if( p->pLeft && (p->pLeft->flags & EP_Collate)!=0 ){
   150    150           p = p->pLeft;
   151    151         }else{
   152         -        p = p->pRight;
          152  +        Expr *pNext  = p->pRight;
          153  +        if( p->x.pList!=0 && !ExprHasProperty(p, EP_xIsSelect) ){
          154  +          int i;
          155  +          for(i=0; i<p->x.pList->nExpr; i++){
          156  +            if( ExprHasProperty(p->x.pList->a[i].pExpr, EP_Collate) ){
          157  +              pNext = p->x.pList->a[i].pExpr;
          158  +              break;
          159  +            }
          160  +          }
          161  +        }
          162  +        p = pNext;
   153    163         }
   154    164       }else{
   155    165         break;
   156    166       }
   157    167     }
   158    168     if( sqlite3CheckCollSeq(pParse, pColl) ){ 
   159    169       pColl = 0;
................................................................................
   351    361   
   352    362   /*
   353    363   ** Set the Expr.nHeight variable in the structure passed as an 
   354    364   ** argument. An expression with no children, Expr.pList or 
   355    365   ** Expr.pSelect member has a height of 1. Any other expression
   356    366   ** has a height equal to the maximum height of any other 
   357    367   ** referenced Expr plus one.
          368  +**
          369  +** Also propagate EP_Propagate flags up from Expr.x.pList to Expr.flags,
          370  +** if appropriate.
   358    371   */
   359    372   static void exprSetHeight(Expr *p){
   360    373     int nHeight = 0;
   361    374     heightOfExpr(p->pLeft, &nHeight);
   362    375     heightOfExpr(p->pRight, &nHeight);
   363    376     if( ExprHasProperty(p, EP_xIsSelect) ){
   364    377       heightOfSelect(p->x.pSelect, &nHeight);
   365         -  }else{
          378  +  }else if( p->x.pList ){
   366    379       heightOfExprList(p->x.pList, &nHeight);
          380  +    p->flags |= EP_Propagate & sqlite3ExprListFlags(p->x.pList);
   367    381     }
   368    382     p->nHeight = nHeight + 1;
   369    383   }
   370    384   
   371    385   /*
   372    386   ** Set the Expr.nHeight variable using the exprSetHeight() function. If
   373    387   ** the height is greater than the maximum allowed expression depth,
   374    388   ** leave an error in pParse.
          389  +**
          390  +** Also propagate all EP_Propagate flags from the Expr.x.pList into
          391  +** Expr.flags. 
   375    392   */
   376         -void sqlite3ExprSetHeight(Parse *pParse, Expr *p){
          393  +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p){
   377    394     exprSetHeight(p);
   378    395     sqlite3ExprCheckHeight(pParse, p->nHeight);
   379    396   }
   380    397   
   381    398   /*
   382    399   ** Return the maximum height of any expression tree referenced
   383    400   ** by the select statement passed as an argument.
   384    401   */
   385    402   int sqlite3SelectExprHeight(Select *p){
   386    403     int nHeight = 0;
   387    404     heightOfSelect(p, &nHeight);
   388    405     return nHeight;
   389    406   }
   390         -#else
   391         -  #define exprSetHeight(y)
          407  +#else /* ABOVE:  Height enforcement enabled.  BELOW: Height enforcement off */
          408  +/*
          409  +** Propagate all EP_Propagate flags from the Expr.x.pList into
          410  +** Expr.flags. 
          411  +*/
          412  +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p){
          413  +  if( p && p->x.pList && !ExprHasProperty(p, EP_xIsSelect) ){
          414  +    p->flags |= EP_Propagate & sqlite3ExprListFlags(p->x.pList);
          415  +  }
          416  +}
          417  +#define exprSetHeight(y)
   392    418   #endif /* SQLITE_MAX_EXPR_DEPTH>0 */
   393    419   
   394    420   /*
   395    421   ** This routine is the core allocator for Expr nodes.
   396    422   **
   397    423   ** Construct a new expression node and return a pointer to it.  Memory
   398    424   ** for this node and for the pToken argument is a single allocation
................................................................................
   590    616     pNew = sqlite3ExprAlloc(db, TK_FUNCTION, pToken, 1);
   591    617     if( pNew==0 ){
   592    618       sqlite3ExprListDelete(db, pList); /* Avoid memory leak when malloc fails */
   593    619       return 0;
   594    620     }
   595    621     pNew->x.pList = pList;
   596    622     assert( !ExprHasProperty(pNew, EP_xIsSelect) );
   597         -  sqlite3ExprSetHeight(pParse, pNew);
          623  +  sqlite3ExprSetHeightAndFlags(pParse, pNew);
   598    624     return pNew;
   599    625   }
   600    626   
   601    627   /*
   602    628   ** Assign a variable number to an expression that encodes a wildcard
   603    629   ** in the original SQL statement.  
   604    630   **
................................................................................
  1206   1232       sqlite3DbFree(db, pItem->zSpan);
  1207   1233     }
  1208   1234     sqlite3DbFree(db, pList->a);
  1209   1235     sqlite3DbFree(db, pList);
  1210   1236   }
  1211   1237   
  1212   1238   /*
  1213         -** Return TRUE if any expression in ExprList has any of the EP_*
  1214         -** properties given by "m"
         1239  +** Return the bitwise-OR of all Expr.flags fields in the given
         1240  +** ExprList.
  1215   1241   */
  1216         -int sqlite3AnyExprListHasProperty(const ExprList *pList, u32 m){
         1242  +u32 sqlite3ExprListFlags(const ExprList *pList){
  1217   1243     int i;
  1218         -  if( pList==0 ) return 0;
  1219         -  for(i=0; i<pList->nExpr; i++){
  1220         -    if( ExprHasProperty(pList->a[i].pExpr, m) ) return 1;
         1244  +  u32 m = 0;
         1245  +  if( pList ){
         1246  +    for(i=0; i<pList->nExpr; i++){
         1247  +       m |= pList->a[i].pExpr->flags;
         1248  +    }
  1221   1249     }
  1222         -  return 0;
         1250  +  return m;
  1223   1251   }
  1224   1252   
  1225   1253   /*
  1226   1254   ** These routines are Walker callbacks used to check expressions to
  1227   1255   ** see if they are "constant" for some definition of constant.  The
  1228   1256   ** Walker.eCode value determines the type of "constant" we are looking
  1229   1257   ** for.

Changes to src/parse.y.

  1074   1074           pRHS->flags |= EP_Generic;
  1075   1075         }
  1076   1076         A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0);
  1077   1077       }else{
  1078   1078         A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
  1079   1079         if( A.pExpr ){
  1080   1080           A.pExpr->x.pList = Y;
  1081         -        sqlite3ExprSetHeight(pParse, A.pExpr);
         1081  +        sqlite3ExprSetHeightAndFlags(pParse, A.pExpr);
  1082   1082         }else{
  1083   1083           sqlite3ExprListDelete(pParse->db, Y);
  1084   1084         }
  1085   1085         if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0);
  1086   1086       }
  1087   1087       A.zStart = X.zStart;
  1088   1088       A.zEnd = &E.z[E.n];
  1089   1089     }
  1090   1090     expr(A) ::= LP(B) select(X) RP(E). {
  1091   1091       A.pExpr = sqlite3PExpr(pParse, TK_SELECT, 0, 0, 0);
  1092   1092       if( A.pExpr ){
  1093   1093         A.pExpr->x.pSelect = X;
  1094   1094         ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery);
  1095         -      sqlite3ExprSetHeight(pParse, A.pExpr);
         1095  +      sqlite3ExprSetHeightAndFlags(pParse, A.pExpr);
  1096   1096       }else{
  1097   1097         sqlite3SelectDelete(pParse->db, X);
  1098   1098       }
  1099   1099       A.zStart = B.z;
  1100   1100       A.zEnd = &E.z[E.n];
  1101   1101     }
  1102   1102     expr(A) ::= expr(X) in_op(N) LP select(Y) RP(E).  [IN] {
  1103   1103       A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
  1104   1104       if( A.pExpr ){
  1105   1105         A.pExpr->x.pSelect = Y;
  1106   1106         ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery);
  1107         -      sqlite3ExprSetHeight(pParse, A.pExpr);
         1107  +      sqlite3ExprSetHeightAndFlags(pParse, A.pExpr);
  1108   1108       }else{
  1109   1109         sqlite3SelectDelete(pParse->db, Y);
  1110   1110       }
  1111   1111       if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0);
  1112   1112       A.zStart = X.zStart;
  1113   1113       A.zEnd = &E.z[E.n];
  1114   1114     }
  1115   1115     expr(A) ::= expr(X) in_op(N) nm(Y) dbnm(Z). [IN] {
  1116   1116       SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z);
  1117   1117       A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
  1118   1118       if( A.pExpr ){
  1119   1119         A.pExpr->x.pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0);
  1120   1120         ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery);
  1121         -      sqlite3ExprSetHeight(pParse, A.pExpr);
         1121  +      sqlite3ExprSetHeightAndFlags(pParse, A.pExpr);
  1122   1122       }else{
  1123   1123         sqlite3SrcListDelete(pParse->db, pSrc);
  1124   1124       }
  1125   1125       if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0);
  1126   1126       A.zStart = X.zStart;
  1127   1127       A.zEnd = Z.z ? &Z.z[Z.n] : &Y.z[Y.n];
  1128   1128     }
  1129   1129     expr(A) ::= EXISTS(B) LP select(Y) RP(E). {
  1130   1130       Expr *p = A.pExpr = sqlite3PExpr(pParse, TK_EXISTS, 0, 0, 0);
  1131   1131       if( p ){
  1132   1132         p->x.pSelect = Y;
  1133   1133         ExprSetProperty(p, EP_xIsSelect|EP_Subquery);
  1134         -      sqlite3ExprSetHeight(pParse, p);
         1134  +      sqlite3ExprSetHeightAndFlags(pParse, p);
  1135   1135       }else{
  1136   1136         sqlite3SelectDelete(pParse->db, Y);
  1137   1137       }
  1138   1138       A.zStart = B.z;
  1139   1139       A.zEnd = &E.z[E.n];
  1140   1140     }
  1141   1141   %endif SQLITE_OMIT_SUBQUERY
  1142   1142   
  1143   1143   /* CASE expressions */
  1144   1144   expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). {
  1145   1145     A.pExpr = sqlite3PExpr(pParse, TK_CASE, X, 0, 0);
  1146   1146     if( A.pExpr ){
  1147   1147       A.pExpr->x.pList = Z ? sqlite3ExprListAppend(pParse,Y,Z) : Y;
  1148         -    sqlite3ExprSetHeight(pParse, A.pExpr);
         1148  +    sqlite3ExprSetHeightAndFlags(pParse, A.pExpr);
  1149   1149     }else{
  1150   1150       sqlite3ExprListDelete(pParse->db, Y);
  1151   1151       sqlite3ExprDelete(pParse->db, Z);
  1152   1152     }
  1153   1153     A.zStart = C.z;
  1154   1154     A.zEnd = &E.z[E.n];
  1155   1155   }

Changes to src/select.c.

  3334   3334     iParent = pSubitem->iCursor;
  3335   3335     pSub = pSubitem->pSelect;
  3336   3336     assert( pSub!=0 );
  3337   3337     if( subqueryIsAgg ){
  3338   3338       if( isAgg ) return 0;                                /* Restriction (1)   */
  3339   3339       if( pSrc->nSrc>1 ) return 0;                         /* Restriction (2a)  */
  3340   3340       if( (p->pWhere && ExprHasProperty(p->pWhere,EP_Subquery))
  3341         -     || sqlite3AnyExprListHasProperty(p->pEList,EP_Subquery)
  3342         -     || sqlite3AnyExprListHasProperty(p->pOrderBy,EP_Subquery)
         3341  +     || (sqlite3ExprListFlags(p->pEList) & EP_Subquery)!=0
         3342  +     || (sqlite3ExprListFlags(p->pOrderBy) & EP_Subquery)!=0
  3343   3343       ){
  3344   3344         return 0;                                          /* Restriction (2b)  */
  3345   3345       }
  3346   3346     }
  3347   3347       
  3348   3348     pSubSrc = pSub->pSrc;
  3349   3349     assert( pSubSrc );

Changes to src/sqliteInt.h.

  3155   3155   Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*);
  3156   3156   void sqlite3ExprAssignVarNumber(Parse*, Expr*);
  3157   3157   void sqlite3ExprDelete(sqlite3*, Expr*);
  3158   3158   ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*);
  3159   3159   void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
  3160   3160   void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*);
  3161   3161   void sqlite3ExprListDelete(sqlite3*, ExprList*);
  3162         -int sqlite3AnyExprListHasProperty(const ExprList*,u32);
         3162  +u32 sqlite3ExprListFlags(const ExprList*);
  3163   3163   int sqlite3Init(sqlite3*, char**);
  3164   3164   int sqlite3InitCallback(void*, int, char**, char**);
  3165   3165   void sqlite3Pragma(Parse*,Token*,Token*,Token*,int);
  3166   3166   void sqlite3ResetAllSchemasOfConnection(sqlite3*);
  3167   3167   void sqlite3ResetOneSchema(sqlite3*,int);
  3168   3168   void sqlite3CollapseDatabaseArray(sqlite3*);
  3169   3169   void sqlite3BeginParse(Parse*,int);
................................................................................
  3739   3739     #define sqlite3JournalExists(p) 1
  3740   3740   #endif
  3741   3741   
  3742   3742   void sqlite3MemJournalOpen(sqlite3_file *);
  3743   3743   int sqlite3MemJournalSize(void);
  3744   3744   int sqlite3IsMemJournal(sqlite3_file *);
  3745   3745   
         3746  +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p);
  3746   3747   #if SQLITE_MAX_EXPR_DEPTH>0
  3747         -  void sqlite3ExprSetHeight(Parse *pParse, Expr *p);
  3748   3748     int sqlite3SelectExprHeight(Select *);
  3749   3749     int sqlite3ExprCheckHeight(Parse*, int);
  3750   3750   #else
  3751         -  #define sqlite3ExprSetHeight(x,y)
  3752   3751     #define sqlite3SelectExprHeight(x) 0
  3753   3752     #define sqlite3ExprCheckHeight(x,y)
  3754   3753   #endif
  3755   3754   
  3756   3755   u32 sqlite3Get4byte(const u8*);
  3757   3756   void sqlite3Put4byte(u8*, u32);
  3758   3757   

Changes to test/collate8.test.

     9      9   #    May you share freely, never taking more than you give.
    10     10   #
    11     11   #***********************************************************************
    12     12   # This file implements regression tests for SQLite library.  The
    13     13   # focus of this script is making sure collations pass through the
    14     14   # unary + operator.
    15     15   #
    16         -# $Id: collate8.test,v 1.2 2008/08/25 12:14:09 drh Exp $
           16  +# 2015-02-09:  Added tests to make sure COLLATE passes through function
           17  +# calls.  Ticket [ca0d20b6cdddec5e81b8d66f89c46a5583b5f6f6].
           18  +#
    17     19   
    18     20   set testdir [file dirname $argv0]
    19     21   source $testdir/tester.tcl
    20     22   
    21     23   do_test collate8-1.1 {
    22     24     execsql {
    23     25       CREATE TABLE t1(a TEXT COLLATE nocase);
................................................................................
   117    119     }
   118    120   } {abc ABC}
   119    121   do_test collate8-2.8 {
   120    122     execsql {
   121    123       SELECT a COLLATE nocase AS x FROM t2 WHERE 'abc'=x COLLATE binary;
   122    124     }
   123    125   } {abc}
          126  +
          127  +# Make sure the COLLATE operator perculates up through function calls
          128  +# and other Expr structures that use the Expr.x.pList field.
          129  +#
          130  +do_execsql_test collate8-3.1 {
          131  +  SELECT 'abc'==('ABC'||'') COLLATE nocase;
          132  +  SELECT 'abc'==('ABC'||'' COLLATE nocase);
          133  +  SELECT 'abc'==('ABC'||('' COLLATE nocase));
          134  +  SELECT 'abc'==('ABC'||upper('' COLLATE nocase));
          135  +} {1 1 1 1}
          136  +do_execsql_test collate8-3.2 {
          137  +  SELECT 'abc'==('ABC'||max('' COLLATE nocase,'' COLLATE binary));
          138  +} {1}
          139  +
          140  +# The COLLATE binary is on the left and so takes precedence
          141  +do_execsql_test collate8-3.3 {
          142  +  SELECT 'abc'==('ABC'||max('' COLLATE binary,'' COLLATE nocase));
          143  +} {0}
          144  +
          145  +do_execsql_test collate8-3.4 {
          146  +  SELECT 'abc'==('ABC'||CASE WHEN 1-1=2 THEN '' COLLATE nocase
          147  +                                        ELSE '' COLLATE binary END);
          148  +  SELECT 'abc'==('ABC'||CASE WHEN 1+1=2 THEN '' COLLATE nocase
          149  +                                        ELSE '' COLLATE binary END);
          150  +} {1 1}
          151  +do_execsql_test collate8-3.5 {
          152  +  SELECT 'abc'==('ABC'||CASE WHEN 1=2 THEN '' COLLATE binary
          153  +                                      ELSE '' COLLATE nocase END);
          154  +} {0}
          155  +
   124    156   
   125    157   finish_test