/ 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 | branch-3.28
Files: files | file ages | folders
SHA3-256: 2f0a564f6ef148e4f63b11ccfc9d1e65cb2755cf923e22f2ffef83b1f176943b
User & Date: mistachkin 2019-07-18 20:55:07
Context
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]. Leaf 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