SQLite

Check-in [df2060f34f]
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
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.906
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: b02630fe6e 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: df2060f34f user: drh tags: branch-3.30)
15:42
Version number to 3.30.1. (check-in: 12e28cc7d9 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: ccba7202b6 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
96
97
98
99
100
101
102

103
104
105
106
107
108
109
110
111
112
    sqlite3ExprDelete(db, p->pHaving);
    sqlite3ExprListDelete(db, p->pOrderBy);
    sqlite3ExprDelete(db, p->pLimit);
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
      sqlite3WindowListDelete(db, p->pWinDefn);
    }

#endif
    if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);
    assert( p->pWin==0 );
    if( bFree ) sqlite3DbFreeNN(db, p);
    p = pPrior;
    bFree = 1;
  }
}

/*







>


<







96
97
98
99
100
101
102
103
104
105

106
107
108
109
110
111
112
    sqlite3ExprDelete(db, p->pHaving);
    sqlite3ExprListDelete(db, p->pOrderBy);
    sqlite3ExprDelete(db, p->pLimit);
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( OK_IF_ALWAYS_TRUE(p->pWinDefn) ){
      sqlite3WindowListDelete(db, p->pWinDefn);
    }
    assert( p->pWin==0 );
#endif
    if( OK_IF_ALWAYS_TRUE(p->pWith) ) sqlite3WithDelete(db, p->pWith);

    if( bFree ) sqlite3DbFreeNN(db, p);
    p = pPrior;
    bFree = 1;
  }
}

/*
3499
3500
3501
3502
3503
3504
3505








3506
3507
3508
3509
3510
3511
3512
    pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);
    pExpr->pRight = substExpr(pSubst, pExpr->pRight);
    if( ExprHasProperty(pExpr, EP_xIsSelect) ){
      substSelect(pSubst, pExpr->x.pSelect, 1);
    }else{
      substExprList(pSubst, pExpr->x.pList);
    }








  }
  return pExpr;
}
static void substExprList(
  SubstContext *pSubst, /* Description of the substitution */
  ExprList *pList       /* List to scan and in which to make substitutes */
){







>
>
>
>
>
>
>
>







3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
    pExpr->pLeft = substExpr(pSubst, pExpr->pLeft);
    pExpr->pRight = substExpr(pSubst, pExpr->pRight);
    if( ExprHasProperty(pExpr, EP_xIsSelect) ){
      substSelect(pSubst, pExpr->x.pSelect, 1);
    }else{
      substExprList(pSubst, pExpr->x.pList);
    }
#ifndef SQLITE_OMIT_WINDOWFUNC
    if( ExprHasProperty(pExpr, EP_WinFunc) ){
      Window *pWin = pExpr->y.pWin;
      pWin->pFilter = substExpr(pSubst, pWin->pFilter);
      substExprList(pSubst, pWin->pPartition);
      substExprList(pSubst, pWin->pOrderBy);
    }
#endif
  }
  return pExpr;
}
static void substExprList(
  SubstContext *pSubst, /* Description of the substitution */
  ExprList *pList       /* List to scan and in which to make substitutes */
){
Changes to src/sqliteInt.h.
2581
2582
2583
2584
2585
2586
2587



2588
2589
2590

2591
2592
2593
2594
2595
2596
2597
*/
#define EXPRDUP_REDUCE         0x0001  /* Used reduced-size Expr nodes */

/*
** True if the expression passed as an argument was a function with
** an OVER() clause (a window function).
*/



#define IsWindowFunc(p) ( \
    ExprHasProperty((p), EP_WinFunc) && p->y.pWin->eFrmType!=TK_FILTER \
)


/*
** A list of expressions.  Each expression may optionally have a
** name.  An expr/name combination can be used in several ways, such
** as the list of "expr AS ID" fields following a "SELECT" or in the
** list of "ID = expr" items in an UPDATE.  A list of expressions can
** also be used as the argument to a function, in which case the a.zName







>
>
>
|

|
>







2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
*/
#define EXPRDUP_REDUCE         0x0001  /* Used reduced-size Expr nodes */

/*
** True if the expression passed as an argument was a function with
** an OVER() clause (a window function).
*/
#ifdef SQLITE_OMIT_WINDOWFUNC
# define IsWindowFunc(p) 0
#else
# define IsWindowFunc(p) ( \
    ExprHasProperty((p), EP_WinFunc) && p->y.pWin->eFrmType!=TK_FILTER \
 )
#endif

/*
** A list of expressions.  Each expression may optionally have a
** name.  An expr/name combination can be used in several ways, such
** as the list of "expr AS ID" fields following a "SELECT" or in the
** list of "ID = expr" items in an UPDATE.  A list of expressions can
** also be used as the argument to a function, in which case the a.zName
Changes to test/filter1.test.
159
160
161
162
163
164
165




















166
167
168
} {2.0 5.0 10.0}
do_execsql_test 4.3 {
  SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c);
} {c 2.0 a 10.0 b 5.0}
do_execsql_test 4.4 {
  SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2
} {c 2.0 b 5.0 a 10.0}





















finish_test








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



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
} {2.0 5.0 10.0}
do_execsql_test 4.3 {
  SELECT a, avg(c) FILTER (WHERE b!=1) AS h FROM t1 GROUP BY a ORDER BY avg(c);
} {c 2.0 a 10.0 b 5.0}
do_execsql_test 4.4 {
  SELECT a, avg(c) FILTER (WHERE b!=1) FROM t1 GROUP BY a ORDER BY 2
} {c 2.0 b 5.0 a 10.0}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(1, 3);
}

do_execsql_test 5.1 {
  SELECT count(*) FILTER (WHERE b>2) FROM (SELECT * FROM t1) 
} {1}

do_execsql_test 5.2 {
  SELECT count(*) FILTER (WHERE b>2) OVER () FROM (SELECT * FROM t1) 
} {1 1}

do_execsql_test 5.3 {
  SELECT count(*) FILTER (WHERE b>2) OVER (ORDER BY b) FROM (SELECT * FROM t1) 
} {0 1}

finish_test

Changes to test/window1.test.
1185
1186
1187
1188
1189
1190
1191












































1192
1193
    count () OVER win3
  FROM t1
  WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING
                  EXCLUDE CURRENT ROW),
         win2 AS (PARTITION BY b ORDER BY a),
         win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING );
} {1 1}













































finish_test







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


1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
    count () OVER win3
  FROM t1
  WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING
                  EXCLUDE CURRENT ROW),
         win2 AS (PARTITION BY b ORDER BY a),
         win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING );
} {1 1}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 31.1 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(c, d);
  CREATE TABLE t3(e, f);

  INSERT INTO t1 VALUES(1, 1);
  INSERT INTO t2 VALUES(1, 1);
  INSERT INTO t3 VALUES(1, 1);
}

do_execsql_test 31.2 {
  SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM (
    SELECT * FROM t2
  );
} {1}

do_execsql_test 31.3 {
  SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM (
    SELECT * FROM t2
  );
} {1}

do_catchsql_test 31.3 {
  SELECT d IN (
    SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING) 
    FROM t3
  )
  FROM (
    SELECT * FROM t2
  );
} {1 {frame starting offset must be a non-negative integer}}

do_catchsql_test 31.3 {
  SELECT d IN (
    SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING) 
    FROM t3
  )
  FROM (
    SELECT * FROM t2
  );
} {1 {frame ending offset must be a non-negative integer}}

finish_test