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. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
05897ca48a40c6771ff83ba8ecc3a5c6 |
| User & Date: | drh 2019-05-15 18:42:15 |
References
|
2019-06-03
| ||
| 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: 583e5a0a user: mistachkin tags: branch-3.28 | |
Context
|
2019-06-03
| ||
| 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: 583e5a0a user: mistachkin tags: branch-3.28 | |
|
2019-05-16
| ||
| 01:22 | Make sure the OP_Concat opcode always correctly zero-terminates a UTF16 string, even if the input strings are ill-formed. This is a followup to check-in [3a16ddf91f0c9c516a7] that fixes a case the previous check-in missed. Also add assert()s to prove correct zero termination. check-in: d612fb78 user: drh tags: trunk | |
|
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: 05897ca4 user: drh tags: trunk | |
| 10:16 | Simplify the "Verifying Code Authenticity" section of the README.md file. No code changes. check-in: adebffc1 user: drh tags: trunk | |
Changes
Changes to src/select.c.
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 |
** 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(*)
**
** 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 */
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 */
|
| > > > |
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 5530 5531 |
** 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
|