Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

[stahlhut]

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

[Igor Tandetnik]

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

[stahlhut]

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

[stahlhut]

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

[Keith Medcalf]

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

[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.

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

[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 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

[stahlhut]

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

[stahlhut]

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

[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).
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

[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."

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

[stahlhut]

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

[stahlhut]

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

[Ryan Johnson]

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

[Simon Davies]

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

[stahlhut]

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

[Simon Davies]

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