/ Check-in [2f0a564f]
Login

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

Overview
Comment:Fix a problem where self-joins on views that are aggregate queries may return the wrong result. Cherrypick of [74ef97bf51dd531a].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.28
Files: files | file ages | folders
SHA3-256: 2f0a564f6ef148e4f63b11ccfc9d1e65cb2755cf923e22f2ffef83b1f176943b
User & Date: mistachkin 2019-07-18 20:55:07
Context
2020-01-17
14:30
Import the 3.31.0-beta FTS3/4 code directly into the 3.28 branch, thus providing 3.28 with all the latest 3.31 fixes. FTS3 has not been enhanced to use any core functionality that was not already available in 3.23, so no modifications were made to FTS3 sources for this import. (check-in: be4269c6 user: drh tags: branch-3.28)
2019-07-18
20:55
Fix a problem where self-joins on views that are aggregate queries may return the wrong result. Cherrypick of [74ef97bf51dd531a]. (check-in: 2f0a564f user: mistachkin tags: branch-3.28)
2019-06-03
15:10
Fix harmless compiler warning in lemon. (check-in: 3a052429 user: mistachkin tags: branch-3.28)
2019-05-03
17:19
Fix a problem where self-joins on views that are aggregate queries may return the wrong result. (check-in: 74ef97bf user: dan tags: trunk)
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  5477   5477       if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue;
  5478   5478       pS1 = pItem->pSelect;
  5479   5479       if( pThis->pSelect->selId!=pS1->selId ){
  5480   5480         /* The query flattener left two different CTE tables with identical
  5481   5481         ** names in the same FROM clause. */
  5482   5482         continue;
  5483   5483       }
  5484         -    if( sqlite3ExprCompare(0, pThis->pSelect->pWhere, pS1->pWhere, -1) ){
         5484  +    if( sqlite3ExprCompare(0, pThis->pSelect->pWhere, pS1->pWhere, -1)
         5485  +     || sqlite3ExprCompare(0, pThis->pSelect->pHaving, pS1->pHaving, -1) 
         5486  +    ){
  5485   5487         /* The view was modified by some other optimization such as
  5486   5488         ** pushDownWhereTerms() */
  5487   5489         continue;
  5488   5490       }
  5489   5491       return pItem;
  5490   5492     }
  5491   5493     return 0;

Changes to test/view.test.

   696    696   set res [list {SQLITE_DELETE sqlite_stat1 {} main {}}]
   697    697   ifcapable stat4 { lappend res {SQLITE_DELETE sqlite_stat4 {} main {}} }
   698    698   do_test view-25.2 {
   699    699     set log ""
   700    700     db eval {DROP TABLE t25;}
   701    701     set log
   702    702   } $res
          703  +
          704  +#-------------------------------------------------------------------------
          705  +do_execsql_test view-26.0 {
          706  +  CREATE TABLE t16(a, b, c UNIQUE);
          707  +  INSERT INTO t16 VALUES(1, 1, 1);
          708  +  INSERT INTO t16 VALUES(2, 2, 2);
          709  +  INSERT INTO t16 VALUES(3, 3, 3);
          710  +  CREATE VIEW v16 AS SELECT max(a) AS mx, min(b) AS mn FROM t16 GROUP BY c;
          711  +
          712  +  SELECT * FROM v16 AS one, v16 AS two WHERE one.mx=1;
          713  +} {
          714  +  1 1 1 1 
          715  +  1 1 2 2 
          716  +  1 1 3 3
          717  +}
   703    718   
   704    719   finish_test