Re: [sqlite] Pragmas

[Dennis Cote]

Drew, Stephen wrote:
I would be grateful if somebody who has
experience using these column name pragmas could take a look and see if
either:
(a) This documentation is incorrect:
http://www.sqlite.org/cvstrac/wiki?p=ColumnNames
(b) I am not using SQLite correctly.
 

Steve,
I have tried these pragmas in the past, and I also found that they 
didn't work as advertised.

The following trace from a fresh build of SQLite using the latest CVS 
source shows that the full_column_names pragma doesn't produce full 
column names as documented.

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> .echo on
sqlite> .read "c:\\temp\\col_prag.sql"
.read "c:\\temp\\col_prag.sql"
drop table test1;
SQL error: no such table: test1
drop table test2;
SQL error: no such table: test2
create table test1 (id integer primary key, data1 varchar);
create table test2 (id integer, data2 varchar);
insert into test1 values(1, 'a');
insert into test1 values(2, 'b');
insert into test2 values(1, 'A');
insert into test2 values(2, 'B');
pragma short_column_names;
1
pragma full_column_names;
0
pragma short_column_names=off;
pragma full_column_names=on;
pragma short_column_names;
0
pragma full_column_names;
1
.header on
select * from test1, test2 where test1.id=test2.id;
id|data1|id|data2
1|a|1|A
2|b|2|B
select * from test1 as T1, test2 as T2 where T1.id=T2.id;
id|data1|id|data2
1|a|1|A
2|b|2|B
select test1.*, test2.* from test1, test2 where test1.id=test2.id;
id|data1|id|data2
1|a|1|A
2|b|2|B
select T1.*, T2.* from test1 as T1, test2 as T2 where T1.id=T2.id;
id|data1|id|data2
1|a|1|A
2|b|2|B
select T1.id, data2 from test1 as T1 join test2 as T2 using(id);
test1.id|test2.data2
1|A
2|B
select T1.id, data2 from test1 as T1 join test2 as T2 on T1.id=T2.id;
test1.id|test2.data2
1|A
2|B
Clearly these results do not agree with the documentation for this case 
(copied below).

   *Case 5: short_column_names=OFF and full_column_names=ON*
   If cases 1 and 2 do not apply and short_column_names=OFF and
   full_column_names=ON then the result set column name is constructed
   as "TABLE.COLUMN" where TABLE is the name of the table from which
   the data is taken and COLUMN is the name of the column within TABLE
   from which the data was taken. If the table is aliased by the use of
   an AS clause in the FROM expression then the alias is used instead
   of the original table name.
First, the implicit joins in the first 4 selects are ignored. Secondly, 
the explicit joins in the last two selects are returning the original 
table name rather than the table's alias name.

You aren't doing anything wrong, SQLite is.
Dennis Cote