Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Klaus Maas]

The table contains a single row with a single column that contains a string.

Like this explanation. Much better than my attempt.

What the outer select sees in its from clause is an ["unnamed-table"] 
that [contains one row containing 'tab1'].


Thank you for pushing this point.

The point I was trying to make and obviously failed was that the string 
returned by the select statement nested in the from clause is just a 
string which happens to be the same text as the name of a table.

Looking the same does not make it the same.

Klaus
email signature Klaus Maas

On 2017-11-05 14:20, Peter Da Silva wrote:

The table contains a single row with a single column that contains a string.

That doesn't make it a string. It's still a table. When you say "select ... from table" it doesn't matter 
where the table came from, it's still an operation on a table. You are not performing "select ... from 
'tab1';", you're performing "select ... from unnamed-table" where "unnamed-table" contains one 
row containing 'tab1'.

The result of *that* select is yet another unnamed table that the sqlite3 shell 
displays for you.

Even if you perform

select * from (select * from (select * from ( ... ) )

The result is still a table. Select is not an indirection operator like 
accessing an element of an array or a structure.

On 2017-11-05, at 05:39, Klaus Maas  wrote:

Yes, correct.

But the contents of the returned table are not objects, but merely values.

In this case the returned table contains a single string value  which 
happens to be the name of a table, but it is not the table.

Or do I get this wrong?

___
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] Unexpected result from SELECT * FROM (subquery);

[Peter Da Silva]

The table contains a single row with a single column that contains a string.

That doesn't make it a string. It's still a table. When you say "select ... 
from table" it doesn't matter where the table came from, it's still an 
operation on a table. You are not performing "select ... from 'tab1';", you're 
performing "select ... from unnamed-table" where "unnamed-table" contains one 
row containing 'tab1'.

The result of *that* select is yet another unnamed table that the sqlite3 shell 
displays for you.

Even if you perform

select * from (select * from (select * from ( ... ) )

The result is still a table. Select is not an indirection operator like 
accessing an element of an array or a structure.

On 2017-11-05, at 05:39, Klaus Maas  wrote:
> Yes, correct.
> 
> But the contents of the returned table are not objects, but merely values.
> 
> In this case the returned table contains a single string value  which 
> happens to be the name of a table, but it is not the table.
> 
> Or do I get this wrong?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Klaus Maas]

Yes, correct.

But the contents of the returned table are not objects, but merely values.

In this case the returned table contains a single string value  
which happens to be the name of a table, but it is not the table.


Or do I get this wrong?

email signature Klaus Maas Klaus


On 2017-11-05 12:31, Peter Da Silva wrote:

On 2017-11-05, at 05:28, Klaus Maas  wrote:

I thought it was because what SQL returns is a value (in this case a string) 
and not an object?

The string value might be the same as the name of an object, but is not the 
object.

Select returns a table, not a name or a string.

The outer select operates on this unnamed table, not any particular cell in it.

___
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] Unexpected result from SELECT * FROM (subquery);

[Peter Da Silva]

On 2017-11-05, at 05:28, Klaus Maas  wrote:
> I thought it was because what SQL returns is a value (in this case a string) 
> and not an object?
> 
> The string value might be the same as the name of an object, but is not the 
> object.

Select returns a table, not a name or a string.

The outer select operates on this unnamed table, not any particular cell in it.

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

Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Klaus Maas]

I thought it was because what SQL returns is a value (in this case a 
string) and not an object?


The string value might be the same as the name of an object, but is not 
the object.


email signature Klaus Maas Klaus



On 2017-11-05 11:51, Richard Hipp wrote:

On 11/5/17, Shane Dev  wrote:

In sqlite3, I executed the following statements -

sqlite> select name from tabs where rowid=1;
tab1
sqlite> select * from tab1;
first rec

sqlite> select * from (select name from tabs where rowid=1);
tab1

I expected the last statement to evaluate the subquery first to be 'tab1'
and then execute SELECT * FROM tab1 to yield 'first rec'. Why didn't this
happen?

Because that is not the way SQL works.  The statement

SELECT * FROM (SELECT name FROM tabs WHERE rowid=1);

is logically equivalent to:

CREATE TEMP TABLE "some-random-name" AS
  SELECT name FROM tabs WHERE rowid=1;
SELECT * FROM "some-random-name";
DROP TABLE "some-random-name";

SQL works on a compile-then-execute model.  Each SQL statement is
first analyzed and compiled into bytecode or into machine code or some
other executable format.  Then the resulting compiled code is run to
generate a result.  The names of tables and columns are fixed at
compile-time and cannot be modified at runtime, since to do so would
require on-the-fly changes to the compiled code.



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

Re: [sqlite] Unexpected result from SELECT * FROM (subquery);

[Richard Hipp]

On 11/5/17, Shane Dev  wrote:
>
> In sqlite3, I executed the following statements -
>
> sqlite> select name from tabs where rowid=1;
> tab1
> sqlite> select * from tab1;
> first rec
>
> sqlite> select * from (select name from tabs where rowid=1);
> tab1
>
> I expected the last statement to evaluate the subquery first to be 'tab1'
> and then execute SELECT * FROM tab1 to yield 'first rec'. Why didn't this
> happen?

Because that is not the way SQL works.  The statement

   SELECT * FROM (SELECT name FROM tabs WHERE rowid=1);

is logically equivalent to:

   CREATE TEMP TABLE "some-random-name" AS
 SELECT name FROM tabs WHERE rowid=1;
   SELECT * FROM "some-random-name";
   DROP TABLE "some-random-name";

SQL works on a compile-then-execute model.  Each SQL statement is
first analyzed and compiled into bytecode or into machine code or some
other executable format.  Then the resulting compiled code is run to
generate a result.  The names of tables and columns are fixed at
compile-time and cannot be modified at runtime, since to do so would
require on-the-fly changes to the compiled code.

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