Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Igor Tandetnik : stahl...@dbs.uni-hannover.de wrote: SQLite's behavior makes sense, because *every* column type may be left out. However, I think that in the case of FK-definitions (like the one in 'tab2') assigning the default type is not the right thing to do. Why should one clause in the syntax behave differently depending on the presence or absence of another, unrelated clause? Sounds like an arbitrary special case to me. I think that's the main point of our disagreement. In my opinion the column type definition is very well related to the foreign-key definition: Barring more specific clauses, a column defined as a foreign key should exactly be like the primary key it references -- including data type, collation rules and any other potential modifiers. This, as I see now, collides with SQLite's view that leaving out a column type, actually means "BLOB". As no one here seems to agree with my view, I will no further pursue this. (Also I'm quite alright with specifying the column type explicitly every time.) My initial 'bug'-report now becomes a well-meant suggestion at best. :-) Thank you very much for your insight! Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
stahl...@dbs.uni-hannover.de wrote: > Quoting Igor Tandetnik : >> stahl...@dbs.uni-hannover.de wrote: >>> 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'. >> >> Why would you expect that? You keep saying this, but I don't >> understand the basis of your expectations - unless it's just the >> good old wishful thinking. > > I base my expectations on two things. > > First (and probably more importantly): > Column 'x' of table 'tab2' is defined as a foreign key referencing the primary > key 'x' of table 'tab1'. > I expect that a foreign key column is in no way different than the primary key > column it points to - unless I explicitly specify it to be different. But you did. I can't help but notice that the two column definitions are indeed different: one specifies the type and the other doesn't. That effectively means that the two columns use different collations. Consider this example, which illustrates the situation more explicitly: create table a(t text collate nocase unique); create table b(t text collate binary references a(t)); insert into a values ('X'); insert into b values ('X'); select * from a where t='x'; -- returns one row select * from b where t='x'; -- returns no rows > (This has nothing to do with SQLite, but with the definition of functional > dependencies in relational databases: A foreign key *is* a primary key from > a foreign table.) Depends on what the meaning of "is" is. SQL-92 requres that foreign key columns match referenced columns according to MATCH predicate: 8.10 Function Specify a test for matching rows. Format ::= MATCH [ UNIQUE ] [ PARTIAL | FULL ] Syntax Rules 1) The shall be of the same degree as the . 2) The data types of the values of the shall be respectively comparable to those of the corresponding columns of the . 3) The collating sequence for each pair of respective values in the is determined in the same manner as described in Subclause 8.2, "". Basically, the columns don't have to be of the same type, and the notion of "matching" is the usual comparison, with collations and everything. > So this means the schema definitions of 'tab1' and 'tab2' must effectively be > the same and thus my assumption above should hold. Which part of which normative document requires this? > As SQLite let me leave out the column type, I thought "Great! SQLite is smart > enough to infer the column type for FKs! Just like Oracle!". This is, of course, a perfect example of wishful thinking. > Which leads to my second reason: The Oracle DBMS does what I expect. So your definition of correctness is "always do what Oracle does", then? In this case, I suggest you only ever use Oracle, to escape disappointment. By this definition, it's the only correctly implemented DBMS in the world. > So yes, I admit that this *is* wishful thinking on my part. > I do think, however, that my wishes are reasonable. You are, naturally, entitled to your optinion, while other people are entitled to theirs. In this case, the balance of opinion doesn't appear to be in your favor. > SQLite's behavior makes sense, because *every* column type may be left out. > However, I think that in the case of FK-definitions (like the one in 'tab2') > assigning the default type is not the right thing to do. Why should one clause in the syntax behave differently depending on the presence or absence of another, unrelated clause? Sounds like an arbitrary special case to me. >>> However with SQLite there are queries which yield incoherent results: >> >> Define "incoherent". As far as I can tell, you use this term to mean >> "results you personally dislike". The results SQLite produces are in >> agreement - in other words, in coherence - with the product >> documentation. > > I just meant 'incoherent' wrt. "The same query returns different results for > the same data." as per my example. As I've shown earlier, this is perfectly normal when two table definitions differ in a way that results in different collation rules being applied. This would be true even for a (largely hypothetical) strictly SQL-92 compliant DBMS. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Simon Slavin : On 10 Nov 2012, at 7:21pm, stahl...@dbs.uni-hannover.de wrote: 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'. Sorry, but you are too optimistic. Yeah, I notice that now. :-) I would expect a schema design tool -- the sort where you move rectangles and pointers around -- to warn you when you put a pointer in from tab2 to tab1, that the affinities should match. But in a language where you specifically declare schema using text, I expect the programmer to have to do this him- or herself. Fair enough. Although I would (optimistically) hope that SQL schema definitions (and thus schema design tools) were portable between DBSes. But this can't happen with the impact SQLite's unique concept of 'type affinities' has on schemas. Thanks for your help tough. Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Igor Tandetnik : stahl...@dbs.uni-hannover.de wrote: 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'. Why would you expect that? You keep saying this, but I don't understand the basis of your expectations - unless it's just the good old wishful thinking. I base my expectations on two things. First (and probably more importantly): Column 'x' of table 'tab2' is defined as a foreign key referencing the primary key 'x' of table 'tab1'. I expect that a foreign key column is in no way different than the primary key column it points to - unless I explicitly specify it to be different. (This has nothing to do with SQLite, but with the definition of functional dependencies in relational databases: A foreign key *is* a primary key from a foreign table.) So this means the schema definitions of 'tab1' and 'tab2' must effectively be the same and thus my assumption above should hold. As SQLite let me leave out the column type, I thought "Great! SQLite is smart enough to infer the column type for FKs! Just like Oracle!". Which leads to my second reason: The Oracle DBMS does what I expect. So yes, I admit that this *is* wishful thinking on my part. I do think, however, that my wishes are reasonable. (I don't know what the SQL Standard has to say about this situation I'm pretty sure the second CREATE TABLE statement is syntactically invalid, per the standard. The column type is mandatory, if I recall correctly. Ah! I think that explains the situation somewhat: SQLite and Oracle expand the standard in the same way by allowing to leave out the column type for FKs. Oracle infers the column type from the PK, but SQLite always uses the default column type BLOB (or 'NONE'..?). (PostgreSQL rejects the definition for 'tab2' as mentioned earlier.) SQLite's behavior makes sense, because *every* column type may be left out. However, I think that in the case of FK-definitions (like the one in 'tab2') assigning the default type is not the right thing to do. but I do think that most users would share my expectation.) I'm not sure what you base this belief on, either. I don't seem to see your argument enjoying widespread support on this thread. Yes, you are obviously right there. I discussed the problem earlier with colleagues (mostly Oracle users), but obviously this list is a different world. :-) However with SQLite there are queries which yield incoherent results: Define "incoherent". As far as I can tell, you use this term to mean "results you personally dislike". The results SQLite produces are in agreement - in other words, in coherence - with the product documentation. I just meant 'incoherent' wrt. "The same query returns different results for the same data." as per my example. You seem to hold this truth to be self-evident, but I honestly don't understand why. Could you explain your reasoning to me? I hope the explanations above make my point at least understandable? Also I don't really care *how* this is fixed As best I can tell, so far there's no agreement that it's broken, so discussing how to fix it seems a bit premature. Agreed there's no agreement. :-) As I said earlier: I'm fine now that I know that I should explicitly specify the column type (even better if that's standard compliant!). I still think that SQLite could be made better by inferring FK column types. But I won't pursue this matter any further if there is no agreement on this. Thank you for your answer! Kind regards, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
On Saturday, 10 November, 2012 13:09 Igor Tandetnik wrote: > > However with SQLite there are queries which yield incoherent results: > Define "incoherent". As far as I can tell, you use this term to mean "results > you personally dislike". The results SQLite produces are in agreement - in > other words, in coherence - with the product documentation. > > 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*. > You seem to hold this truth to be self-evident, but I honestly don't > understand why. Could you explain your reasoning to me? I assume there's more > to it than just you finding the outcome aesthetically displeasing. In my opinion the behaviour of SQLite is exactly correct. It is not the job of the software (SQLite) to be second guessing and imposing its world-view on users of the software. There may be perfectly valid and rational cases where the behaviour being complained about is desired and intended. I cannot at this particular time state what that might be, but it cannot be ruled out. Clearly this is a case of erroneous definition by the user (PLBKAC) with garbage-in resulting in garbage-out (GIGO). In other words, this is a basic misunderstanding of how SQLite works. > > Also I don't really care *how* this is fixed > As best I can tell, so far there's no agreement that it's broken, so > discussing how to fix it seems a bit premature. I don't think SQLite is broken -- it is working as intended and carrying out the expressed intention of the user. If the user wishes to obtain a different result, then the user will need to re-frame the original expression so that when the SQLite function is applied the result desired is obtained. In other words, just because one wants SIN(45) to be .7071, but instead obtains the result .8509, does not mean that the SIN function is broken. It merely means that the user input and expectation is incorrect. The function is not broken, the result is exactly correct. The user is broken for having expectation which do not match documented reality. If a straight-jacket is wanted then one can always choose to wear one and use a language or tool where the world-view of the designer is enforced in preference to merely behaving in a predictable rational manner. Microsoft is very very good at writing software which only works according to the Microsoft world-view and prevents you from doing anything not conforming to that view, even though such behaviour would be a perfectly valid application of the design rules of the product being used. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
On 10 Nov 2012, at 7:21pm, stahl...@dbs.uni-hannover.de wrote: > 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'. Sorry, but you are too optimistic. I would expect a schema design tool -- the sort where you move rectangles and pointers around -- to warn you when you put a pointer in from tab2 to tab1, that the affinities should match. But in a language where you specifically declare schema using text, I expect the programmer to have to do this him- or herself. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
stahl...@dbs.uni-hannover.de wrote: > 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'. Why would you expect that? You keep saying this, but I don't understand the basis of your expectations - unless it's just the good old wishful thinking. > (I don't know what the SQL Standard has to say about this situation I'm pretty sure the second CREATE TABLE statement is syntactically invalid, per the standard. The column type is mandatory, if I recall correctly. > but I do think that most users would share my expectation.) I'm not sure what you base this belief on, either. I don't seem to see your argument enjoying widespread support on this thread. > However with SQLite there are queries which yield incoherent results: Define "incoherent". As far as I can tell, you use this term to mean "results you personally dislike". The results SQLite produces are in agreement - in other words, in coherence - with the product documentation. > 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*. You seem to hold this truth to be self-evident, but I honestly don't understand why. Could you explain your reasoning to me? I assume there's more to it than just you finding the outcome aesthetically displeasing. > Also I don't really care *how* this is fixed As best I can tell, so far there's no agreement that it's broken, so discussing how to fix it seems a bit premature. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting gwenn : If you want, you can verify automatically that all the FK columns have a type matching the referenced columns by using (and tweaking) an old tool whose name is 'genfkey' (see http://www.sqlite.org/faq.html#q22 but the 'readme' link is broken). Thanks for the hint. Honestly though, I think that the need for an external tool to check this basic kind of schema-integrity is kind of clumsy. After all, if one could simply define FK columns so that they have the type they refer to, there wouldn't even be a problem. regards, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Simon Slavin : 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
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
If you want, you can verify automatically that all the FK columns have a type matching the referenced columns by using (and tweaking) an old tool whose name is 'genfkey' (see http://www.sqlite.org/faq.html#q22 but the 'readme' link is broken). Regards. On Thu, Nov 8, 2012 at 6:29 PM, Simon Slavin wrote: > > 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." > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
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." Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Ryan Johnson : On 08/11/2012 8:04 AM, 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 [...] 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. Agree, but badly-designed software has an annoying habit of showing up in production and then yelling loudly when you fix the bug it depends on. So true... :-( 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. I actually liked your idea of making the FK field inherit the type of the PK field it references. I think that this would be the best solution. 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? With a change of syntax on the other hand, the break would at least be clearly visible: Existing databases would continue to behave as is. But statements in the form of 'CREATE TABLE ( ... [column-name] REFERENCES... )' which used to be accepted, would now be rejected. Screaming users could then be informed that a proper datatype must be chosen for [column-name] -- 'BLOB' if the user actually relies on the foreign key to have type affinity 'none'. Meanwhile, you might want to tell sqlite to enforce those foreign key constraints you so carefully specified: http://www.sqlite.org/foreignkeys.html#fk_enable [1]. Then, the only way to insert '24' into the FK table and have it mismatch the 24 in your PK table is if your PK table has '24' in it as well. But that would clearly be bad database design :P Hehe! You caught me... :-D (I actually tripped over this myself as I tried to feed my example to Oracle!) [1] I always forget that check's off by default... somehow I doubt it would catch problems with previously inserted keys, but you could fix them in one fell swoop with: update $fktable set $fk=cast($fk as int) Thanks! I forget to turn on the check too... I guess I'm somewhat spoiled by "non-lite" DBMSes. :-) Maybe the 'foreign-keys' pragma could also turn on the FK-type inheritance. This seems reasonable and might mitigate the badly-designed-software-yelling somewhat. best regards, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
On 08/11/2012 8:04 AM, stahl...@dbs.uni-hannover.de wrote: Quoting Ryan Johnson : On 07/11/2012 7:58 PM, Simon Davies wrote: On 7 November 2012 20:36, 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. Agree, but badly-designed software has an annoying habit of showing up in production and then yelling loudly when you fix the bug it depends on. 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. I actually liked your idea of making the FK field inherit the type of the PK field it references. Meanwhile, you might want to tell sqlite to enforce those foreign key constraints you so carefully specified: http://www.sqlite.org/foreignkeys.html#fk_enable [1]. Then, the only way to insert '24' into the FK table and have it mismatch the 24 in your PK table is if your PK table has '24' in it as well. But that would clearly be bad database design :P Ryan [1] I always forget that check's off by default... somehow I doubt it would catch problems with previously inserted keys, but you could fix them in one fell swoop with: update $fktable set $fk=cast($fk as int) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Ryan Johnson : On 07/11/2012 7:58 PM, Simon Davies wrote: On 7 November 2012 20:36, 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
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
On 07/11/2012 7:58 PM, Simon Davies wrote: On 7 November 2012 20:36, wrote: Quoting Simon Davies : . . . 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
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
On 7 November 2012 20:36, wrote: > Quoting Simon Davies : > . . . > >> 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! > > Anyway, thanks for your help, Simon! > > kind regards, > > Christian > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
Quoting Simon Davies : CREATE TABLE main ( id INTEGER PRIMARY KEY ); CREATE TABLE tab1 ( id INTEGER REFERENCES main, str VARCHAR(10) ); CREATE TABLE tab2 ( id REFERENCES main, str VARCHAR(10) ); INSERT INTO tab1 VALUES ( 42, 'foo' ); INSERT INTO tab2 VALUES ( 42, 'foo' ); The following two queries return different results: SELECT * FROM tab1 WHERE id = '42'; -- returns 1 row SELECT * FROM tab2 WHERE id = '42'; -- returns no rows 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. :-) Anyway, thanks for your help, Simon! kind regards, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns
On 7 November 2012 16:41, wrote: > Hi! > > I have encountered inconsistent behavior regarding indirectly defined > columns. > > In the following example: > > CREATE TABLE main ( id INTEGER PRIMARY KEY ); > CREATE TABLE tab1 ( id INTEGER REFERENCES main, str VARCHAR(10) ); > CREATE TABLE tab2 ( id REFERENCES main, str VARCHAR(10) ); > > Table 'tab2' defines column 'id' indirectly by referring to the primary key > of table 'main'. > This is accepted by SQLite, and usually tables 'tab1' and 'tab2' behave the > same (as they should). > > But assuming these rows: > > INSERT INTO tab1 VALUES ( 42, 'foo' ); > INSERT INTO tab2 VALUES ( 42, 'foo' ); > > The following two queries return different results: > > SELECT * FROM tab1 WHERE id = '42'; -- returns 1 row > SELECT * FROM tab2 WHERE id = '42'; -- returns no rows > > I understand that the coercion from string '42' to number 42 plays a role > here. > (Maybe the data type of column 'id' in table 'tab2' cannot be inferred.) > But I do expect both queries to give the same result (be it 1 row, no rows > or even an error for trying to use string '42' as a number). > > Best regards, > Christian > 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 Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: Inconsistency wrt. indirectly defined columns
Hi! I have encountered inconsistent behavior regarding indirectly defined columns. In the following example: CREATE TABLE main ( id INTEGER PRIMARY KEY ); CREATE TABLE tab1 ( id INTEGER REFERENCES main, str VARCHAR(10) ); CREATE TABLE tab2 ( id REFERENCES main, str VARCHAR(10) ); Table 'tab2' defines column 'id' indirectly by referring to the primary key of table 'main'. This is accepted by SQLite, and usually tables 'tab1' and 'tab2' behave the same (as they should). But assuming these rows: INSERT INTO tab1 VALUES ( 42, 'foo' ); INSERT INTO tab2 VALUES ( 42, 'foo' ); The following two queries return different results: SELECT * FROM tab1 WHERE id = '42'; -- returns 1 row SELECT * FROM tab2 WHERE id = '42'; -- returns no rows I understand that the coercion from string '42' to number 42 plays a role here. (Maybe the data type of column 'id' in table 'tab2' cannot be inferred.) But I do expect both queries to give the same result (be it 1 row, no rows or even an error for trying to use string '42' as a number). Best regards, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users