List:Bugs« Previous MessageNext Message »
From:Michael Widenius Date:April 22 2000 10:59pm
Subject:Problem with "HAVING" and quoted field aliases
View as plain text  
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

Thread
Problem with "HAVING" and quoted field aliasesThimble Smith2 Mar
  • Problem with "HAVING" and quoted field aliasesMichael Widenius23 Apr