Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem handling sub-queries with both a correlated WHERE clause and a "HAVING 0" clause where the parent query is itself an aggregate. The problem was apparently introduced by check-in [6e6b3729e0549de0] |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | branch-3.34 |
| Files: | files | file ages | folders |
| SHA3-256: |
30a4c323650cc9498a6dacd1760dd517 |
| User & Date: | drh 2021-01-19 20:09:44 |
Context
|
2021-01-19
| ||
| 20:17 | Bump the version number to 3.34.1. (check-in: cea7d0f8 user: drh tags: branch-3.34) | |
| 20:09 | Fix a problem handling sub-queries with both a correlated WHERE clause and a "HAVING 0" clause where the parent query is itself an aggregate. The problem was apparently introduced by check-in [6e6b3729e0549de0] (check-in: 30a4c323 user: drh tags: branch-3.34) | |
| 20:04 | Prevent potential segfault in the sqlite-expert idxPopulateStat1 context cleanup code. (check-in: 69e31d52 user: drh tags: branch-3.34) | |
|
2020-12-22
| ||
| 16:23 | Fix a problem handling sub-queries with both a correlated WHERE clause and a "HAVING 0" clause where the parent query is itself an aggregate. The problem was apparently introduced by check-in [6e6b3729e0549de0] (check-in: f62f983b user: dan tags: trunk) | |
Changes
Changes to src/select.c.
5611 5612 5613 5614 5615 5616 5617 5618 5619 5620 5621 5622 5623 5624 5625 |
** sub-expression matches the criteria for being moved to the WHERE
** clause. If so, add it to the WHERE clause and replace the sub-expression
** within the HAVING expression with a constant "1".
*/
static int havingToWhereExprCb(Walker *pWalker, Expr *pExpr){
if( pExpr->op!=TK_AND ){
Select *pS = pWalker->u.pSelect;
if( sqlite3ExprIsConstantOrGroupBy(pWalker->pParse, pExpr, pS->pGroupBy) ){
sqlite3 *db = pWalker->pParse->db;
Expr *pNew = sqlite3Expr(db, TK_INTEGER, "1");
if( pNew ){
Expr *pWhere = pS->pWhere;
SWAP(Expr, *pNew, *pExpr);
pNew = sqlite3ExprAnd(pWalker->pParse, pWhere, pNew);
pS->pWhere = pNew;
|
| > > |
5611 5612 5613 5614 5615 5616 5617 5618 5619 5620 5621 5622 5623 5624 5625 5626 5627 |
** sub-expression matches the criteria for being moved to the WHERE
** clause. If so, add it to the WHERE clause and replace the sub-expression
** within the HAVING expression with a constant "1".
*/
static int havingToWhereExprCb(Walker *pWalker, Expr *pExpr){
if( pExpr->op!=TK_AND ){
Select *pS = pWalker->u.pSelect;
if( sqlite3ExprIsConstantOrGroupBy(pWalker->pParse, pExpr, pS->pGroupBy)
&& ExprAlwaysFalse(pExpr)==0
){
sqlite3 *db = pWalker->pParse->db;
Expr *pNew = sqlite3Expr(db, TK_INTEGER, "1");
if( pNew ){
Expr *pWhere = pS->pWhere;
SWAP(Expr, *pNew, *pExpr);
pNew = sqlite3ExprAnd(pWalker->pParse, pWhere, pNew);
pS->pWhere = pNew;
|
Changes to test/having.test.
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
...
149
150
151
152
153
154
155
156
157
158
|
2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>5 AND a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a HAVING sum(b)>5"
3 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE binary"
5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 0"
"SELECT a, sum(b) FROM t1 WHERE 0 GROUP BY a COLLATE binary"
6 "SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d"
"SELECT count(*) FROM t1,t2 WHERE a=c AND b=d GROUP BY b, d"
7 {
SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d
HAVING b=d COLLATE nocase
................................................................................
# If the term where moved, the query above would return the same
# result as the following. But it does not.
#
set ::nondeter_ret 0
do_execsql_test 4.3 {
SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a
} {1 4 2 2}
finish_test
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
...
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
|
2 "SELECT a, sum(b) FROM t1 GROUP BY a HAVING sum(b)>5 AND a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a HAVING sum(b)>5"
3 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING a=2"
"SELECT a, sum(b) FROM t1 WHERE a=2 GROUP BY a COLLATE binary"
5 "SELECT a, sum(b) FROM t1 GROUP BY a COLLATE binary HAVING 1"
"SELECT a, sum(b) FROM t1 WHERE 1 GROUP BY a COLLATE binary"
6 "SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d HAVING b=d"
"SELECT count(*) FROM t1,t2 WHERE a=c AND b=d GROUP BY b, d"
7 {
SELECT count(*) FROM t1,t2 WHERE a=c GROUP BY b, d
HAVING b=d COLLATE nocase
................................................................................
# If the term where moved, the query above would return the same
# result as the following. But it does not.
#
set ::nondeter_ret 0
do_execsql_test 4.3 {
SELECT a, sum(b) FROM t3 WHERE nondeter(a) GROUP BY a
} {1 4 2 2}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
CREATE TABLE t1(a, b);
CREATE TABLE t2(x, y);
INSERT INTO t1 VALUES('a', 'b');
}
# The WHERE clause (a=2), uses an aggregate column from the outer query.
# If the HAVING term (0) is moved into the WHERE clause in this case,
# SQLite would at one point optimize (a=2 AND 0) to simply (0). Which
# is logically correct, but happened to cause problems in aggregate
# processing for the outer query. This test case verifies that those
# problems are no longer present.
do_execsql_test 5.1 {
SELECT min(b), (
SELECT x FROM t2 WHERE a=2 GROUP BY y HAVING 0
) FROM t1;
} {b {}}
finish_test
|