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 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
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
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] Subject: SQLite driver for Java
Well its been a while since I did a search on it, but a few years ago the best current one I could come up with is the xerial.org SQLiteJDBC. My project, MyJSQLView, uses this to provide a GUI to a SQLite database File. Dana M. Proctor MyJSQLView Project Manager > Howdy! > > What driver are people using to access SQLite databases from Java applications? > > Will > -- > Will Duquette -- william.h.duque...@jpl.nasa.gov > Athena Development Lead -- Jet Propulsion Laboratory > "It's amazing what you can do with the right tools." ___ 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
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
[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) ); 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] couldn't use is null function
YAN HONG YE wrote: > SELECT * FROM ADL WHERE Project_grading is null; > can't select anything. So there's no row in ADL where Project_grading is in fact NULL. What makes you believe differently? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: about date question
On 7 November 2012 09:42, YAN HONG YE wrote: > the table had a column: SupplierDate Date > I wanna add 84 days : > SELECT SupplierDate+84 as date1 FROM ADL; > BUT result have noting > when use: > select date(supplierdate+'2 day') from t93c_adl limit 3; > the result is: > -4713-12-08 > -4713-11-29 > -4713-12-01 > select date( supplierDate, '+2 days' ) http://www.sqlite.org/lang_datefunc.html Regards. Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell import field separators
Simon, > > On 6 Nov 2012, at 11:40am, Colin Hardwick > wrote: > >> Line 1 of the import file is fine, but line 2 is rejected as only having 7 >> fields instead of 8. I've experimented with ".separator" commands to no >> avail (e.g. ".separator='\t'). It appears the the shell tool is thrown by >> the (clearly) mismatched single and double quotes, but since I want it to >> use tabs as the separator should this be the case? > > The shell tool is trying to do the right thing, given its specifications. > You have some alternatives. > > One is to write your own simple importer program which interprets the saved > files how you want. > > Another is to use a shell command to pre-process the TSV file to replace your > quote characters with some unused character, then import that file, then use > SQL UPDATE commands to put the quote characters back again. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > Thanks for that. I'm getting old and slow, it's taken me a long time to realise that the sources for the shell are in the standard distribution! Peering at that at least clears up for me exactly what the import is doing now. We have this process running successfully under Windows, which confused me a bit. It seems that shell version 3.7.3 (which is what's running on the Windy system) actually works for us and imports the names as expected, but I just checked with the latest version and it fails in the same way as it does on the Mac. I'll do some pre/post-processing as you advise. Best regards, Colin Hardwick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW: about date question
the table had a column: SupplierDate Date I wanna add 84 days : SELECT SupplierDate+84 as date1 FROM ADL; BUT result have noting when use: select date(supplierdate+'2 day') from t93c_adl limit 3; the result is: -4713-12-08 -4713-11-29 -4713-12-01 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] about date question
the table had a column: SupplierDate Date I wanna add 84 days : SELECT SupplierDate+84 as date1 FROM ADL; BUT result have noting ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] couldn't use is null function
On 7 November 2012 08:27, YAN HONG YE wrote: > SELECT * FROM ADL WHERE Project_grading is null; > can't select anything. sqlite> create table ADL( id integer primary key, data text, Project_grading integer ); sqlite> insert into ADL( data, Project_grading ) values( '1', 1 ); sqlite> insert into ADL( data, Project_grading ) values( '2', 2 ); sqlite> insert into ADL( data ) values( '3' ); sqlite> insert into ADL( data, Project_grading ) values( '4', 4 ); sqlite> insert into ADL( data ) values( '5' ); sqlite> insert into ADL( data, Project_grading ) values( '6', 6 ); sqlite> sqlite> select * from ADL; 1|1|1 2|2|2 3|3| 4|4|4 5|5| 6|6|6 sqlite> sqlite> select * from ADL where Project_grading is null; 3|3| 5|5| Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] couldn't use is null function
SELECT * FROM ADL WHERE Project_grading is null; can't select anything. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
