Gavin Kistner wrote:
> Given that it's possible (if moderately insane) and legal(?) to have a
> column name with a backtick in it, then displaying "`foo`" as the
> column name is semantically different from displaying "foo".
The quotes (of whatever kind) are not part of the identifier, they
surround the identifier. They are not "in it".
> For us
> humans, it's not as bad as displaying "Grape Ape"; our poor computers,
> however, have lesser powers of reasoning.
>
> As you say, using an AS clause avoids the problem:
>
> sqlite> .headers on
> sqlite> create table bar (`select` text);
> sqlite> insert into bar values ('a');
> sqlite> select `select` from bar;
> select
> a
> sqlite> select `select`, count(`select`) from bar;
> `select`|count(`select`)
> a|1
This looks like a bug to me. The back tick quoting extension for
identifiers was added for MYSQL compatibility. The same thing also
happens when using SQL standard double quotes to select a column and an
aggregate function of the column at the same time.
select "select", sum("select") ...
returns the column names
"select" and sum("select")
but using an non aggregate function such as length()
select "select", length("select")
returns different columns names, namely
select and length("select")
Changing the type of function that is used to produce the second column
of the result set should not effect the name that is returned for the
first column in the result set.
I would suggest that the name of the result column or expression should
always be returned without any surrounding quotes. As is done for all
but the case with a aggregate function of the column.
> sqlite> select `select` as `select`, count(`select`) as hits from
> bar;
> select|hits
> a|1
>
This is also a bug if you ask me, but it is also by design. The second
`select` in the above statement should actually be a literal string not
an identifier, but SQLite allows identifier quoting for literal strings.
It falls back to using the identifier as a literal string if only a
literal string can be used in that location.
Again it behaves the same way if the as clause literal name is quoted
using SQL standard double quotes, or the MSSQL compatibility extension
square bracket quotes.
select "select" as "select" ...
select [select] as [select] ...
select `select` as `select` ...
all produce the same column name
select
without any surrounding quote characters.
All three should generate a syntax error message since an identifier is
not allowed as the alias name in an as clause.
> Now I just have to convince the author of the ORM library I'm using to
> put AS statements on every column in every SELECT, and either hope
> that this causes no performance impact on any of the supported RDBMS,
> or convince him to branch code for SQLite. (Assuming that placing AS
> clauses for every column in every SELECT does not noticeably impact
> SQLite.)
>
There is no standard for the display of result column names (at least
that I am aware of), so this sort of code is inherently database engine
specific.
> I also hope, however, that you may see the current behavior as
> undesirably inconsistent, even if it is justifiable by your design
> philosophy.
>
You should perhaps file a bug report to see if this can be corrected.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
