/ Check-in [05897ca4]
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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 05897ca48a40c6771ff83ba8ecc3a5c60dafddf58651c222dd8cf89b9fc7b077
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

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