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 | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
f62f983b56623f0ec34f9a54ce1c21b0 |
| User & Date: | dan 2020-12-22 16:23:29 |
| Original 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. |
References
|
2020-12-30
| ||
| 13:20 | New test case for the HAVING fix of check-in [f62f983b56623f0e]. (check-in: 45f46317 user: drh tags: trunk) | |
Context
|
2021-01-19
| ||
| 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) | |
|
2020-12-22
| ||
| 19:57 | Fix a couple spelling typos in comments. (check-in: 907ddf86 user: mistachkin tags: trunk) | |
| 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) | |
| 14:54 | Simplification to the aggregate-function analysis error detection logic at the end of sqlite3Select(). (check-in: 82884438 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
5692 5693 5694 5695 5696 5697 5698 |
** 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;
| | > > | 5692 5693 5694 5695 5696 5697 5698 5699 5700 5701 5702 5703 5704 5705 5706 5707 5708 |
** 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 |
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"
| | | | 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
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
|
| ︙ | ︙ | |||
149 150 151 152 153 154 155 156 157 158 |
# 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
| > > > > > > > > > > > > > > > > > > > > | 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 |
# 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
|