MySQL Bugs: #1739: Strange results when using @vars with GROUP BY

Bug #1739	Strange results when using @vars with GROUP BY
Submitted:	3 Nov 2003 2:18	Modified:	18 Nov 2003 3:22
Reporter:	Konstantin Baryshnikov	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	FreeBSD (FreeBSD 4.8)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
All the tests were run on the following configurations:
1) mysql 4.0.16 / FreeBSD 4.8
2) mysql 4.0.13-nt / Windows 2000 Pro
on system table mysql.user.

The query:

SELECT @a := 0;
SELECT @a := @a + count( * ) , count( * ) 
FROM user
GROUP BY `user`;

returns

+-----------------+----------+
| @a:=@a+count(*) | count(*) |
+-----------------+----------+
|               2 |        2 |
|               1 |        1 |
|               1 |        1 |
...

The documentation says about using GROUP BY and HAVING with @vars the following:
"For example, the following statement will NOT work as expected: 
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;
The reason is that @aa will not contain the value of the current row, but the value of id for the previous accepted row."

However, as you can see, the result is different from excepted and from documented. Next, if I execute the following query:

SELECT @a := 0;
SELECT @a := @a + 0 + count( * ) , count( * ) 
FROM user
GROUP BY `user`;

the result is as expected:

+-------------------+----------+
| @a:=@a+0+count(*) | count(*) |
+-------------------+----------+
|                 2 |        2 |
|                 3 |        1 |
|                 4 |        1 |
...

Because the documentation - while it does not recommend using @vars with GROUP BY - does not say about such behaviour, it is probably a documentation bug. Hovewer, I'm not sure this is the documentation bug because the second query works perfectly.

How to repeat:
use mysql;
Execute the two queries above.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

there were several issues with this bugreport. Only one of them was a clear bug, it is fixed in the 4.0.17.

Now MySQL behaves consistentlky - both queries return identical results, and neither of them "work perfectly" or at least works as one would expected.

But as the result from assigning and using variable in the save query is not well defined (it depends on execution plan - in this particular case on using temporary table for GROUP BY) I would not call it a bug.

oops, sorry for confusion.
The patch is reverted.

Indeed, as I said above "results from assigning and using variable in the save query are not well defined".