Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

Justin Ng
I just encountered something weird with "temp" and "sqlite_master".
I was wondering if it was another bug, or intentional.

-----

This throws a "column not found" error,

SELECT
    sqlite_master.sql
FROM
    temp.sqlite_master;

-----

This throws a "column not found" error,

SELECT
    temp.sqlite_master.sql
FROM
    temp.sqlite_master;

-----

This works fine,

SELECT
    x.sql
FROM
    temp.sqlite_master AS x;

-----

Referring to temp.sqlite_master columns directly in the WHERE clause also causes the error.
I assume it's the same for every other part of a query.
_______________________________________________
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: Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

Petite Abeille-2


> On Mar 14, 2020, at 03:14, Justin Ng <[hidden email]> wrote:
>
> This works fine,
>
> SELECT
>    x.sql
> FROM
>    temp.sqlite_master AS x;
>

As is this:

SELECT
   sql
FROM
   temp.sqlite_master;

Hmm.
_______________________________________________
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: Cannot select specific columns from temp.sqlite_master unless temp.sqlite_master is aliased

Keith Medcalf
In reply to this post by Justin Ng

On Friday, 13 March, 2020 20:14, Justin Ng <[hidden email]> wrote:

>I just encountered something weird with "temp" and "sqlite_master".
>I was wondering if it was another bug, or intentional.

The sqlite_master table in "temp" is called "sqlite_temp_master"  (temp.sqlite_temp_master) even though it responds to the spelling temp.sqlite_master as well.  In either case the alias name is "sqlite_temp_master", not "sqlite_master".  You can specify your own alias name which will be respected.

Three level qualifiers are not recognized.  That is, you cannot use <schema>.<table>.<column> to identify a column -- it won't work.

I cannot answer why this is as it is, merely that this is indeed how it is.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users