/ Check-in [8424492e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional comments on the sqlite3ExprIsConstantOrGroupBy() routine. No code changes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | having-where-optimization
Files: files | file ages | folders
SHA3-256:8424492eac506866d2918e5fe03c8f65fef960215d56012a3b52ed42789ed35a
User & Date: drh 2017-05-02 16:46:41
Context
2017-05-02
16:55
Move terms of the HAVING clause that reference only columns in the GROUP BY clause over to the WHERE clause, resulting in a faster query plan. check-in: 47cbb471 user: drh tags: trunk
16:46
Additional comments on the sqlite3ExprIsConstantOrGroupBy() routine. No code changes. Closed-Leaf check-in: 8424492e user: drh tags: having-where-optimization
2017-05-01
19:53
Remove an unnecessary branch. check-in: a3317959 user: drh tags: having-where-optimization
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1844   1844     return exprNodeIsConstant(pWalker, pExpr);
  1845   1845   }
  1846   1846   
  1847   1847   /*
  1848   1848   ** Walk the expression tree passed as the first argument. Return non-zero
  1849   1849   ** if the expression consists entirely of constants or copies of terms 
  1850   1850   ** in pGroupBy that sort with the BINARY collation sequence.
         1851  +**
         1852  +** This routine is used to determine if a term of the HAVING clause can
         1853  +** be promoted into the WHERE clause.  In order for such a promotion to work,
         1854  +** the value of the HAVING clause term must be the same for all members of
         1855  +** a "group".  The requirement that the GROUP BY term must be BINARY
         1856  +** assumes that no other collating sequence will have a finer-grained
         1857  +** grouping than binary.  In other words (A=B COLLATE binary) implies
         1858  +** A=B in every other collating sequence.  The requirement that the
         1859  +** GROUP BY be BINARY is stricter than necessary.  It would also work
         1860  +** to promote HAVING clauses that use the same alternative collating
         1861  +** sequence as the GROUP BY term, but that is much harder to check,
         1862  +** alternative collating sequences are uncommon, and this is only an
         1863  +** optimization, so we take the easy way out and simply require the
         1864  +** GROUP BY to use the BINARY collating sequence.
  1851   1865   */
  1852   1866   int sqlite3ExprIsConstantOrGroupBy(Parse *pParse, Expr *p, ExprList *pGroupBy){
  1853   1867     Walker w;
  1854   1868     memset(&w, 0, sizeof(w));
  1855   1869     w.eCode = 1;
  1856   1870     w.xExprCallback = exprNodeIsConstantOrGroupBy;
  1857   1871     w.u.pGroupBy = pGroupBy;