On Jan 23, 2015, at 9:35 AM, Aldo Buratti <aldo.w.bura...@gmail.com> 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
