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 |