MySQL Bugs: #64765: Strange behavior of GREATEST, when use it in subquery with variables

Bug #64765	Strange behavior of GREATEST, when use it in subquery with variables
Submitted:	26 Mar 2012 13:38	Modified:	6 Nov 2019 21:44
Reporter:	Oleksandr Dyklevych	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.62, 5.5.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GREATEST, subquery, variables

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.

Same with 5.0.96, 5.1.62 and 5.5.20

Peter
(not a MySQL person).

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.

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?

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)

Posted by developer:
 
The problem is related to the sequence of resolver operations.
When the query is first run, the variables do not exist.
The "set" operation is run first and is internally tagged as an integer operation,
since it assigns an integer value to the variable. However, the variable is created
with default type character. Later the variables are used in resolving of GREATEST,
and when seeing three character expressions, the GREATEST operation is also assumed
to be character string. Later, when the variables are assigned to, they are converted
to their correct type: integer. When GREATEST is later evaluated, it reads three integers
but convert them to string and the biggest of "0", "33" and "6" is "6".

On second run, the variables exist and have type integer. All expressions are correctly
resolved as integer and GREATEST returns max of the 3 integer expressions, ie 33.

I think this problem may be solved by creating the variables first as type "unknown" and
when later seeing it in an assignment, type can be set properly as "integer". However,
the effort is quite big and care must be taken to avoid regressions.

Posted by developer:
 
Not a bug, but a consequence of how resolving of user variables is implemented.