Unexpected result from SELECT * FROM (subquery);

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Unexpected result from SELECT * FROM (subquery);

Shane Dev
Hello,

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

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

Richard Hipp-3
On 11/5/17, Shane Dev <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

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

Peter da Silva
On 2017-11-05, at 05:28, Klaus Maas <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

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

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

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

Hick Gunter
In reply to this post by Shane Dev
Because the query planner needs to know the name(s) of the table(s) required for the query at "prepare time" and your query does not provide the name until "run time".

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Shane Dev
Gesendet: Sonntag, 05. November 2017 08:04
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Unexpected result from SELECT * FROM (subquery);

Hello,

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?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users