SQLite

View Ticket
Login
Ticket Hash: 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:

This:

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;

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:

Fixed by [bfea226d].