SQLite: Check-in [583e5a0a]

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 \| SQL 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

Context

2019-06-03
15:10		Fix harmless compiler warning in lemon. (check-in: 3a052429 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: 583e5a0a 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: 05897ca4 user: drh tags: trunk)
2019-04-23
15:21		Minor simplification of NULL value handling for STAT4. Cherrypick of [69bad9257f8db6a2] from trunk. (check-in: 442c177d user: mistachkin tags: branch-3.28)

Changes

Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  5502   5502   **    SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)
  5503   5503   **
  5504   5504   ** The transformation only works if all of the following are true:
  5505   5505   **
  5506   5506   **   *  The subquery is a UNION ALL of two or more terms
  5507   5507   **   *  The subquery does not have a LIMIT clause
  5508   5508   **   *  There is no WHERE or GROUP BY or HAVING clauses on the subqueries
  5509         -**   *  The outer query is a simple count(*)
         5509  +**   *  The outer query is a simple count(*) with no WHERE clause or other
         5510  +**      extraneous syntax.
  5510   5511   **
  5511   5512   ** Return TRUE if the optimization is undertaken.
  5512   5513   */
  5513   5514   static int countOfViewOptimization(Parse *pParse, Select *p){
  5514   5515     Select *pSub, *pPrior;
  5515   5516     Expr *pExpr;
  5516   5517     Expr *pCount;
  5517   5518     sqlite3 *db;
  5518   5519     if( (p->selFlags & SF_Aggregate)==0 ) return 0;   /* This is an aggregate */
  5519   5520     if( p->pEList->nExpr!=1 ) return 0;               /* Single result column */
         5521  +  if( p->pWhere ) return 0;
         5522  +  if( p->pGroupBy ) return 0;
  5520   5523     pExpr = p->pEList->a[0].pExpr;
  5521   5524     if( pExpr->op!=TK_AGG_FUNCTION ) return 0;        /* Result is an aggregate */
  5522   5525     if( sqlite3_stricmp(pExpr->u.zToken,"count") ) return 0;  /* Is count() */
  5523   5526     if( pExpr->x.pList!=0 ) return 0;                 /* Must be count(*) */
  5524   5527     if( p->pSrc->nSrc!=1 ) return 0;                  /* One table in FROM  */
  5525   5528     pSub = p->pSrc->a[0].pSelect;
  5526   5529     if( pSub==0 ) return 0;                           /* The FROM is a subquery */

Changes to test/countofview.test.

    35     35   } {1}
    36     36   
    37     37   do_execsql_test 1.3 {
    38     38     select count(*) from (
    39     39       select c from t2 union all select f from t3
    40     40     )
    41     41   } {3}
           42  +
           43  +# 2019-05-15
           44  +do_execsql_test 2.0 {
           45  +  CREATE TABLE t1(x);
           46  +  INSERT INTO t1 VALUES(1),(99),('abc');
           47  +  CREATE VIEW v1(x,y) AS SELECT x,1 FROM t1 UNION ALL SELECT x,2 FROM t1;
           48  +  SELECT count(*) FROM v1 WHERE x<>1;
           49  +} {4}
           50  +do_execsql_test 2.1 {
           51  +  SELECT count(*) FROM v1 GROUP BY y;
           52  +} {3 3}
           53  +
           54  +
    42     55   
    43     56   finish_test