I find the .explain/explain <query> functionality very helpful in clearing up what happens and why.
Note the error when creating main.t. Note the database number 1 (for temp) vs. 0 (for main) in the OpenWrite and TableLock instructions. asql> create temp table t (db, val); asql> create table main.t (db, val); Error: table t already exists asql> .explain asql> explain insert into t select 'main', 'original'; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Goto 0 25 0 00 NULL ... 12 OpenWrite 0 2 1 2 00 t ... 27 TableLock 1 2 1 t 00 NULL 28 Goto 0 2 0 00 NULL asql> drop table temp.t; asql> explain insert into t select 'main', 'original'; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Goto 0 25 0 00 NULL ... 12 OpenWrite 0 42 0 2 00 t ... 27 TableLock 0 42 1 t 00 NULL 28 Goto 0 2 0 00 NULL asql> quit -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mark Brand Gesendet: Mittwoch, 29. März 2017 02:55 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] operation unexpectedly applied to both main and temp? On 29/03/17 02:38, Simon Slavin wrote: > On 29 Mar 2017, at 1:34am, Domingo Alvarez Duarte <mingo...@gmail.com> wrote: >> It seems sqlite look first if there is a temp.table before main.table and >> without qualification temp.table is used. > You got it. It’s not obvious that this is what SQLite would do. But now you > know it you understand what is happening. Thanks for helping to clear this up. I should have tested the content of the table before doing the update and especially before posting. Oh well. I didn't expect temp to win over main like that. Especially because I recently read the documentation page for "ATTACH DATABASE". > Tables in an attached database can be referred to using the syntax > /schema-name.table-name/. If the name of the table is unique across > all attached databases and the main and temp databases, then the > /schema-name/ prefix is not required. If two or more tables in > different databases have the same name and the /schema-name/ prefix is > not used on a table reference, then the table chosen is the one in the > database that was *least recently attached*. So I guess for our purposes temp is "less recently" attached than main, which wasn't an obvious fact to me. Mark _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
