/ Check-in [b4b57413]
Login

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

Overview
Comment:Fix a problem with renaming a column that is used as part of an ORDER BY on a compound SELECT within a database view or trigger.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: b4b5741366578b25ec6e4c415ab8239215e53b1c900be613575f40a826cfccc9
User & Date: dan 2019-01-16 14:58:37
References
2019-01-16
20:48
Fix a problem with fix [b4b57413]. check-in: ca7b7aae user: dan tags: trunk
Context
2019-01-16
19:26
Fix a problem in the code generator for sorting results with SRT_EphemTab and a LIMIT clause. check-in: 49fcde2f user: drh tags: trunk
14:58
Fix a problem with renaming a column that is used as part of an ORDER BY on a compound SELECT within a database view or trigger. check-in: b4b57413 user: dan tags: trunk
12:05
Avoid a dangling pointer comparison when renaming a table that has a trigger that itself contains a window function with an (illegal) column reference in a FOLLOWING expression. check-in: d45bee36 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

  1134   1134           if( iCol<=0 || iCol>pEList->nExpr ){
  1135   1135             resolveOutOfRangeError(pParse, "ORDER", i+1, pEList->nExpr);
  1136   1136             return 1;
  1137   1137           }
  1138   1138         }else{
  1139   1139           iCol = resolveAsName(pParse, pEList, pE);
  1140   1140           if( iCol==0 ){
  1141         -          pDup = sqlite3ExprDup(db, pE, 0);
         1141  +          /* Now test if expression pE matches one of the values returned
         1142  +          ** by pSelect. In the usual case this is done by duplicating the 
         1143  +          ** expression, resolving any symbols in it, and then comparing
         1144  +          ** it against each expression returned by the SELECT statement.
         1145  +          ** Once the comparisons are finished, the duplicate expression
         1146  +          ** is deleted.
         1147  +          **
         1148  +          ** Or, if this is running as part of an ALTER TABLE operation,
         1149  +          ** resolve the symbols in the actual expression, not a duplicate.
         1150  +          ** And, if one of the comparisons is successful, leave the expression
         1151  +          ** as is instead of transforming it to an integer as in the usual
         1152  +          ** case. This allows the code in alter.c to modify column
         1153  +          ** refererences within the ORDER BY expression as required.  */
         1154  +          if( IN_RENAME_OBJECT ){
         1155  +            pDup = pE;
         1156  +          }else{
         1157  +            pDup = sqlite3ExprDup(db, pE, 0);
         1158  +          }
  1142   1159             if( !db->mallocFailed ){
  1143   1160               assert(pDup);
  1144   1161               iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup);
  1145   1162             }
  1146         -          sqlite3ExprDelete(db, pDup);
         1163  +          if( IN_RENAME_OBJECT ){
         1164  +            if( iCol>0 ){
         1165  +              pItem->done = 1;
         1166  +              break;
         1167  +            }
         1168  +          }else{
         1169  +            sqlite3ExprDelete(db, pDup);
         1170  +          }
  1147   1171           }
  1148   1172         }
  1149   1173         if( iCol>0 ){
  1150   1174           /* Convert the ORDER BY term into an integer column number iCol,
  1151   1175           ** taking care to preserve the COLLATE clause if it exists */
  1152   1176           Expr *pNew = sqlite3Expr(db, TK_INTEGER, 0);
  1153   1177           if( pNew==0 ) return 1;

Changes to test/altertab2.test.

   228    228         );
   229    229     END}
   230    230   }
   231    231   
   232    232   do_catchsql_test 5.3 {
   233    233     INSERT INTO t2x VALUES(1);
   234    234   } {1 {no such column: b}}
          235  +
          236  +#-------------------------------------------------------------------------
          237  +
          238  +do_execsql_test 6.0 {
          239  +  CREATE TABLE t3(a,b,c,d);
          240  +  CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN
          241  +    SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a;
          242  +    SELECT rowid, * FROM t3;
          243  +  END;
          244  +} {}
          245  +
          246  +do_execsql_test 6.1 {
          247  +  ALTER TABLE t3 RENAME TO t3x;
          248  +  SELECT sql FROM sqlite_master WHERE name = 'r3';
          249  +} {
          250  +  {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.a NOT NULL BEGIN
          251  +    SELECT a,b,c FROM "t3x" EXCEPT SELECT a,b,c FROM "t3x" ORDER BY a;
          252  +    SELECT rowid, * FROM "t3x";
          253  +  END}
          254  +}
          255  +
          256  +do_execsql_test 6.2 {
          257  +  ALTER TABLE t3x RENAME a TO abcd;
          258  +  SELECT sql FROM sqlite_master WHERE name = 'r3';
          259  +} {
          260  +  {CREATE TRIGGER r3 AFTER INSERT ON "t3x" WHEN new.abcd NOT NULL BEGIN
          261  +    SELECT abcd,b,c FROM "t3x" EXCEPT SELECT abcd,b,c FROM "t3x" ORDER BY abcd;
          262  +    SELECT rowid, * FROM "t3x";
          263  +  END}
          264  +}
   235    265   
   236    266   finish_test
          267  +
          268  +