Re: [sqlite] TEMP tables with the same name as of main tables

[Jay Kreibich]

On Jan 23, 2015, at 9:35 AM, Aldo Buratti  wrote:

> I had a bad programming experience with temporary tables and after some
> googling I found this old post
> 
>   [sqlite] How to select from a temp table with same name as a main table.
>   dochsm Tue, 18 Jun 2013 05:39:04 -0700
> 
> that illustrated exactly the same troubles.
> In short, if you have a table named A and a temporary table named TEMP.A,
> then if you want to refer to the A table, it is strongly recommended to
> explicitly call it MAIN.A ( otherwise if you simply refer to A, you will
> pick ... TEMP.A )


For what it is worth, this behavior is documented:

https://www.sqlite.org/lang_naming.html

Database Object Name Resolution

[…] If no database is specified as part of the object reference, then SQLite 
searches the main, temp and all attached databases for an object with a 
matching name. The temp database is searched first, followed by the main 
database, followed all attached databases in the order that they were attached. 
The reference resolves to the first match found. […]


> In my opinion, and for my experience, I find this behavior
> counter-intuitive,

I will have to disagree, as will, I believe, the SQL language designers.

Temp tables are scoped to the connection that created them, while other 
databases are, in a sense, “global.”  Think of your main database like global 
variables in a program, while the temp database has objects (tables, indexes, 
views, etc.) that are “local” to the execution context, similar to variables 
defined within a function.  In almost all languages, object names within a 
tighter scope hide similarly named objects that live a larger scope, just as a 
function variable named “A” will hide a global variable with the name “A” in 
most languages.  The behavior is consistent with the vast majority of 
programming languages and programming paradigms.

Of course, the wisdom of using the same name for two different objects, even if 
they are in a different scope, is a different discussion— but it is a very 
similar discussion to programmers that reference function variables using the 
same name as an existing global variable.

  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] TEMP tables with the same name as of main tables

[Aldo Buratti]

I had a bad programming experience with temporary tables and after some
googling I found this old post

   [sqlite] How to select from a temp table with same name as a main table.
   dochsm Tue, 18 Jun 2013 05:39:04 -0700

that illustrated exactly the same troubles.
In short, if you have a table named A and a temporary table named TEMP.A,
then if you want to refer to the A table, it is strongly recommended to
explicitly call it MAIN.A ( otherwise if you simply refer to A, you will
pick ... TEMP.A )

In my opinion, and for my experience, I find this behavior
counter-intuitive, so I ask if it's possibile to change it in a future
release,
so that "A"  always refer to MAIN.A and TEMP.A refers to TEMP.A.

Since this change may break some old programs logic, I suggest to introduce
a new PRAGMA named "IMPLICIT_MAINDB_PREFIX" (or or better name ..), so that
if a table-name is not prefixed by a db-name, then MAIN is assumed.

regards
  A.Buratti
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users