Ack, trying again with subscribed address.
--
(-, /\ \/ / /\/
On Sep 12, 2008, at 2:46 PM, Gavin Kistner <[EMAIL PROTECTED]> wrote:
> 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
