SQLite
View Ticket
Not logged in
Ticket UUID: 8a2adec166701e42e6f5dab214f80a1340864bb4
Title: Problem creating automatic-indexes on sub-queries in the FROM clause of correlated scalar sub-queries
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2015-10-24 20:34:52
Version Found In: 3.8.11 - 3.9.1
User Comments:
dan added on 2015-10-24 19:32:39: (text/x-fossil-wiki)
This:

<verbatim>
CREATE TABLE t1(x, z);
INSERT INTO t1 VALUES('aaa', 4.0);
INSERT INTO t1 VALUES('aaa', 4.0);
CREATE VIEW vvv AS
  SELECT * FROM t1
    UNION ALL
  SELECT 0, 0 WHERE 0;

SELECT (
  SELECT sum(z) FROM vvv WHERE x='aaa'
) FROM sqlite_master;
</verbatim>

currently returns two rows containing the single value 4.0 (instead of 8.0). The problem is that when SQLite creates an automatic index on view "vvv", it incorrectly discards duplicate rows - making the sum() expression equivalent to just 4.0 instead of (4.0 + 4.0). 

Problem introduced here:

  http://www.sqlite.org/src/info/020b8b106fc8f840

First appeared in release 3.8.11.

dan added on 2015-10-24 20:34:34: (text/x-fossil-wiki)
Fixed by [bfea226d].