user variable ist not assigned when using function like SUM(), MAX(), AVG()

Hello,

this is may first bug report so I don't really know exactly what and how to
tell ...

The bug appears in all tested versions:
4.0.12-nt/Windows 2000 prof. SP4
4.0.15/Linux/SuSE
4.0.17-nt/Windows XP prof. SP1
4.0.20-nt/Windows 2000 prof. SP4
5.0.00-alpha/Windows XP prof. SP1

User variables are assigned correctly with
SELECT @var := value;

This fails when one of the the following functions is used
MIN(), MAX(), SUM(), AVG(), COUNT() ...


Screenshot of command line sequence:

--------------------- 8< ---------------------------

mysql> -- MySQL dump 9.11
mysql> --
mysql> -- Host: localhost Database: test
mysql> -- ------------------------------------------------------
mysql> -- Server version 4.0.20a-nt
mysql>
mysql> --
mysql> -- Table structure for table `test`
mysql> --
mysql>
mysql> CREATE TABLE test (
-> a int(11) default NULL
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> --
mysql> -- Dumping data for table `test`
mysql> --
mysql>
mysql> INSERT INTO test VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (5);
Query OK, 1 row affected (0.00 sec)

mysql> SET @var = "";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @var := t.a, @var
-> FROM test t;
+-------------+------+
| @var := t.a | @var |
+-------------+------+
| 5 | 5 |
| 4 | 4 |
| 3 | 3 |
| 2 | 2 |
| 1 | 1 |
+-------------+------+
5 rows in set (0.02 sec)

mysql> SET @var = "";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @var := SUM(t.a), @var
-> FROM test t
-> GROUP BY t.a;
+------------------+------+
| @var := SUM(t.a) | @var |
+------------------+------+
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
+------------------+------+
5 rows in set (0.05 sec)

mysql> SELECT @var;
+------+
| @var |
+------+
| 5 |
+------+
1 row in set (0.00 sec)

--------------------- >8 ---------------------------

--
Supergünstige DSL-Tarife + WLAN-Router für 0,- EUR*
Jetzt zu GMX wechseln und sparen http://www.gmx.net/de/go/dsl


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: user variable ist not assigned when using function like SUM(),MAX(), AVG()

Daniel.Musketa@gmx.de wrote:
> Hello,
>
> this is may first bug report so I don't really know exactly what and how to
> tell ...
>
> The bug appears in all tested versions:
> 4.0.12-nt/Windows 2000 prof. SP4
> 4.0.15/Linux/SuSE
> 4.0.17-nt/Windows XP prof. SP1
> 4.0.20-nt/Windows 2000 prof. SP4
> 5.0.00-alpha/Windows XP prof. SP1
>
> User variables are assigned correctly with
> SELECT @var := value;
>
> This fails when one of the the following functions is used
> MIN(), MAX(), SUM(), AVG(), COUNT() ...
>

This is documented behaviour, see

http://dev.mysql.com/doc/mysql/en/Variables.html

[...]

Note: In a SELECT statement, each expression is evaluated only
when sent to the client. This means that in a HAVING, GROUP BY,
or ORDER BY clause, you cannot refer to an expression that
involves variables that are set in the SELECT list.

[...]

The general rule is to never assign and use the same variable
in the same statement.

[...]

--
Hartmut Holzgraefe

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org