/ Check-in [f2d175f9]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

  1039   1039   
  1040   1040     if( pOrderBy==0 ) return 0;
  1041   1041     nResult = pSelect->pEList->nExpr;
  1042   1042     pParse = pNC->pParse;
  1043   1043     for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){
  1044   1044       Expr *pE = pItem->pExpr;
  1045   1045       Expr *pE2 = sqlite3ExprSkipCollate(pE);
  1046         -    iCol = resolveAsName(pParse, pSelect->pEList, pE2);
  1047         -    if( iCol>0 ){
  1048         -      /* If an AS-name match is found, mark this ORDER BY column as being
  1049         -      ** a copy of the iCol-th result-set column.  The subsequent call to
  1050         -      ** sqlite3ResolveOrderGroupBy() will convert the expression to a
  1051         -      ** copy of the iCol-th result-set expression. */
  1052         -      pItem->iOrderByCol = (u16)iCol;
  1053         -      continue;
         1046  +    if( zType[0]!='G' ){
         1047  +      iCol = resolveAsName(pParse, pSelect->pEList, pE2);
         1048  +      if( iCol>0 ){
         1049  +        /* If an AS-name match is found, mark this ORDER BY column as being
         1050  +        ** a copy of the iCol-th result-set column.  The subsequent call to
         1051  +        ** sqlite3ResolveOrderGroupBy() will convert the expression to a
         1052  +        ** copy of the iCol-th result-set expression. */
         1053  +        pItem->iOrderByCol = (u16)iCol;
         1054  +        continue;
         1055  +      }
  1054   1056       }
  1055   1057       if( sqlite3ExprIsInteger(pE2, &iCol) ){
  1056   1058         /* The ORDER BY term is an integer constant.  Again, set the column
  1057   1059         ** number so that sqlite3ResolveOrderGroupBy() will convert the
  1058   1060         ** order-by term to a copy of the result-set expression */
  1059   1061         if( iCol<1 || iCol>0xffff ){
  1060   1062           resolveOutOfRangeError(pParse, zType, i+1, nResult);

Changes to test/resolver01.test.

   144    144     INSERT INTO t4 VALUES('az');
   145    145     INSERT INTO t4 VALUES('by');
   146    146     INSERT INTO t4 VALUES('cx');
   147    147     SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m;
   148    148     SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary;
   149    149     SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m);
   150    150   } {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x}
          151  +
          152  +##########################################################################
          153  +# Test cases for ticket [1c69be2dafc28]:  Make sure the GROUP BY binds
          154  +# more tightly to the input tables in all cases.
          155  +#
          156  +# This first case case has been wrong in SQLite for time out of mind.
          157  +# For SQLite version 3.7.17 the answer was two rows, which is wrong.
          158  +#
          159  +do_execsql_test resolver01-5.1 {
          160  +  CREATE TABLE t5(m CHAR(2));
          161  +  INSERT INTO t5 VALUES('ax');
          162  +  INSERT INTO t5 VALUES('bx');
          163  +  INSERT INTO t5 VALUES('cy');
          164  +  SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2;
          165  +} {1 x 1 x 1 y}
          166  +
          167  +# This case is unambiguous and has always been correct.
          168  +#
          169  +do_execsql_test resolver01-5.2 {
          170  +  SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2;
          171  +} {1 x 1 x 1 y}
          172  +
          173  +# This case is not allowed in standard SQL, but SQLite allows and does
          174  +# the sensible thing.
          175  +#
          176  +do_execsql_test resolver01-5.3 {
          177  +  SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2;
          178  +} {1 y 2 x}
          179  +do_execsql_test resolver01-5.4 {
          180  +  SELECT count(*), substr(m,2,1) AS mx FROM t5
          181  +   GROUP BY substr(m,2,1) ORDER BY 1, 2;
          182  +} {1 y 2 x}
          183  +
          184  +# These test case weere provided in the 2013-08-14 email from Rob Golsteijn
          185  +# that originally reported the problem of ticket [1c69be2dafc28].
          186  +#
          187  +do_execsql_test resolver01-6.1 {
          188  +  CREATE TABLE t61(name);
          189  +  SELECT min(name) FROM t61 GROUP BY lower(name);
          190  +} {}
          191  +do_execsql_test resolver01-6.2 {
          192  +  SELECT min(name) AS name FROM t61 GROUP BY lower(name); 
          193  +} {}
          194  +do_execsql_test resolver01-6.3 {
          195  +  CREATE TABLE t63(name);
          196  +  INSERT INTO t63 VALUES (NULL);
          197  +  INSERT INTO t63 VALUES ('abc');
          198  +  SELECT count(),
          199  +       NULLIF(name,'abc') AS name
          200  +    FROM t63
          201  +   GROUP BY lower(name);
          202  +} {1 {} 1 {}}
          203  +
          204  +
          205  +
          206  +
   151    207   
   152    208   finish_test