/ Check-in [74ef97bf]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 74ef97bf51dd531a277cf22fa4d42043d93799d5a5bd550812648834460fe0b7
User & Date: dan 2019-05-03 17:19:10
References
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-05-22
23:12
New test case for check-in [74ef97bf51dd531a] that takes the fix in the previous check-in into account. check-in: cb1d0652 user: drh tags: trunk
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-05-03
18:50
Fix a memory-leak/segfault caused by using OP_OpenDup and OP_OpenEphemeral on the same VM cursor. check-in: a9b90aa1 user: dan tags: trunk
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
02:41
Fix the ".open --hexdb" command in the CLI so that it works even with terminal input. check-in: 9b5d9434 user: drh 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