Bug #64765 Strange behavior of GREATEST, when use it in subquery with variables
Submitted: 26 Mar 2012 13:38 Modified: 26 Mar 2012 16:22
Reporter: Oleksandr Dyklevych Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.62, 5.5.20 OS:Any
Assigned to:
Tags: GREATEST, subquery, variables
Triage: Needs Triage: D2 (Serious)

[26 Mar 2012 13:38] Oleksandr Dyklevych
Description:
When arguments for function GREATEST are computed variables, and GREATEST is used in subquery, then it produces strange results during the first run. All the consequent runs produce expected result.
At the same time, if run subquery itself, it will produce expected result always.

How to repeat:
Run this code:
SELECT
	a.`mdm`
	,a.`mdmb`
	,a.`mds`
	,a.`ml`
FROM (
	SELECT
		@mdm := 33 AS `mdm`
		,@mdmb := 0 AS `mdmb`
		,@mds := 6 AS `mds`
		,GREATEST(@mdm, @mdmb, @mds) AS `ml`
	FROM
		(SELECT @mdm := 0, @mdmb := 0, @mds := 0) AS InitV) AS a
First time the field a.`ml` = 6, all consequent times - 33. 

Suggested fix:
No suggestion.
[26 Mar 2012 13:54] Peter Laursen
Same with 5.0.96, 5.1.62 and 5.5.20

Peter
(not a MySQL person).
[26 Mar 2012 14:01] Peter Laursen
I tried LEAST().  Her the data are correct.

But both GREATEST() and LEAST() seem to have a problem with metadata for the `ml` column in first run.
[26 Mar 2012 14:08] Oleksandr Dyklevych
Well, i think, that it is not related to any certain function.
It should have been related to using variables in subqueries.
for example,
SELECT
	a.`mdm`
	,a.`mdmb`
	,a.`mds`
	,a.`ml`
FROM (
	SELECT
		@mdm := 33 AS `mdm`
		,@mdmb := 0 AS `mdmb`
		,@mds := 6 AS `mds`
		,@mdm > @mds AS `ml`
	FROM
		(SELECT @mdm := 0, @mdmb := 0, @mds := 0) AS InitV) AS a
a.`ml` after first run = 0, all consequent = 1.
I understand, that the order of calculation is not determined, but why only for the first time?
[26 Mar 2012 16:22] Valerii Kravchuk
Thank you for the problem report. Verified just as described:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, 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> SELECT
    -> a.`mdm`
    -> ,a.`mdmb`
    -> ,a.`mds`
    -> ,a.`ml`
    -> FROM (
    -> SELECT
    -> @mdm := 33 AS `mdm`
    -> ,@mdmb := 0 AS `mdmb`
    -> ,@mds := 6 AS `mds`
    -> ,GREATEST(@mdm, @mdmb, @mds) AS `ml`
    -> FROM
    -> (SELECT @mdm := 0, @mdmb := 0, @mds := 0) AS InitV) AS a
    -> ;
+-----+------+-----+------+
| mdm | mdmb | mds | ml   |
+-----+------+-----+------+
|  33 |    0 |   6 | 6    |
+-----+------+-----+------+
1 row in set (0.12 sec)

mysql> SELECT a.`mdm` ,a.`mdmb` ,a.`mds` ,a.`ml` FROM ( SELECT @mdm := 33 AS `mdm` ,@mdmb := 0 AS `mdmb` ,@mds := 6 AS `mds` ,GREATEST(@mdm, @mdmb, @mds) AS `ml` FROM (SELECT @mdm := 0, @mdmb := 0, @mds := 0) AS InitV) AS a;
+-----+------+-----+------+
| mdm | mdmb | mds | ml   |
+-----+------+-----+------+
|  33 |    0 |   6 |   33 |
+-----+------+-----+------+
1 row in set (0.01 sec)

mysql> SELECT a.`mdm` ,a.`mdmb` ,a.`mds` ,a.`ml` FROM ( SELECT @mdm := 33 AS `mdm` ,@mdmb := 0 AS `mdmb` ,@mds := 6 AS `mds` ,GREATEST(@mdm, @mdmb, @mds) AS `ml` FROM (SELECT @mdm := 0, @mdmb := 0, @mds := 0) AS InitV) AS a;
+-----+------+-----+------+
| mdm | mdmb | mds | ml   |
+-----+------+-----+------+
|  33 |    0 |   6 |   33 |
+-----+------+-----+------+
1 row in set (0.01 sec)

mysql> explain SELECT a.`mdm` ,a.`mdmb` ,a.`mds` ,a.`ml` FROM ( SELECT @mdm := 33 AS `mdm` ,@mdmb := 0 AS `mdmb` ,@mds := 6 AS `mds` ,GREATEST(@mdm, @mdmb, @mds) AS `ml` FROM (SELECT @mdm := 0, @mdmb := 0, @mds := 0) AS InitV) AS a;
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |                |
|  2 | DERIVED     | <derived3> | system | NULL          | NULL | NULL    | NULL |    1 |                |
|  3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
3 rows in set (0.04 sec)