Hi!
Because of a bug in my procmail filter, I didn't notice the following
mail until now :(
>>>>> "Thimble" == Thimble Smith <tim@stripped> writes:
Thimble> Forwarded from mysql@stripped....
Thimble> ----- Forwarded message from spt@stripped -----
Thimble> Date: Wed, 01 Mar 2000 16:55:02 -0500
Thimble> From: spt@stripped
Thimble> Subject: Problem with "HAVING" and quoted field aliases
Thimble> To: mysql@stripped
>> Description:
Thimble> When running a query with HAVING with the critia with a quoted field, I
Thimble> recieve incorrect results.
>> How-To-Repeat:
Thimble> select 1 as Junk from sometable having Junk=1;
Thimble> This returns the number of records in sometable with each record containing
> 1.
Thimble> but:
Thimble> select 1 as "Junk" from sometable having "Junk"=1;
Thimble> This returns an empty set.
The above correct if you are not using the --ansi option to MySQL.
If you are not using --ansi, " is used to quote strings, not
identifiers. (MySQL 3.23 uses by default ` to quote identifiers).
In other words:
select 1 as "Junk" from sometable having "Junk"=1;
<==>
select 1 as "Junk" from sometable having 0=1;
(as the value of "junk" in numercial context is 0)
Fix:
Start mysqld with --ansi
or use:
select 1 as "Junk" from sometable having `Junk`=1;
Regards,
Monty
