On 12/22/16, David Raymond <[email protected]> wrote:
> Problem appears to be coming from an automatic index.
Thanks for the insight, David. Automatic indexes do appear to be a
factor, but not the only factor. The script below shows different
answers depending on whether the VIEW is created with or without
explicit column names:
------------- begin script ---------------
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 id_map1 as
SELECT * FROM map_integer
UNION ALL
SELECT * FROM map_text;
CREATE VIEW id_map2(id,name) as
SELECT * FROM map_integer
UNION ALL
SELECT * FROM map_text;
PRAGMA automatic_index=off;
.print ------ id_map1:
SELECT * FROM data LEFT JOIN id_map1 USING(id);
.print ------ id_map2:
SELECT * FROM data LEFT JOIN id_map2 USING(id);
------------ end script ----------
Adrian:
String are not equal to numbers. You should not expect that '4'==4.
Yes, I know that SQLite will sometimes do this. The complex type
coercion rules were added so that SQL scripts that worked on
PostgreSQL would also work on SQLite. But really, you should avoid
depending on automatic type coercion. If you are having to think
about the type coercion rules, then you are doing it wrong.
Yes - SQLite should give consistent and predictable answers. We will
fix that. Eventually. Once I figure out what it ought to be doing.
But you should not wait on that fix. You should go ahead redesign
your application so that it does not depend on the nuances of type
coercions and so that it does not compare strings against integers and
expect them to be equal.
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
