Re: [sqlite] Trouble with column names

[John LeSueur]

D. Richard Hipp wrote:
Please tell me what *you* think the column name should be in
the following cases.  Also, please tell me what PostgreSQL and
MySQL and perhaps Oracle do.  The decision of how to name
result columns is complex and I am unwilling to go about
making random changes here and there in whack-a-mole fashion.
Until there is a specification on what column names should
actually be, I will decline to change anything.
 select field1 from test;
 select "field1" from test;
 select FIELD1 from test;
 select "FIELD1" from test;
 

This returns an error(case sensitive).
 select [field1] FROM test;
 select test.field1 from test;
 select a.field1 from test as a;
 select "a".field1 from test as a;
 select "a"."field1" from test as a;
 select main.test1.field1 from test;
 select [main]."a".[field1] from test as a;
 

In postgres, all of these except the error noted above, would return the 
same:
field1
a value

This problem is related to the naming of columns in views.
Please tell me what the column names for the following views
should be:
 create view v1 as select field1 from test;
 create view v2 as select FIELD1 from test;
 create view v3 as select a.field1 from test as a;
 create view v4 as select test1.field1 from test;
 

should this be:
create view v4 as select test.field1 from test;
Assuming the typo above, all of these would return the same(in postgres):
field1
a value
 create view v5 as select "FIELD1" from test;
 create view v6 as select "A"."FIELD1" from test as a;
 

These throw errors(case sensitive field search).
 create view v7 as select "a".field1 + 5 FROM test as a;
 create view v8 as select "a".field1+5 from test as a;
 

Postgres is strongly typed, so this wasn't happy, but changing the 
operator to concatenation:

create view v7 as select "a".field1 || '5' FROM test as a;
create view v7 as select "a".field1||'5' FROM test as a;
I got the same for both of these.
?column?
a value5

Another place where naming is an issue is on CREATE TABLE AS
statements.  What is the name of the column in each of the
following tables:
 create table t2 as select field1 from test;
 create table t3 as select FIELD1 from test;
 create table t4 as SELECT a.field1 from test as a;
 -- and so forth
 

For postgres, these seem to follow the selects above, in that without 
the "" surrounding the name, they are all converted to lower case, and 
with the "" they become case-sensitive.
field1
(0 rows)

For the above, what is the column name reported out when
I do:
 select * from t2;
 select * from t3; -- and so forth
or
 select * from v1;
 select * from v2; -- and so forth
If someone will come up with a coherent set of column naming
rules - rules which other popular SQL database engines follow -
then I will be happy to implement them in SQLite.  Doing so will
probably break a lot of existing code.  But as the column naming
rules have never been specified or documented before, I'd be
willing to do it since any code that breaks would have been
depending on undefined behavior to begin with.
 

Here is another test for this.
drop table test;
create table test ("Field1" text);
select * from test;
Field1
(0 rows)
select field1 from test;
ERROR:  column "field1" does not exist
select Field1 from test;
ERROR:  column "field1" does not exist
select "Field1" from test;
Field1
(0 rows)
as opposed to:
drop table test;
create table test (Field1 text); -- this could also be field1, and 
postgres would treat it the same way.
select * from test;
field1
(0 rows)

select field1 from test;
(0 rows)
select Field1 from test;
(0 rows)
select "Field1" from test;
ERROR:  column "Field1" does not exist
Does the SQL standard have anything to say about this?
Does anybody know?
 

I don't know if the sql standard has anything to say. I thought that in 
a previous discussion, some one had done research and found nothing.

Hopefully, this helps clarify postgresql's behavior. Let's see if I can 
make a summary.

On creation of a table:
if "" are used, store the column name exactly as specified, otherwise 
store in lowercase(this applies to create table as select statements as 
well).
On select of a field:
if "" are not used, lowercase whatever the user specified, then use in a 
case sensitive way to find the column, other wise, do not lower case, 
search in a case sensitive way to find the column.
On creation of a view:
Whatever the select returns is used as the column names. If no "" were 
used in the select statement, these will all be lowercase fieldnames.

the tablenames are not included in the column name in any of these 
situations.

Does that make sense? I think that some other databases do the same, but 
using uppercase instead of lower.

I know this issue is really annoying, so I hope that this is helpful in 
getting it to go away.

John LeSueur