Re: [sqlite] Behaviour of tables with same name
On Fri, May 07, 2010 at 07:06:52AM -0700, a1rex scratched on the wall: > Since we are at this topic let me ask the question: Are the table > names case insensitive? $ ./sqlite3 SQLite version 3.6.23.1 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table T ( i ); sqlite> insert into t values ( 1 ); sqlite> select * from t; 1 sqlite> select * from T; 1 sqlite> select * from "t"; 1 sqlite> select * from "T"; 1 sqlite> -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Behaviour of tables with same name
> Since we are at this topic let me ask the question: Are the table names > case insensitive? Yes, I believe they are: as are all identifiers. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Behaviour of tables with same name
Since we are at this topic let me ask the question: Are the table names case insensitive? From: Andy Gibbs <andyg1...@hotmail.co.uk> To: sqlite-users@sqlite.org Sent: Fri, May 7, 2010 8:00:44 AM Subject: Re: [sqlite] Behaviour of tables with same name > I think the bigger issue is that you probably shouldn't rely on > automatic resolution of names. ... If you're using multiple > databases-- even just temp and main-- the best solution is to > just qualify as much as you can. This is sound advice. Thank you, also, for the clarification of the search order. Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Behaviour of tables with same name
> I think the bigger issue is that you probably shouldn't rely on > automatic resolution of names. ... If you're using multiple > databases-- even just temp and main-- the best solution is to > just qualify as much as you can. This is sound advice. Thank you, also, for the clarification of the search order. Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Behaviour of tables with same name
On Fri, May 07, 2010 at 09:43:13AM +0200, Andy Gibbs scratched on the wall: > Hi, > > I have tracked down a bug in some other software where two tables > have the same name, where it seems that the one table was masking the other. > What happens is that the table 't' in the main connection takes > precedence over table 't' in the attached database, but *not* over > the temporary table. > > My question is, is this correct behaviour on the part of sqlite? The search order for unqualified objects is always: temp, main, (attached)... > Alternatively, would it be better if it always took the table from the > main connection over any others? I don't think so. Temp structures have to be explicit created by the current database connection, so I think there is some assumption that they are created with an awareness of the current environment (unlike, for example, some random attached database). There are a lot of cases when the search order makes sense. I think the bigger issue is that you probably shouldn't rely on automatic resolution of names. I've found a few bugs related to temp trigger creation where tables were incorrectly referenced or implied because of the search order. If you're using multiple databases-- even just temp and main-- the best solution is to just qualify as much as you can. There are still places where this is not possible, and the search order ends up masking stuff, so unique names are even better. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
