On 07/11/2012 7:58 PM, Simon Davies wrote:
On 7 November 2012 20:36, <stahl...@dbs.uni-hannover.de> wrote:
Quoting Simon Davies <simon.james.dav...@gmail.com>:
.
.
.
I think this is the documented behaviour:
http://www.sqlite.org/datatype3.html
tab1.id has integer affinity, and '42' is coerced to integer
tab2.id has none affinity, and '42' is not coerced
Hmm... I see what you mean:
Point 3 under 2.1 states that "if no type is specified then the column has
affinity NONE."
However, I find a foreign-key-clause counting as "no type specified" is at
least a bit irritating. After all the type could be inferred from the
reference. :-/
Also, ironically, the documentation claims that "The dynamic type system of
SQLite is backwards compatible with the more common static type systems of
other database engines...".
I just checked the example with the Oracle and PostgreSQL instances I have
at hand here:
Oracle does what I think is correct and returns a row in both cases.
PostgreSQL does not allow the "id REFERENCES" construction at all and
requires a data type even for foreign keys.
So in this case SQLite is incompatible with two major DBMSes. :-)
Which from what you have said, are also incompatible with each other!
Man with sharp stick has point.
To be fair, though, I have been bitten numerous times by exactly this
same scenario, where foreign key joins fail because the key types
somehow end up differing and don't coerce automatically. Very easy to
forget, or to accidentally let a string slip in where an int was
intended (say, by loading from csv). Also hard to diagnose.
Not sure the best way to "fix" the problem [1], but it might be the
single most surprising aspect of using sqlite3 in my experience.
Ryan
[1] especially since there's probably a customer out there somewhere
whose app actually depends on foreign key join columns having different
types and not matching '24' with 24.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
