/ Changes On Branch group-by-name-resolution
Login

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

Changes In Branch group-by-name-resolution Excluding Merge-Ins

This is equivalent to a diff from 6484fb5a25 to 401a0ca3dd

2014-06-12
15:46
Simplify the estLog() routine. (check-in: 6c68d758bc user: drh tags: trunk)
2014-06-10
20:18
Proposed change to the fix for ticket [1c69be2dafc28b] such that legacy applications that were exploiting the older buggy behavior in SQLite continue to work. (Leaf check-in: 401a0ca3dd user: drh tags: group-by-name-resolution)
2014-06-09
20:39
Avoid an unnecessary initialization of the szFile field of unixFile in the unix VFS. (check-in: 6484fb5a25 user: drh tags: trunk)
20:24
Enhance the unix VFS so that it keeps track of the size of unlinked files internally and thus avoids the need to call fstat() on those files, since fstat() does not work reliably on unlinked files on some implementations of FuseFS. (check-in: 10707d3578 user: drh tags: trunk)

Changes to src/resolve.c.

793
794
795
796
797
798
799










800
801
802
803
804
805

806
807
808
809
810
811
812
813
814

815
816






817

818
819
820
821
822
823
824
** This routine checks to see if pE is a simple identifier which corresponds
** to the AS-name of one of the terms of the expression list.  If it is,
** this routine return an integer between 1 and N where N is the number of
** elements in pEList, corresponding to the matching entry.  If there is
** no match, or if pE is not a simple identifier, then this routine
** return 0.
**










** pEList has been resolved.  pE has not.
*/
static int resolveAsName(
  Parse *pParse,     /* Parsing context for error messages */
  ExprList *pEList,  /* List of expressions to scan */
  Expr *pE           /* Expression we are trying to match */

){
  int i;             /* Loop counter */

  UNUSED_PARAMETER(pParse);

  if( pE->op==TK_ID ){
    char *zCol = pE->u.zToken;
    for(i=0; i<pEList->nExpr; i++){
      char *zAs = pEList->a[i].zName;

      if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
        return i+1;






      }

    }
  }
  return 0;
}

/*
** pE is a pointer to an expression which is a single term in the







>
>
>
>
>
>
>
>
>
>





|
>









>
|
<
>
>
>
>
>
>

>







793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827

828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
** This routine checks to see if pE is a simple identifier which corresponds
** to the AS-name of one of the terms of the expression list.  If it is,
** this routine return an integer between 1 and N where N is the number of
** elements in pEList, corresponding to the matching entry.  If there is
** no match, or if pE is not a simple identifier, then this routine
** return 0.
**
** The sameNameOnly flag is set if pE comes from a GROUP BY clause.  The
** arguments of a GROUP BY clause are not supposed to be able to match
** against AS names in SQL.  But early versions of SQLite allowed this
** behavior by mistake.  To provide backwards compatibility, a GROUP BY
** term will match as AS alias only if the corresponding result set expression
** refers to a table column by the same name.  In other words:
**
**        SELECT t1.x AS x, t2.x AS y FROM t1,t2 GROUP BY x;  -- match
**        SELECT t1.y AS x, t2.y AS y FROM t1,t2 GROUP BY x;  -- no match
**
** pEList has been resolved.  pE has not.
*/
static int resolveAsName(
  Parse *pParse,     /* Parsing context for error messages */
  ExprList *pEList,  /* List of expressions to scan */
  Expr *pE,          /* Expression we are trying to match */
  int sameNameOnly   /* Only resolve if the alias matches the column name */
){
  int i;             /* Loop counter */

  UNUSED_PARAMETER(pParse);

  if( pE->op==TK_ID ){
    char *zCol = pE->u.zToken;
    for(i=0; i<pEList->nExpr; i++){
      char *zAs = pEList->a[i].zName;
      if( zAs==0 ) continue;
      if( sqlite3StrICmp(zAs, zCol)!=0 ) continue;

      if( sameNameOnly ){
        Expr *p = pEList->a[i].pExpr;
        Table *pTab;
        if( p->op!=TK_COLUMN ) continue;
        pTab = p->pTab;
        if( sqlite3StrICmp(pTab->aCol[p->iColumn].zName, zAs)!=0 ) continue;
      }
      return i+1;
    }
  }
  return 0;
}

/*
** pE is a pointer to an expression which is a single term in the
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
      pE = sqlite3ExprSkipCollate(pItem->pExpr);
      if( sqlite3ExprIsInteger(pE, &iCol) ){
        if( iCol<=0 || iCol>pEList->nExpr ){
          resolveOutOfRangeError(pParse, "ORDER", i+1, pEList->nExpr);
          return 1;
        }
      }else{
        iCol = resolveAsName(pParse, pEList, pE);
        if( iCol==0 ){
          pDup = sqlite3ExprDup(db, pE, 0);
          if( !db->mallocFailed ){
            assert(pDup);
            iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup);
          }
          sqlite3ExprDelete(db, pDup);







|







968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
      pE = sqlite3ExprSkipCollate(pItem->pExpr);
      if( sqlite3ExprIsInteger(pE, &iCol) ){
        if( iCol<=0 || iCol>pEList->nExpr ){
          resolveOutOfRangeError(pParse, "ORDER", i+1, pEList->nExpr);
          return 1;
        }
      }else{
        iCol = resolveAsName(pParse, pEList, pE, 0);
        if( iCol==0 ){
          pDup = sqlite3ExprDup(db, pE, 0);
          if( !db->mallocFailed ){
            assert(pDup);
            iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup);
          }
          sqlite3ExprDelete(db, pDup);
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094

  if( pOrderBy==0 ) return 0;
  nResult = pSelect->pEList->nExpr;
  pParse = pNC->pParse;
  for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){
    Expr *pE = pItem->pExpr;
    Expr *pE2 = sqlite3ExprSkipCollate(pE);
    if( zType[0]!='G' ){
      iCol = resolveAsName(pParse, pSelect->pEList, pE2);
      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->u.x.iOrderByCol = (u16)iCol;
        continue;
      }
    }
    if( sqlite3ExprIsInteger(pE2, &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 || iCol>0xffff ){
        resolveOutOfRangeError(pParse, zType, i+1, nResult);







<
|
|
|
|
|
|
|
|
<







1089
1090
1091
1092
1093
1094
1095

1096
1097
1098
1099
1100
1101
1102
1103

1104
1105
1106
1107
1108
1109
1110

  if( pOrderBy==0 ) return 0;
  nResult = pSelect->pEList->nExpr;
  pParse = pNC->pParse;
  for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){
    Expr *pE = pItem->pExpr;
    Expr *pE2 = sqlite3ExprSkipCollate(pE);

    iCol = resolveAsName(pParse, pSelect->pEList, pE2, zType[0]=='G');
    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->u.x.iOrderByCol = (u16)iCol;
      continue;

    }
    if( sqlite3ExprIsInteger(pE2, &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 || iCol>0xffff ){
        resolveOutOfRangeError(pParse, zType, i+1, nResult);

Changes to test/resolver01.test.

153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204




205













206

207
208
# Test cases for ticket [1c69be2dafc28]:  Make sure the GROUP BY binds
# more tightly to the input tables in all cases.
#
# This first case case has been wrong in SQLite for time out of mind.
# For SQLite version 3.7.17 the answer was two rows, which is wrong.
#
do_execsql_test resolver01-5.1 {
  CREATE TABLE t5(m CHAR(2));
  INSERT INTO t5 VALUES('ax');
  INSERT INTO t5 VALUES('bx');
  INSERT INTO t5 VALUES('cy');
  SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2;
} {1 x 1 x 1 y}

# This case is unambiguous and has always been correct.
#
do_execsql_test resolver01-5.2 {
  SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2;
} {1 x 1 x 1 y}

# This case is not allowed in standard SQL, but SQLite allows and does
# the sensible thing.
#
do_execsql_test resolver01-5.3 {
  SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2;
} {1 y 2 x}
do_execsql_test resolver01-5.4 {
  SELECT count(*), substr(m,2,1) AS mx FROM t5
   GROUP BY substr(m,2,1) ORDER BY 1, 2;
} {1 y 2 x}

# These test case weere provided in the 2013-08-14 email from Rob Golsteijn
# that originally reported the problem of ticket [1c69be2dafc28].
#
do_execsql_test resolver01-6.1 {
  CREATE TABLE t61(name);
  SELECT min(name) FROM t61 GROUP BY lower(name);
} {}
do_execsql_test resolver01-6.2 {
  SELECT min(name) AS name FROM t61 GROUP BY lower(name); 
} {}
do_execsql_test resolver01-6.3 {
  CREATE TABLE t63(name);
  INSERT INTO t63 VALUES (NULL);
  INSERT INTO t63 VALUES ('abc');
  SELECT count(),
       NULLIF(name,'abc') AS name
    FROM t63
   GROUP BY lower(name);
} {1 {} 1 {}}























finish_test







|
|
|
|




















|



















|
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>


153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
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
226
# Test cases for ticket [1c69be2dafc28]:  Make sure the GROUP BY binds
# more tightly to the input tables in all cases.
#
# This first case case has been wrong in SQLite for time out of mind.
# For SQLite version 3.7.17 the answer was two rows, which is wrong.
#
do_execsql_test resolver01-5.1 {
  CREATE TABLE t5(m CHAR(2), n);
  INSERT INTO t5 VALUES('ax',1);
  INSERT INTO t5 VALUES('bx',2);
  INSERT INTO t5 VALUES('cy',2);
  SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2;
} {1 x 1 x 1 y}

# This case is unambiguous and has always been correct.
#
do_execsql_test resolver01-5.2 {
  SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2;
} {1 x 1 x 1 y}

# This case is not allowed in standard SQL, but SQLite allows and does
# the sensible thing.
#
do_execsql_test resolver01-5.3 {
  SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2;
} {1 y 2 x}
do_execsql_test resolver01-5.4 {
  SELECT count(*), substr(m,2,1) AS mx FROM t5
   GROUP BY substr(m,2,1) ORDER BY 1, 2;
} {1 y 2 x}

# These test cases were provided in the 2013-08-14 email from Rob Golsteijn
# that originally reported the problem of ticket [1c69be2dafc28].
#
do_execsql_test resolver01-6.1 {
  CREATE TABLE t61(name);
  SELECT min(name) FROM t61 GROUP BY lower(name);
} {}
do_execsql_test resolver01-6.2 {
  SELECT min(name) AS name FROM t61 GROUP BY lower(name); 
} {}
do_execsql_test resolver01-6.3 {
  CREATE TABLE t63(name);
  INSERT INTO t63 VALUES (NULL);
  INSERT INTO t63 VALUES ('abc');
  SELECT count(),
       NULLIF(name,'abc') AS name
    FROM t63
   GROUP BY lower(name);
} {1 {} 1 {}}

# (2014-06-10) The fix to GROUP BY name binding has resulted in errors
# in some legacy Android applications.  To work around this, GROUP BY terms
# can be bound to AS aliases as long as the corresponding expression is
# a column by the same name as the alias.  Verify that this exception
# case works.  Continuation of ticket [1c69be2dafc28].
#
do_execsql_test resolver01-7.1 {
  CREATE TABLE t7(m, x);
  INSERT INTO t7 VALUES('bx',1),('ax',2),('dx',3);
  SELECT count(*), t5.m AS m FROM t5, t7 GROUP BY m ORDER BY 2;
} {3 ax 3 bx 3 cy}
do_execsql_test resolver01-7.2 {
  SELECT count(*), t5.m AS n FROM t5, t7 GROUP BY n ORDER BY 2;
} {3 ax 6 cy}
do_test resolver01-7.3 {
  catchsql {
    CREATE TABLE t7b(m, n);
    INSERT INTO t7b SELECT * FROM t7;
    SELECT count(*), t5.m AS n FROM t5, t7b GROUP BY n ORDER BY 2;
  }
} {1 {ambiguous column name: n}}

finish_test