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