MySQL Bugs: #59574: Assignment operator with group functions

Bug #59574	Assignment operator with group functions
Submitted:	18 Jan 2011 7:00	Modified:	27 Jan 2011 17:57
Reporter:	Alexander G	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.51	OS:	FreeBSD (Server version: 5.1.51 FreeBSD port: mysql-server-5.1.51_1)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	Assignment operator, group functions

Description:
The manual example on http://dev.mysql.com/doc/refman/5.1/en/extensions-to-ansi.html
mysql> SELECT @a:=SUM(total),@b:=COUNT(*),@a/@b AS avg FROM test_table;
not working properly.
Assignment operator assigns a value to a variable only after the query is finished. This does not allow to use variables in query. Bug detected only with group functions (sum, count, min, max...), in other cases assignment operator works fine.

How to repeat:
CREATE TABLE `Tests` (
  `id` int(11) NOT NULL,
  `id_Task` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO `Tests` (`id`, `id_Task`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 2);

mysql> select @s:=sum(id),@c:=count(*),@a:=@s/@c,@s,@c from Tests;
+-------------+--------------+-----------+------+------+
| @s:=sum(id) | @c:=count(*) | @a:=@s/@c | @s   | @c   |
+-------------+--------------+-----------+------+------+
|          15 |            5 |      NULL | NULL | NULL |
+-------------+--------------+-----------+------+------+

mysql> select @s,@c,@a;
+------+------+------+
| @s   | @c   | @a   |
+------+------+------+
|   15 |    5 | NULL |
+------+------+------+

Query without group functions works fine:

mysql> select @a:=id,@b:=id_Task,@c:=@a+@b from Tests;
+--------+-------------+-----------+
| @a:=id | @b:=id_Task | @c:=@a+@b |
+--------+-------------+-----------+
|      1 |           1 |         2 |
|      2 |           1 |         3 |
|      3 |           1 |         4 |
|      4 |           2 |         6 |
|      5 |           2 |         7 |
+--------+-------------+-----------+

Suggested fix:

Indeed, it does not work:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.1.54-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test_table(total int);
Query OK, 0 rows affected (0.77 sec)

mysql> insert into test_table values(1),(2),(4);
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select @a:=sum(total),@b:=count(*), @a/@b as avg from test_table;
+----------------+--------------+------+
| @a:=sum(total) | @b:=count(*) | avg  |
+----------------+--------------+------+
|              7 |            3 | NULL |
+----------------+--------------+------+
1 row in set (0.08 sec)

mysql> select @a:=sum(total),@b:=count(*), @a/@b as avg from test_table;
+----------------+--------------+-------------+
| @a:=sum(total) | @b:=count(*) | avg         |
+----------------+--------------+-------------+
|              7 |            3 | 2.333333333 |
+----------------+--------------+-------------+
1 row in set (0.00 sec)

I think we should better remove that example from the manual. http://dev.mysql.com/doc/refman/5.1/en/user-variables.html explains what can happen, to some extent:

"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."

The Manual explains this clearly, but I agree the example contradicts the recommendation we give, and should thus be removed (or rewritten).

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Removed the example, replaced it with a cross reference to the user-variables section.