On Sep 11, 2008, at 11:44 AM, D. Richard Hipp wrote:
> On Sep 11, 2008, at 1:11 PM, Gavin Kistner wrote:
>
>> So I ask again: can the inclusion of backticks in the column name
>> returned as the result for certain select statements be considered a
>> bug?
>
> In the absence of an AS clause, SQLite makes no promises about column
> names. If you want a specific column name, use an AS clause on that
> column to specify the name. Otherwise, you get what you get and what
> you get might change from one point release to the next. (That said,
> people tend to scream furiously when the column naming rules changes,
> so we do try to avoid changing them without a very good reason.)
Respectfully, it's your (super awesome) software, so of course you're
free to design it as you will.
Obviously it would be sub-optimal if "SELECT foo FROM bar" displayed
the name of a random Hanna-Barbera cartoon character for the column
name each time it was issued. Along that line of reasoning (but not
nearly so far down the ridiculous road) I find it unhelpful that
"SELECT `foo`, ... FROM bar" changes the name of the first column
depending on the contents elided.
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". 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
sqlite> select `select` as `select`, count(`select`) as hits from
bar;
select|hits
a|1
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.)
I also hope, however, that you may see the current behavior as
undesirably inconsistent, even if it is justifiable by your design
philosophy.
Thanks (truly) for the joy that is SQLite!
/wave from a fellow Duke grad
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
