MySQL Bugs: #52507: HAVING cannot reference a field with variable

Bug #52507	HAVING cannot reference a field with variable
Submitted:	31 Mar 2010 14:53	Modified:	1 May 2010 7:00
Reporter:	weipan lu	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	MySQL 5.1.45	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	having, variable

Description:
  The following sql return nothing(no record). 
	SELECT @v := 1, (@v) AS c
		FROM `user`
      		HAVING c > 0

  But the following record return one record.
	SELECT @v := 1, (@v) AS c
		FROM `user`

  Run the second sql, and then run the first sql. The first sql return one record. Both sql should return one record, because 'user' table has one record.

How to repeat:
  Run above sql in the default "mysql" database.

The problem you described is easily repeatable, but our manual, http://dev.mysql.com/doc/refman/5.1/en/user-variables.html, explains:

"As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a  first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation."

You assign value in select list and then read it (via alias) in HAVING clause. According to the text above you can NOT expect any specific result.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".