Home Original page

SQLite: View Ticket

2015-11-02

11:19 When creating an automatic-index on a sub-query, add a unique integer to the end of each index key to ensure the entire key is unique. Fix for [8a2adec1]. (check-in: c0c4b6b3 user: drh tags: branch-3.9)

2015-10-24

20:34 • Ticket [8a2adec1] Problem creating automatic-indexes on sub-queries in the FROM clause of correlated scalar sub-queries status still Closed with 3 other changes (artifact: 831a2f9b user: dan)
20:34 • Closed ticket [8a2adec1]. (artifact: 5529b5aa user: dan)
20:31 When creating an automatic-index on a sub-query, add a unique integer to the end of each index key to ensure the entire key is unique. Fix for [8a2adec1]. (check-in: bfea226d user: dan tags: trunk)
19:32 • New ticket [8a2adec1] Problem creating automatic-indexes on sub-queries in the FROM clause of correlated scalar sub-queries. (artifact: 13db9a52 user: dan)
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].