SQLite Forum

Strange behaviour with tables have the same name and different schemas
Login

Strange behaviour with tables have the same name and different schemas

(1) By little-brother on 2020-09-21 21:03:49 [link] [source]

Steps to reproduce

create table t as select 1 a;
create table temp.t as select *, 2 b from t;
select * from pragma_table_info("t"); --> temp.t info
select * from main.pragma_table_info("t"); --> temp.t info
pragma table_info("t"); --> temp.t info
pragma main.table_info("t"); --> Finally, t info

P.S. Win7, sqlite 3.33.0 x32bit.

(2) By Keith Medcalf (kmedcalf) on 2020-09-21 23:35:58 in reply to 1 [link] [source]

There is no inconsistency, only misunderstanding. (You should also learn to use the proper quotes -- double-quotes are for identifiers and single-quotes are for strings. The virtual table implementation uses strings, not identifiers.)

When you use the virtual table implementation, the two hidden parameters that you can specify are "schema" and "arg". "arg" is the argument to the pragma as a string, and "schema" is the schema to which the pragma form would apply. You cannot specify the "schema" as an argument.

The eponymous virtual table form <schema>.pragma_table_info refers to the eponymous virtual table "pragma_table_info" located in the schema <schema>, not the schema in which the table specified by the argument is to be found.

Since an eponymous virtual table is a creature of the "connection" and not of any particular schema, it exists in all schemas and refers to exactly the same thing. You are making an error in specification of the <schema> in which you want to find the table <arg>.

sqlite> pragma table_xinfo(pragma_table_info);
┌─────┬────────────┬──────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────────┐
│ cid │    name    │ type │   aff   │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ hidden │
├─────┼────────────┼──────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────────┤
│ -1  │            │      │ INTEGER │      │ 0       │            │ 1  │ 1     │ 0       │ 1      │
│ 0   │ cid        │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 1   │ name       │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 2   │ type       │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 3   │ aff        │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 4   │ coll       │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 5   │ notnull    │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 6   │ dflt_value │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 7   │ pk         │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 8   │ rowid      │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 9   │ autoinc    │      │ BLOB    │      │ 0       │            │ 0  │ 0     │ 0       │ 0      │
│ 10  │ arg        │      │ NUMERIC │      │ 0       │            │ 0  │ 0     │ 0       │ 1      │
│ 11  │ schema     │      │ NUMERIC │      │ 0       │            │ 0  │ 0     │ 0       │ 1      │
└─────┴────────────┴──────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────────┘

** I have more columns than you because I have modified the pragma to produce more columns of output from the internal data structures **

The equivalent of the statement:

pragma <schema>.table_info(<arg>);

is

select * from pragma_table_info where arg=<arg> and schema=<schema>;

If you specify tablename <arg> without specifying a schema in which to look, then the normal "search order" for tables applies and the "temp" schema is searched first, followed by the "main" schema and then any other attached schema in the order they were attached.

(3) By little-brother on 2020-09-22 00:39:54 in reply to 2 [link] [source]

Thanks a lot for the detailed answer!

I should read more docs :)

(4.1) By Keith Medcalf (kmedcalf) on 2020-09-22 16:42:45 edited from 4.0 in reply to 3 [link] [source]

The hidden columns arg and schema to the eponymous virtual table implementations of the pragma's is not documented anywhere that I know of, so searching the documentation may not have been helpful.

Perhaps the arg and schema hidden columns should be documented on the applicable documentation page:

https://sqlite.org/pragma.html

(5.1) By Keith Medcalf (kmedcalf) on 2020-09-22 16:47:32 edited from 5.0 in reply to 3 [source]

Note also that you can specify the schema as an argument as in:

select * from pragma_table_info(arg, schema);

where arg and schema are the stringified table and schema names.

(6) By little-brother on 2020-09-22 18:01:03 in reply to 5.1 [link] [source]

Thanks again.

As I understood the args of a virtual table can be provided by "where"-condition. Interesting. 
I'm going to write a virtual table extension, so I'll dig this topic deeply later.