SQLite
View Ticket
Not logged in
Ticket UUID: 7ffd1ca1d2ad4ecf960e83816b8968184bdc20cb
Title: Incorrect affinity when using automatic indexes
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-01-16 18:10:28
Version Found In: 3.16.2
User Comments:
drh added on 2017-01-16 15:10:18:

In the script below, the first query which uses automatic indexes gets a different and incorrect answer than the other three:

CREATE TABLE map_integer (id INT, name);
INSERT INTO map_integer VALUES(1,'a');
CREATE TABLE map_text (id TEXT, name);
INSERT INTO map_text VALUES('4','e');
CREATE TABLE data (id TEXT, name);
INSERT INTO data VALUES(1,'abc');
INSERT INTO data VALUES('4','xyz');
CREATE VIEW idmap as
    SELECT * FROM map_integer
    UNION SELECT * FROM map_text;
CREATE TABLE mzed AS SELECT * FROM idmap;

.print ------ idmap:
SELECT * FROM data JOIN idmap USING(id);
.print ------ mzed:
SELECT * FROM data JOIN mzed USING(id);
.print ------ id_map1 with auto-index off:
PRAGMA automatic_index=off;
SELECT * FROM data JOIN idmap USING(id);
.print ------ mzed with auto-index off:
SELECT * FROM data JOIN mzed USING(id);

Bisecting shows that this problem was introduced by check-in [0b3174e0b1] on 2011-06-23 and first appeared in SQLite version 3.7.7.