/ Check-in [df2060f3]
Login

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

Overview
Comment:Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.30
Files: files | file ages | folders
SHA3-256: df2060f34fcb2a38c016df92079df5b64017fa31c97b2eef51eab7a9b5b940bf
User & Date: drh 2019-10-10 15:48:16
Context
2019-10-10
15:57
Fix the OP_SeekRowid opcode so that it works correctly with a Real argument without damaging the value in the register that is the argument. Ticket [b2d4edaffdc156cc]. Test cases in TH3. check-in: b02630fe user: drh tags: branch-3.30
15:48
Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19]. check-in: df2060f3 user: drh tags: branch-3.30
15:42
Version number to 3.30.1. check-in: 12e28cc7 user: drh tags: branch-3.30
15:17
Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19]. check-in: ccba7202 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

    96     96       sqlite3ExprDelete(db, p->pHaving);
    97     97       sqlite3ExprListDelete(db, p->pOrderBy);
    98     98       sqlite3ExprDelete(db, p->pLimit);
    99     99   #ifndef SQLITE_OMIT_WINDOWFUNC
   100    100       if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
   101    101         sqlite3WindowListDelete(db, p->pWinDefn);
   102    102       }
   103         -#endif
   104         -    if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);
   105    103       assert( p->pWin==0 );
          104  +#endif
          105  +    if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);
   106    106       if( bFree ) sqlite3DbFreeNN(db, p);
   107    107       p = pPrior;
   108    108       bFree = 1;
   109    109     }
   110    110   }
   111    111   
   112    112   /*
................................................................................
  3499   3499       pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);
  3500   3500       pExpr->pRight = substExpr(pSubst, pExpr->pRight);
  3501   3501       if( ExprHasProperty(pExpr, EP_xIsSelect) ){
  3502   3502         substSelect(pSubst, pExpr->x.pSelect, 1);
  3503   3503       }else{
  3504   3504         substExprList(pSubst, pExpr->x.pList);
  3505   3505       }
         3506  +#ifndef SQLITE_OMIT_WINDOWFUNC
         3507  +    if( ExprHasProperty(pExpr, EP_WinFunc) ){
         3508  +      Window *pWin = pExpr->y.pWin;
         3509  +      pWin->pFilter = substExpr(pSubst, pWin->pFilter);
         3510  +      substExprList(pSubst, pWin->pPartition);
         3511  +      substExprList(pSubst, pWin->pOrderBy);
         3512  +    }
         3513  +#endif
  3506   3514     }
  3507   3515     return pExpr;
  3508   3516   }
  3509   3517   static void substExprList(
  3510   3518     SubstContext *pSubst, /* Description of the substitution */
  3511   3519     ExprList *pList       /* List to scan and in which to make substitutes */
  3512   3520   ){

Changes to src/sqliteInt.h.

  2581   2581   */
  2582   2582   #define EXPRDUP_REDUCE         0x0001  /* Used reduced-size Expr nodes */
  2583   2583   
  2584   2584   /*
  2585   2585   ** True if the expression passed as an argument was a function with
  2586   2586   ** an OVER() clause (a window function).
  2587   2587   */
  2588         -#define IsWindowFunc(p) ( \
         2588  +#ifdef SQLITE_OMIT_WINDOWFUNC
         2589  +# define IsWindowFunc(p) 0
         2590  +#else
         2591  +# define IsWindowFunc(p) ( \
  2589   2592       ExprHasProperty((p), EP_WinFunc) && p->y.pWin->eFrmType!=TK_FILTER \
  2590         -)
         2593  + )
         2594  +#endif
  2591   2595   
  2592   2596   /*
  2593   2597   ** A list of expressions.  Each expression may optionally have a
  2594   2598   ** name.  An expr/name combination can be used in several ways, such
  2595   2599   ** as the list of "expr AS ID" fields following a "SELECT" or in the
  2596   2600   ** list of "ID = expr" items in an UPDATE.  A list of expressions can
  2597   2601   ** also be used as the argument to a function, in which case the a.zName

Changes to test/filter1.test.

   159    159   } {2.0 5.0 10.0}
   160    160   do_execsql_test 4.3 {
   161    161     SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c);
   162    162   } {c 2.0 a 10.0 b 5.0}
   163    163   do_execsql_test 4.4 {
   164    164     SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2
   165    165   } {c 2.0 b 5.0 a 10.0}
          166  +
          167  +#-------------------------------------------------------------------------
          168  +reset_db
          169  +do_execsql_test 5.0 {
          170  +  CREATE TABLE t1(a, b);
          171  +  INSERT INTO t1 VALUES(1, 2);
          172  +  INSERT INTO t1 VALUES(1, 3);
          173  +}
          174  +
          175  +do_execsql_test 5.1 {
          176  +  SELECT count(*) FILTER (WHERE b>2) FROM (SELECT * FROM t1) 
          177  +} {1}
          178  +
          179  +do_execsql_test 5.2 {
          180  +  SELECT count(*) FILTER (WHERE b>2) OVER () FROM (SELECT * FROM t1) 
          181  +} {1 1}
          182  +
          183  +do_execsql_test 5.3 {
          184  +  SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1) 
          185  +} {0 1}
   166    186   
   167    187   finish_test
   168    188   

Changes to test/window1.test.

  1185   1185       count () OVER win3
  1186   1186     FROM t1
  1187   1187     WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING
  1188   1188                     EXCLUDE CURRENT ROW),
  1189   1189            win2 AS (PARTITION BY b ORDER BY a),
  1190   1190            win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING );
  1191   1191   } {1 1}
         1192  +
         1193  +#-------------------------------------------------------------------------
         1194  +reset_db
         1195  +do_execsql_test 31.1 {
         1196  +  CREATE TABLE t1(a, b);
         1197  +  CREATE TABLE t2(c, d);
         1198  +  CREATE TABLE t3(e, f);
         1199  +
         1200  +  INSERT INTO t1 VALUES(1, 1);
         1201  +  INSERT INTO t2 VALUES(1, 1);
         1202  +  INSERT INTO t3 VALUES(1, 1);
         1203  +}
         1204  +
         1205  +do_execsql_test 31.2 {
         1206  +  SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM (
         1207  +    SELECT * FROM t2
         1208  +  );
         1209  +} {1}
         1210  +
         1211  +do_execsql_test 31.3 {
         1212  +  SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM (
         1213  +    SELECT * FROM t2
         1214  +  );
         1215  +} {1}
         1216  +
         1217  +do_catchsql_test 31.3 {
         1218  +  SELECT d IN (
         1219  +    SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING) 
         1220  +    FROM t3
         1221  +  )
         1222  +  FROM (
         1223  +    SELECT * FROM t2
         1224  +  );
         1225  +} {1 {frame starting offset must be a non-negative integer}}
         1226  +
         1227  +do_catchsql_test 31.3 {
         1228  +  SELECT d IN (
         1229  +    SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING) 
         1230  +    FROM t3
         1231  +  )
         1232  +  FROM (
         1233  +    SELECT * FROM t2
         1234  +  );
         1235  +} {1 {frame ending offset must be a non-negative integer}}
  1192   1236   
  1193   1237   finish_test