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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
