SQLite

Check-in [2f0a564f6e]
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
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.058
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: be4269c624 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: 2f0a564f6e user: mistachkin tags: branch-3.28)
2019-06-03
15:10
Fix harmless compiler warning in lemon. (check-in: 3a05242953 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: 74ef97bf51 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
5477
5478
5479
5480
5481
5482
5483
5484


5485
5486
5487
5488
5489
5490
5491

    if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue;
    pS1 = pItem->pSelect;
    if( pThis->pSelect->selId!=pS1->selId ){
      /* The query flattener left two different CTE tables with identical
      ** names in the same FROM clause. */
      continue;
    }
    if( sqlite3ExprCompare(0, pThis->pSelect->pWhere, pS1->pWhere, -1) ){


      /* The view was modified by some other optimization such as
      ** pushDownWhereTerms() */
      continue;
    }
    return pItem;
  }
  return 0;








|
>
>








5477
5478
5479
5480
5481
5482
5483
5484
5485
5486
5487
5488
5489
5490
5491
5492
5493

    if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue;
    pS1 = pItem->pSelect;
    if( pThis->pSelect->selId!=pS1->selId ){
      /* The query flattener left two different CTE tables with identical
      ** names in the same FROM clause. */
      continue;
    }
    if( sqlite3ExprCompare(0, pThis->pSelect->pWhere, pS1->pWhere, -1)
     || sqlite3ExprCompare(0, pThis->pSelect->pHaving, pS1->pHaving, -1) 
    ){
      /* The view was modified by some other optimization such as
      ** pushDownWhereTerms() */
      continue;
    }
    return pItem;
  }
  return 0;
︙︙
Changes to test/view.test.
︙︙
696
697
698
699
700
701
702















703
704

set res [list {SQLITE_DELETE sqlite_stat1 {} main {}}]
ifcapable stat4 { lappend res {SQLITE_DELETE sqlite_stat4 {} main {}} }
do_test view-25.2 {
  set log ""
  db eval {DROP TABLE t25;}
  set log
} $res
















finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719

set res [list {SQLITE_DELETE sqlite_stat1 {} main {}}]
ifcapable stat4 { lappend res {SQLITE_DELETE sqlite_stat4 {} main {}} }
do_test view-25.2 {
  set log ""
  db eval {DROP TABLE t25;}
  set log
} $res

#-------------------------------------------------------------------------
do_execsql_test view-26.0 {
  CREATE TABLE t16(a, b, c UNIQUE);
  INSERT INTO t16 VALUES(1, 1, 1);
  INSERT INTO t16 VALUES(2, 2, 2);
  INSERT INTO t16 VALUES(3, 3, 3);
  CREATE VIEW v16 AS SELECT max(a) AS mx, min(b) AS mn FROM t16 GROUP BY c;

  SELECT * FROM v16 AS one, v16 AS two WHERE one.mx=1;
} {
  1 1 1 1 
  1 1 2 2 
  1 1 3 3
}

finish_test