AnyhowStep/tsql-sqlite3-browser

Report temp.sqlite_master bug to SQLite mailing list

Opened this issue · 4 comments

AnyhowStep commented

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;

Some kind of shadowing is going on and it's causing SQLite to freak out.

AnyhowStep commented

Also, someone replied with,

This works fine,

SELECT
x.sql
FROM
temp.sqlite_master AS x;

As is this:

SELECT
   sql
FROM
   temp.sqlite_master;

Hmm.

AnyhowStep commented

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 .

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

AnyhowStep commented

I just tested it and it works.

My life has been a lie. I never knew about sqlite_temp_master until today.

From the FAQ, https://www.sqlite.org/faq.html

Temporary tables do not appear in the SQLITE_MASTER table. Temporary tables and their indices and triggers occur in another special table named SQLITE_TEMP_MASTER. SQLITE_TEMP_MASTER works just like SQLITE_MASTER except that it is only visible to the application that created the temporary tables. To get a list of all tables, both permanent and temporary, one can use a command similar to the following:

SELECT name FROM 
   (SELECT * FROM sqlite_master UNION ALL
    SELECT * FROM sqlite_temp_master)
WHERE type='table'
ORDER BY name