BUG?: operation unexpectedly applied to both main and temp

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

BUG?: operation unexpectedly applied to both main and temp

Mark Brand
HI,

Something seems to go wrong in this example where an operation
unexpectedly gets applied both main and temp.

The order of table creation seems to be one crucial factor. I ran into
this while trying to get my head around the use of temporary triggers,
which seems to be the other necessary factor.

create temp table t (db, val);
insert into t select 'temp', 'original';

create table t (db, val);
insert into t select 'main', 'original';

create view v as select NULL val WHERE 1;

create temp trigger trg instead of update on v  /* main.v make no
difference */
begin
     update t set val = new.val;
end;

update v set val = 'touched';

select * from temp.t;
select * from main.t;

select sqlite_version();

OUTPUT:

temp|touched
main|touched
3.17.0

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Simon Slavin-3

On 28 Mar 2017, at 11:02pm, Mark Brand <[hidden email]> wrote:

> create temp table t (db, val);
> insert into t select 'temp', 'original';
>
> create table t (db, val);
> insert into t select 'main', 'original';

Here’s your problem:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create temp table t (db, val);
sqlite> insert into t select 'temp', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
sqlite> create table t (db, val);
sqlite> insert into t select 'main', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
CREATE TABLE t (db, val);
sqlite> SELECT * FROM t;
temp|original
main|original
sqlite> SELECT * FROM temp.t;
temp|original
main|original
sqlite> DROP TABLE temp.t;
sqlite> SELECT * FROM t;
sqlite>

While temp.t exists it gets in the way of main.t.  When you refer to "t" you’re talking about temp.t, not main.t.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Mark Brand


On 29/03/17 01:35, Simon Slavin wrote:

> On 28 Mar 2017, at 11:02pm, Mark Brand <[hidden email]> wrote:
>
>> create temp table t (db, val);
>> insert into t select 'temp', 'original';
>>
>> create table t (db, val);
>> insert into t select 'main', 'original';
> Here’s your problem:
>
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> create temp table t (db, val);
> sqlite> insert into t select 'temp', 'original';
> sqlite> .schema
> CREATE TABLE t (db, val);
> sqlite> create table t (db, val);
> sqlite> insert into t select 'main', 'original';
> sqlite> .schema
> CREATE TABLE t (db, val);
> CREATE TABLE t (db, val);
> sqlite> SELECT * FROM t;
> temp|original
> main|original
> sqlite> SELECT * FROM temp.t;
> temp|original
> main|original
> sqlite> DROP TABLE temp.t;
> sqlite> SELECT * FROM t;
> sqlite>
>
> While temp.t exists it gets in the way of main.t.  When you refer to "t" you’re talking about temp.t, not main.t.
>

The point isn't about which table one expects to receive the update,
it's that *both* tables get updated. In fact, now I realize that the
effect can be demonstrated with a simpler demo than my original:

create temp table t (db, val);
insert into t select 'temp', 'original';

create table main.t (db, val);
insert into t select 'main', 'original';

update t set val = 'touched';

select * from temp.t;
select * from main.t;

/*  output
temp|default table
main|default table
*/

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Mark Brand


On 29/03/17 02:12, Mark Brand wrote:

>
>
> On 29/03/17 01:35, Simon Slavin wrote:
>> On 28 Mar 2017, at 11:02pm, Mark Brand <[hidden email]> wrote:
>>
>>> create temp table t (db, val);
>>> insert into t select 'temp', 'original';
>>>
>>> create table t (db, val);
>>> insert into t select 'main', 'original';
>> Here’s your problem:
>>
>> SQLite version 3.16.0 2016-11-04 19:09:39
>> Enter ".help" for usage hints.
>> sqlite> create temp table t (db, val);
>> sqlite> insert into t select 'temp', 'original';
>> sqlite> .schema
>> CREATE TABLE t (db, val);
>> sqlite> create table t (db, val);
>> sqlite> insert into t select 'main', 'original';
>> sqlite> .schema
>> CREATE TABLE t (db, val);
>> CREATE TABLE t (db, val);
>> sqlite> SELECT * FROM t;
>> temp|original
>> main|original
>> sqlite> SELECT * FROM temp.t;
>> temp|original
>> main|original
>> sqlite> DROP TABLE temp.t;
>> sqlite> SELECT * FROM t;
>> sqlite>
>>
>> While temp.t exists it gets in the way of main.t.  When you refer to
>> "t" you’re talking about temp.t, not main.t.
>>
>
> The point isn't about which table one expects to receive the update,
> it's that *both* tables get updated. In fact, now I realize that the
> effect can be demonstrated with a simpler demo than my original:
>
> create temp table t (db, val);
> insert into t select 'temp', 'original';
>
> create table main.t (db, val);
> insert into t select 'main', 'original';
>
> update t set val = 'touched';
>
> select * from temp.t;
> select * from main.t;
>
> /*  output
>

Sorry, the output I meant to paste is:

     temp|touched
     main|touched


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Domingo Alvarez Duarte
In reply to this post by Mark Brand
Hello !

I repeated your simple case and could see that only the temp.t tabale is
populated/updates.

It seems sqlite look first if there is a temp.table before main.table
and without qualification temp.table is used.

Cheers !


On 28/03/17 21:12, Mark Brand wrote:

>
>
> On 29/03/17 01:35, Simon Slavin wrote:
>> On 28 Mar 2017, at 11:02pm, Mark Brand <[hidden email]> wrote:
>>
>>> create temp table t (db, val);
>>> insert into t select 'temp', 'original';
>>>
>>> create table t (db, val);
>>> insert into t select 'main', 'original';
>> Here’s your problem:
>>
>> SQLite version 3.16.0 2016-11-04 19:09:39
>> Enter ".help" for usage hints.
>> sqlite> create temp table t (db, val);
>> sqlite> insert into t select 'temp', 'original';
>> sqlite> .schema
>> CREATE TABLE t (db, val);
>> sqlite> create table t (db, val);
>> sqlite> insert into t select 'main', 'original';
>> sqlite> .schema
>> CREATE TABLE t (db, val);
>> CREATE TABLE t (db, val);
>> sqlite> SELECT * FROM t;
>> temp|original
>> main|original
>> sqlite> SELECT * FROM temp.t;
>> temp|original
>> main|original
>> sqlite> DROP TABLE temp.t;
>> sqlite> SELECT * FROM t;
>> sqlite>
>>
>> While temp.t exists it gets in the way of main.t.  When you refer to
>> "t" you’re talking about temp.t, not main.t.
>>
>
> The point isn't about which table one expects to receive the update,
> it's that *both* tables get updated. In fact, now I realize that the
> effect can be demonstrated with a simpler demo than my original:
>
> create temp table t (db, val);
> insert into t select 'temp', 'original';
>
> create table main.t (db, val);
> insert into t select 'main', 'original';
>
> update t set val = 'touched';
>
> select * from temp.t;
> select * from main.t;
>
> /*  output
> temp|default table
> main|default table
> */
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Simon Slavin-3
In reply to this post by Mark Brand

On 29 Mar 2017, at 1:33am, Mark Brand <[hidden email]> wrote:

>> The point isn't about which table one expects to receive the update, it's that *both* tables get updated.

No.  Just the one table is updated: the temp.t table.  Both rows of data are in that table.  Try repeating your experiment but replace this line

insert into t select 'main', 'original';

with this:

insert into main.t select 'main', 'original';

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Simon Slavin-3
In reply to this post by Domingo Alvarez Duarte

On 29 Mar 2017, at 1:34am, Domingo Alvarez Duarte <[hidden email]> 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.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: operation unexpectedly applied to both main and temp?

Mark Brand


On 29/03/17 02:38, Simon Slavin wrote:
> On 29 Mar 2017, at 1:34am, Domingo Alvarez Duarte <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: operation unexpectedly applied to both main and temp?

Hick Gunter
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:[hidden email]] Im Auftrag von Mark Brand
Gesendet: Mittwoch, 29. März 2017 02:55
An: [hidden email]
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 <[hidden email]> 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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: operation unexpectedly applied to both main and temp?

Hick Gunter
Ignore the first note. Table main.t is persistent, whrereas temp.t is automatically dropped on closing the connection.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Hick Gunter
Gesendet: Mittwoch, 29. März 2017 08:40
An: 'SQLite mailing list' <[hidden email]>
Betreff: Re: [sqlite] operation unexpectedly applied to both main and temp?

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:[hidden email]] Im Auftrag von Mark Brand
Gesendet: Mittwoch, 29. März 2017 02:55
An: [hidden email]
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 <[hidden email]> 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
[hidden email]
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: [hidden email]

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
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Olivier Mascia
In reply to this post by Simon Slavin-3
Le 29 mars 2017 à 02:38, Simon Slavin <[hidden email]> a écrit :

>> 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.

It's as documented though. Without qualifier the most recently attached database in the connection is used, if the table or column name matches.

temp, even though there is no explicit "attach" is that most recently attached compared to main. :)

--
Best regards, Meilleures salutations, Met vriendelijke groeten,  
Olivier Mascia (from mobile device), http://integral.software

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Mark Brand


On 29/03/17 15:29, Olivier Mascia wrote:
> Le 29 mars 2017 à 02:38, Simon Slavin <[hidden email]> a écrit :
>
>>> 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.
> It's as documented though. Without qualifier the most recently attached database in the connection is used, if the table or column name matches.
>
> temp, even though there is no explicit "attach" is that most recently attached compared to main. :)
>

The documentation actually says "*least* recently attached".
https://www.sqlite.org/lang_attach.html, paragraph 4.


Mark
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Simon Slavin-3

On 30 Mar 2017, at 5:53pm, Mark Brand <[hidden email]> wrote:

> The documentation actually says "*least* recently attached". https://www.sqlite.org/lang_attach.html, paragraph 4.

I’m unsure whether you can legitimately say that the "temp" database is attached to "main".  But whatever the truth is, it does look like the documentation needs rewriting to make clear what happens.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Mark Brand


On 30/03/17 19:10, Simon Slavin wrote:
> On 30 Mar 2017, at 5:53pm, Mark Brand <[hidden email]> wrote:
>
>> The documentation actually says "*least* recently attached". https://www.sqlite.org/lang_attach.html, paragraph 4.
> I’m unsure whether you can legitimately say that the "temp" database is attached to "main".  But whatever the truth is, it does look like the documentation needs rewriting to make clear what happens.
>

Given the fact that an unprefixed name resolves in temp before main,
temp would have to be less recently attached than main. You would have
to say that main is attached to temp. Of course, temp itself is attached
to a elephant standing on a stack of turtles.

Mark
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

David Raymond
Close. It rests on the backs of 4 elephants, who in turn stand on the back of the Great A'Tuin



"Of course, temp itself is attached to a elephant standing on a stack of turtles.

Mark"
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: BUG?: operation unexpectedly applied to both main and temp

Mark Brand


On 30/03/17 21:06, David Raymond wrote:
> Close. It rests on the backs of 4 elephants, who in turn stand on the back of the Great A'Tuin

I don't know but I've been told it's turtles all the way down.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...