stahl...@dbs.uni-hannover.de wrote:
> Quoting Igor Tandetnik <itandet...@mvps.org>:
>> 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 <match predicate>
Function
Specify a test for matching rows.
Format
<match predicate> ::=
<row value constructor> MATCH [ UNIQUE ] [ PARTIAL | FULL ]
<table subquery>
Syntax Rules
1) The <row value constructor> shall be of the same degree as the
<table subquery>.
2) The data types of the values of the <row value constructor>
shall be respectively comparable to those of the corresponding
columns of the <table subquery>.
3) The collating sequence for each pair of respective values in the
<match predicate> is determined in the same manner as described
in Subclause 8.2, "<comparison predicate>".
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
