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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
