Quoting Simon Slavin <slav...@bigfraud.org>:
On 8 Nov 2012, at 5:27pm, stahl...@dbs.uni-hannover.de wrote:
But inferring the FK's type from the referenced PK would cause applications
which rely on the FK's type affinity being 'none' to be broken, no?
At this sort of level of bug-compatibility, you have to say "Will
not be fixed until SQLite4."
:-)
In order to meet you guys half way, I thought I cut back on my zeal.
But I'll try to refine my example to make my point more clearly:
Consider these two tables:
CREATE TABLE tab1 (x INTEGER PRIMARY KEY);
CREATE TABLE tab2 (x PRIMARY KEY REFERENCES tab1);
Assuming they contain the same rows, I expect any query against 'tab1' to
return the same rows as against 'tab2'.
(I don't know what the SQL Standard has to say about this situation, but I do
think that most users would share my expectation.)
However with SQLite there are queries which yield incoherent results:
INSERT INTO tab1 VALUES (42);
INSERT INTO tab2 VALUES (42);
SELECT * FROM tab1 WHERE x = '42'; --> one row
SELECT * FROM tab2 WHERE x = '42'; --> no rows
I understand that this behavior is documented, but I do think it is simply
wrong *for this case*.
As a side note:
The use of an implicit type conversion from text '42' to integer may
seem a bit
obscure. However queries of this form may appear more often than one might
think: For example the Perl DBI driver for SQLite always uses text values to
execute prepared queries by default.
(See https://rt.cpan.org/Public/Bug/Display.html?id=80676 for the previous
step of my bug-hunting odyssey...)
After all, I must admit that the problem is not really pressing anymore for
me personally: The proper workaround (always explicitly specify the data type
for FK-columns) is acceptable to me.
Also I don't really care *how* this is fixed -- be it by type-inheritance from
the PK (like Oracle does) or by simply denying the syntax for 'tab2' (like in
PostgreSQL).
If this problem is at least officially recognized, I'm okay with a fix in
SQLite4. :-)
kind regards,
Christian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
