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.

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

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

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

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 >

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

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

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

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

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

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

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'

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

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

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

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

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

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