Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 30 Mar 2017, at 5:53pm, Mark Brand  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Olivier Mascia]

Le 29 mars 2017 à 02:38, Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 29 Mar 2017, at 1:34am, Domingo Alvarez Duarte  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 29 Mar 2017, at 1:33am, Mark Brand  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Domingo Alvarez Duarte]

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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 28 Mar 2017, at 11:02pm, Mark Brand  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users