MySQL Bugs: #45300: MAX() and ENUM type

Bug #45300	MAX() and ENUM type
Submitted:	3 Jun 2009 11:52	Modified:	1 Apr 2011 16:52
Reporter:	Nuno Tavares	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	enum, MAX, min

Description:
In some aggregating functions, like MAX(), MIN(), etc, the value of an ENUM type field being evaluated is the STRING component, instead of the INTEGER component, which is the point in using ENUM type fields.

Note however that indexing is OK, as well as ORDER BY.

How to repeat:
CREATE TABLE `example` (
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type` enum('c','b','a') NOT NULL DEFAULT 'c'
) ENGINE=MyISAM;

INSERT INTO example(type) VALUES ('a'),('c'),('b');

Now, use an aggregating function:

mysql> select MAX(type) from example;
+-----------+
| MAX(type) |
+-----------+
| c         | 
+-----------+
1 row in set (0.00 sec)

According to http://dev.mysql.com/doc/refman/5.1/en/enum.html, it should have returned 'a', since in the ENUM context, 'c' < 'a', so it's probably evaluating the string instead the integer value.

I know this is a known bug, as reported in 2002:
http://osdir.com/ml/db.mysql.bugs/2002-09/msg00002.html

I had a quick look around and couldn't find the related bug reported here in bugs.mysql.com.

However, if you request the field in the INT context, it will work correctly:

mysql> select MAX(type+0) from example;
+-------------+
| MAX(type+0) |
+-------------+
|           3 | 
+-------------+
1 row in set (0.00 sec)

That is, 3 = 'a', so it's correct. However there is no way I could find to get the related string value back.

Suggested fix:
According to Sinisa Milivojevic, later on the cited thread[1], the fix for the bug itself seems to be complex.

[1] http://osdir.com/ml/db.mysql.bugs/2002-09/msg00036.html

In the meantime, while the bug is being fixed (I believe :)) I think there should be a function to convert the INT value to the VARCHAR related value, which would be doing something similar to what FIND_IN_SET() does, but looking at the column type definition.

I'm sorry, it seems I didn't look enough. I just checked
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max

which states:
"For MAX(), MySQL currently compares ENUM and SET columns by their string value rather than by the string's relative position in the set. This differs from how ORDER BY  compares them. This is expected to be rectified in a future MySQL release."

Well, anyway, 2009-2002 = 7 years, so maybe this will remind someone it should be fixed :-)

Thank you for the bug report.

From what I can figure, this still isn't fixed ?

Adrian, Just verified in 5.1.37, it still happens.

Please note also: 
«
I think there should be a function to convert the INT value to the VARCHAR related value, which would be doing something similar to what FIND_IN_SET() does, but looking at the column type definition.
»

Best, -NT