CARIOTOGLOU MIKE wrote:
> I can verify this :
>
> Select "columnName" from someTable gives columns with quoted names !
> ("ColumnName") instead of ColumnName
>
> This *feels* like a bug
>
I believe this is a bug as well.
The following script demonstrates the problems using the sqlite3 shell. I
use a column name with a space that requires quoting when it is created. SQL
uses single quotes to demark literal strings, and double quotes to demark
column names.
mode column
headers on
create table t ('I D' varchar);
insert into t values ('abcd');
select * from t;
I D
----------
abcd
select I D from t;
SQL error: no such column: I
select 'I D' from t;
'I D'
----------
I D
select 'I D' as 'I D' from t;
I D
----------
I D
select "I D" from t;
"I D"
----------
abcd
select "I D" as 'I D' from t;
I D
----------
abcd
select "I D" as "I D" from t;
I D
----------
abcd
select "I D" as I D from t;
SQL error: near "D": syntax error
The "select *" case returns an un-quoted column name. This is correct.
The un-quoted column name case returns an error as it should.
Adding single quotes to the name make it a literal character value, which
returns the correct value, but the column name that is generated by SQLite
includes the single quote characters. I believe this is incorrect. It should
produce the same column name as the next case where a literal string is used
as the column name. If there are wrappers that need to quote these names
(for a CSV file output for example) that should be done by the wrapper, not
by SQLite.
Similarly, the double quoted column name case returns the correct result
with the wrong column name. SQLite has included the quotes in the column
name. Again the correct result can be achieved by specifying the column name
as a literal string.
Interestingly, SQLite also allows the literal name to be specified using
double quote characters. This lead me to check the SQL standard. It says
that the only legal way to specify a literal column name in an AS clause is
using double quotes. So SQLite's previous acceptance of the single quoted
literals can also be viewed as a standard non-conformance bug.
Finally, SQLite generates an error, as it should, if you try using an
un-quoted column name literal with a space.
Dennis Cote
