On Sep 12, 2008, at 10:16 AM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> 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".
My point can perhaps better be described by example:
(written on my phone; please replace all • with backticks)
sqlite> create table perverse (•select• text, •••select••• text);
sqlite> insert into perverse values ('a','b');
sqlite> select •select•, •••select••• from perverse;
select|•select•
a|b
In the above, "•select•" is very different from "select".
(As an aside, here's a fun bug: try "select * from perverse" and see
if you can guess what will be shown in sqlite.)
>
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
