Problem appears to be coming from an automatic index. sqlite> select * from data left join id_map using (id); --EQP-- 2,0,0,SCAN TABLE map_integer --EQP-- 3,0,0,SCAN TABLE map_text --EQP-- 1,0,0,COMPOUND SUBQUERIES 2 AND 3 (UNION ALL) --EQP-- 0,0,0,SCAN TABLE data --EQP-- 0,1,1,SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (id=?) id|flag|name 1|0|a 2|1|b 3|1|c 4|0|NULL 3,12|1|d
sqlite> pragma automatic_index = off; sqlite> select * from data left join id_map using (id); --EQP-- 2,0,0,SCAN TABLE map_integer --EQP-- 3,0,0,SCAN TABLE map_text --EQP-- 1,0,0,COMPOUND SUBQUERIES 2 AND 3 (UNION ALL) --EQP-- 0,0,0,SCAN TABLE data --EQP-- 0,1,1,SCAN SUBQUERY 1 id|flag|name 1|0|a 2|1|b 3|1|c 4|0|e 3,12|1|d -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Adrian Stachlewski Sent: Wednesday, December 21, 2016 8:14 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Possible bug with union and join. Hi everyone. When I was working with sqlite3 I've found weird behavior of JOIN clause when I was trying to merge table with union of tables. Let me explain this using database with reproduced problem. Database dump: CREATE TABLE map_integer (id INTEGER PRIMARY KEY, name TEXT); INSERT INTO "map_integer" VALUES(1,'a'); INSERT INTO "map_integer" VALUES(2,'b'); INSERT INTO "map_integer" VALUES(3,'c'); CREATE TABLE map_text (id TEXT PRIMARY KEY, name TEXT); INSERT INTO "map_text" VALUES('4','e'); INSERT INTO "map_text" VALUES('3,12','d'); CREATE TABLE data (id TEXT PRIMARY KEY, flag BOOLEAN); INSERT INTO "data" VALUES('1',0); INSERT INTO "data" VALUES('2',1); INSERT INTO "data" VALUES('3',1); INSERT INTO "data" VALUES('4',0); INSERT INTO "data" VALUES('3,12',1); View for map: CREATE VIEW id_map as SELECT * FROM map_integer UNION ALL SELECT * FROM map_text; Select statements: SELECT * FROM data LEFT JOIN id_map USING(id); SELECT * FROM data LEFT JOIN id_map ON data.id = id_map.id; Both of select statements give wrong output: 1|0|a 2|1|b 3|1|c 4|0| 3,12|1|d Expected result: 1|0|a 2|1|b 3|1|c 4|0|e 3,12|1|d Another tests I've made: 1. view to CTE - same problem 2. left join to join - same problem, there's no row with id=4 3. change the order of select statements in view - not working, output: 1|0| 2|1| 3|1| 4|0|e 3,12|1|d My explanation of the problem and workaround: There is problem when UNION is made on different types in one column. When I've cast id to text in view or when joining (... ON data.id = CAST( id_map.id AS TEXT) everything it's working fine. Unfortunately this workaround makes no sense for me, as long as SELECT CAST(1 AS TEXT) = CAST(1 AS INTEGER); gives True. SQLite versions: - 3.13.0 - 3.15.2 - pre-release snapshot OS: Ubuntu 16.04.1 LTS with 4.4.0-57-generic I hope that I've described all necessary informations. Of course feel free to ask me some questions if there's some ambiguous part. Best regards, Adrian Stachlewski _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
