/ Check-in [401a0ca3]
Login

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

Overview
Comment:Proposed change to the fix for ticket [1c69be2dafc28b] such that legacy applications that were exploiting the older buggy behavior in SQLite continue to work.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | group-by-name-resolution
Files: files | file ages | folders
SHA1:401a0ca3dd7e214d07958b4659d947a443cbbc52
User & Date: drh 2014-06-10 20:18:17
Context
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: 401a0ca3 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: 6484fb5a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

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
...
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
....
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
** 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
................................................................................
      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);
................................................................................

  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);







>
>
>
>
>
>
>
>
>
>





|
>









>
|
<
>
>
>
>
>
>

>







 







|







 







<
|
|
|
|
|
|
|
|
<







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
...
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
....
1089
1090
1091
1092
1093
1094
1095

1096
1097
1098
1099
1100
1101
1102
1103

1104
1105
1106
1107
1108
1109
1110
** 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
................................................................................
      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);
................................................................................

  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
...
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
...
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;
................................................................................
  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 {
................................................................................
  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
...
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
...
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;
................................................................................
  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 {
................................................................................
  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