Bug #25214 Assigning and using user variables in a SELECT command with agg. functions

• Submitted: 20 Dec 2006 12:15
• Modified: 20 Dec 2006 12:59
• Reporter: Aurel Pekarcik
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.27
• OS: Windows (Win XP)
• CPU Architecture: Any

[20 Dec 2006 12:15] Aurel Pekarcik

Description:
Incorrect values are assigned to user variables.

How to repeat:
use test;

drop table if exists T;
create table T( ID int primary key auto_increment,
 aGroup char(3),Amt decimal(12,2), Qty decimal(12,2) );

insert T(aGroup,Amt,Qty) values ('one',10,3),('one',12,2),('one',11,4);
insert T(aGroup,Amt,Qty) values ('too',15,4),('too',17,6),('too',21,5);

select aGroup, @A:=Sum(Amt),@Q:=Sum(Qty),@A/@Q,@A,@Q from test.t
group by aGroup

[20 Dec 2006 12:59] MySQL Verification Team

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The query example is performed from right to left, so:

C:\mydb\bin>mysql -uroot -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.27-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql>
mysql> drop table if exists T;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table T( ID int primary key auto_increment,
    ->  aGroup char(3),Amt decimal(12,2), Qty decimal(12,2) );
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> insert T(aGroup,Amt,Qty) values ('one',10,3),('one',12,2),('one',11,4);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert T(aGroup,Amt,Qty) values ('too',15,4),('too',17,6),('too',21,5);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> select aGroup, @A:=Sum(Amt),@Q:=Sum(Qty),@A/@Q,@A,@Q from test.t
    -> group by aGroup;
+--------+--------------+--------------+-------+------+------+
| aGroup | @A:=Sum(Amt) | @Q:=Sum(Qty) | @A/@Q | @A   | @Q   |
+--------+--------------+--------------+-------+------+------+
| one    |        33.00 |         9.00 |  NULL | NULL | NULL |
| too    |        53.00 |        15.00 |  NULL | NULL | NULL |
+--------+--------------+--------------+-------+------+------+
2 rows in set (0.00 sec)

mysql>
mysql> select @A/@Q,@A,@Q;
+----------------------------------+-------+-------+
| @A/@Q                            | @A    | @Q    |
+----------------------------------+-------+-------+
| 3.533333333333333333000000000000 | 53.00 | 15.00 |
+----------------------------------+-------+-------+
1 row in set (0.00 sec)

mysql>