SQLite

Check-in [583e5a0ab6]
Login

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: 583e5a0ab6bdf3ce3d76852089160a8d11df4c3dd151050a70997b3720c3d538
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
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/select.c.
︙
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

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(*)
**   *  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

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