Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix the count-of-view optimization so that it is (correctly) disabled for a query that includes a WHERE clause or a GROUP BY clause. Cherrypick of [05897ca48a40c6771]. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | branch-3.28 |
| Files: | files | file ages | folders |
| SHA3-256: |
583e5a0ab6bdf3ce3d76852089160a8d |
| User & Date: | mistachkin 2019-06-03 13:53:29.317 |
Context
|
2019-06-03
| ||
| 15:10 | Fix harmless compiler warning in lemon. (check-in: 3a05242953 user: mistachkin tags: branch-3.28) | |
| 13:53 | Fix the count-of-view optimization so that it is (correctly) disabled for a query that includes a WHERE clause or a GROUP BY clause. Cherrypick of [05897ca48a40c6771]. (check-in: 583e5a0ab6 user: mistachkin tags: branch-3.28) | |
|
2019-05-15
| ||
| 18:42 | Fix the count-of-view optimization so that it is (correctly) disabled for a query that includes a WHERE clause or a GROUP BY clause. (check-in: 05897ca48a user: drh tags: trunk) | |
|
2019-04-23
| ||
| 15:21 | Minor simplification of NULL value handling for STAT4. Cherrypick of [69bad9257f8db6a2] from trunk. (check-in: 442c177dc4 user: mistachkin tags: branch-3.28) | |
Changes
Changes to src/select.c.
| ︙ | ︙ | |||
5502 5503 5504 5505 5506 5507 5508 | ** SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2) ** ** The transformation only works if all of the following are true: ** ** * The subquery is a UNION ALL of two or more terms ** * The subquery does not have a LIMIT clause ** * There is no WHERE or GROUP BY or HAVING clauses on the subqueries | | > > > | 5502 5503 5504 5505 5506 5507 5508 5509 5510 5511 5512 5513 5514 5515 5516 5517 5518 5519 5520 5521 5522 5523 5524 5525 5526 5527 5528 5529 |
** SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)
**
** The transformation only works if all of the following are true:
**
** * The subquery is a UNION ALL of two or more terms
** * The subquery does not have a LIMIT clause
** * There is no WHERE or GROUP BY or HAVING clauses on the subqueries
** * The outer query is a simple count(*) with no WHERE clause or other
** extraneous syntax.
**
** Return TRUE if the optimization is undertaken.
*/
static int countOfViewOptimization(Parse *pParse, Select *p){
Select *pSub, *pPrior;
Expr *pExpr;
Expr *pCount;
sqlite3 *db;
if( (p->selFlags & SF_Aggregate)==0 ) return 0; /* This is an aggregate */
if( p->pEList->nExpr!=1 ) return 0; /* Single result column */
if( p->pWhere ) return 0;
if( p->pGroupBy ) return 0;
pExpr = p->pEList->a[0].pExpr;
if( pExpr->op!=TK_AGG_FUNCTION ) return 0; /* Result is an aggregate */
if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0; /* Is count() */
if( pExpr->x.pList!=0 ) return 0; /* Must be count(*) */
if( p->pSrc->nSrc!=1 ) return 0; /* One table in FROM */
pSub = p->pSrc->a[0].pSelect;
if( pSub==0 ) return 0; /* The FROM is a subquery */
|
| ︙ | ︙ |
Changes to test/countofview.test.
| ︙ | ︙ | |||
35 36 37 38 39 40 41 42 43 |
} {1}
do_execsql_test 1.3 {
select count(*) from (
select c from t2 union all select f from t3
)
} {3}
finish_test
| > > > > > > > > > > > > > | 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
} {1}
do_execsql_test 1.3 {
select count(*) from (
select c from t2 union all select f from t3
)
} {3}
# 2019-05-15
do_execsql_test 2.0 {
CREATE TABLE t1(x);
INSERT INTO t1 VALUES(1),(99),('abc');
CREATE VIEW v1(x,y) AS SELECT x,1 FROM t1 UNION ALL SELECT x,2 FROM t1;
SELECT count(*) FROM v1 WHERE x<>1;
} {4}
do_execsql_test 2.1 {
SELECT count(*) FROM v1 GROUP BY y;
} {3 3}
finish_test
|