A bug with MAX and ENUM type

Probably there is a bug with MAX function and ENUM field type.

MySQL: 3.23.52
OS: Linux 2.2.20

How-To-Repeat:

Consider the following table with its values:

CREATE TABLE test (
id int(11) NOT NULL auto_increment,
item varchar(30) NOT NULL default '',
property enum('one','two','three','four') NOT NULL default 'one',
UNIQUE KEY id (id)
) TYPE=MyISAM;

INSERT INTO test VALUES (1, 'orange', 'two');
INSERT INTO test VALUES (2, 'blue', 'four');
INSERT INTO test VALUES (3, 'yellow', 'two');
INSERT INTO test VALUES (4, 'orange', 'three');
INSERT INTO test VALUES (5, 'yellow', 'one');
INSERT INTO test VALUES (6, 'blue', 'three');

so:
SELECT * FROM test;

returns:
+----+--------+----------+
| id | item | property |
+----+--------+----------+
| 1 | orange | two |
| 2 | blue | four |
| 3 | yellow | two |
| 4 | orange | three |
| 5 | yellow | one |
| 6 | blue | three |
+----+--------+----------+


As explained here http://www.mysql.com/doc/en/ENUM.html MySQL should
associates the following values to the ENUM values:

one -> 1
two -> 2
three -> 3
four -> 4

Now, try the following query:

SELECT id, item, MAX(property) FROM test GROUP BY item;

the result will be:
+----+--------+---------------+
| id | item | MAX(property) |
+----+--------+---------------+
| 2 | blue | three |
| 1 | orange | two |
| 3 | yellow | two |
+----+--------+---------------+

but it should be:
+----+--------+---------------+
| id | item | MAX(property) |
+----+--------+---------------+
| 2 | blue | four |
| 4 | orange | three |
| 3 | yellow | two |
+----+--------+---------------+

That's all.
Thank you,
Nico





------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12458@lists.mysql.com
To unsubscribe, e-mail

A bug with MAX and ENUM type

Hi!

>>>>> "tricera" == tricera it writes:

tricera> Probably there is a bug with MAX function and ENUM field type.
tricera> MySQL: 3.23.52
tricera> OS: Linux 2.2.20

tricera> How-To-Repeat:



tricera> SELECT id, item, MAX(property) FROM test GROUP BY item;

tricera> the result will be:
tricera> +----+--------+---------------+
tricera> | id | item | MAX(property) |
tricera> +----+--------+---------------+
tricera> | 2 | blue | three |
tricera> | 1 | orange | two |
tricera> | 3 | yellow | two |
tricera> +----+--------+---------------+

tricera> but it should be:
tricera> +----+--------+---------------+
tricera> | id | item | MAX(property) |
tricera> +----+--------+---------------+
tricera> | 2 | blue | four |
tricera> | 4 | orange | three |
tricera> | 3 | yellow | two |
tricera> +----+--------+---------------+

Just a note about this:

Sinisa has fixed this in the 4.0 tree and will push this shortly.
This will be in the 4.0.4 binary.

(As this is not a critical bug we will not fix this in 3.23 as this
may cause unexpected problems for old users that hasn't noticed this
problem and is relying on this 'wrong' behavior)

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12486@lists.mysql.com
To unsubscribe, e-mail

Re: A bug with MAX and ENUM type

Nicola writes:
> Probably there is a bug with MAX function and ENUM field type.
>
> MySQL: 3.23.52
> OS: Linux 2.2.20
>
> How-To-Repeat:
>
> Consider the following table with its values:
>

[skip]

>
> SELECT id, item, MAX(property) FROM test GROUP BY item;
>
> the result will be:
> +----+--------+---------------+
> | id | item | MAX(property) |
> +----+--------+---------------+
> | 2 | blue | three |
> | 1 | orange | two |
> | 3 | yellow | two |
> +----+--------+---------------+
>
> but it should be:
> +----+--------+---------------+
> | id | item | MAX(property) |
> +----+--------+---------------+
> | 2 | blue | four |
> | 4 | orange | three |
> | 3 | yellow | two |
> +----+--------+---------------+
>
> That's all.
> Thank you,
> Nico
>

Hi!

Thank you for your bug report.

Unfortunately, this behaviour is not as easy to fix as we thought it
would be.

To make it properly our code base would have a feature to distingish a
manner in which column values are represented from the manner in
which they are compared.

This will require some major changes in the code and we are planning
to make those changes in version 4.1.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12490@lists.mysql.com
To unsubscribe, e-mail