Quoting Ryan Johnson <ryan.john...@cs.utoronto.ca>:
On 07/11/2012 7:58 PM, Simon Davies wrote:
On 7 November 2012 20:36, <stahl...@dbs.uni-hannover.de> wrote:
[...]
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
[...]
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.
Okay, but at least Oracle and PostgreSQL don't claim to do their
auto-coerce-voodoo because of compatibility with other database engines. :-]
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.
Interesting... so I'm not the only one bitten by this.
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.
[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.
There are actually users *relying* on this incoherent behaviour?
Granted, I don't have insight in the full consequences of this problem,
but I find that hard to believe: This would clearly be bad database design.
If this is actually a concern, then I think the best way of fixing would be
to do what PostgreSQL does: Make '[column-name] REFERENCES...' a syntax error
and explicitly require a datatype in this case.
Thanks for your insight!
regards,
Christian
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
