/ Check-in [f2d175f9]
Login

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

Overview
Comment:Make sure that GROUP BY terms select input column names in preference to output column names, in compliance with the SQL standard. Ticket [1c69be2dafc28].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:f2d175f975cd0be63425424ec322a98fb650019e
User & Date: drh 2013-08-15 22:40:21
References
2014-06-10
20:11 Open ticket [1c69be2d]: Incorrect GROUP BY when input and output columns have the same name plus 4 other changes artifact: a8a0089d user: drh
Context
2013-08-17
16:37
Add the cache_spill pragma. Change the fullfsync and checkpoint_fullfsync pragmas to apply to all attached databases. check-in: 65a85a15 user: drh tags: trunk
2013-08-16
20:42
Add the cache_spill pragma. check-in: cdb181c0 user: drh tags: cache_spill
12:26
Merge recent trunk changes into the STAT4 branch. check-in: c69b512a user: drh tags: sqlite_stat4
2013-08-15
22:40
Make sure that GROUP BY terms select input column names in preference to output column names, in compliance with the SQL standard. Ticket [1c69be2dafc28]. check-in: f2d175f9 user: drh tags: trunk
20:24
Bare identifiers in ORDER BY clauses bind more tightly to output column name, but identifiers in expressions bind more tightly to input column names. This is a compromise between SQL92 and SQL99 behavior and is what PostgreSQL and MS-SQL do. Ticket [f617ea3125e9c]. check-in: c78b357c user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

1039
1040
1041
1042
1043
1044
1045

1046
1047
1048
1049
1050
1051
1052
1053

1054
1055
1056
1057
1058
1059
1060

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







>
|
|
|
|
|
|
|
|
>







1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062

  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->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.

144
145
146
147
148
149
150
151
























































152
  INSERT INTO t4 VALUES('az');
  INSERT INTO t4 VALUES('by');
  INSERT INTO t4 VALUES('cx');
  SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m;
  SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary;
  SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m);
} {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x}

























































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

144
145
146
147
148
149
150
151
152
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
  INSERT INTO t4 VALUES('az');
  INSERT INTO t4 VALUES('by');
  INSERT INTO t4 VALUES('cx');
  SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m;
  SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary;
  SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m);
} {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x}

##########################################################################
# 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