MySQL Bugs: #15398: select distinct

Bug #15398	select distinct
Submitted:	1 Dec 2005 17:52	Modified:	25 May 2006 10:43
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.15-nt	OS:	Windows (Windows XP Pro, SP1)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
select distinct
  code,
  if(code='d',1.5,12) as max_val
from unit

expected result:
code   max_val
----------------
d        1.5
h        12

my result:
code   max_val
----------------
d        0.9
h        0.9

How to repeat:
-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.15-nt

--
-- Table structure for table `test`.`unit`
--

DROP TABLE IF EXISTS `unit`;
CREATE TABLE `unit` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `code` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test`.`unit`
--

INSERT INTO `unit` (`id`,`code`) VALUES 
 (1,'d'),
 (2,'h');

--run the select statement!

Suggested fix:
no idea

Verified on Linux (FC4) with 5.0 from bk tree ChangeSet@1.2027, 2005-11-29

The problem is with DISTINCT clause:

mysql> select distinct   code,   if(code='d',1.5,12) as max_val from unit;
+------+---------+
| code | max_val |
+------+---------+
| d    |     0.9 |
| h    |     0.9 |
+------+---------+
2 rows in set (0.00 sec)

mysql> select code,   if(code='d',1.5,12) as max_val from unit;
+------+---------+
| code | max_val |
+------+---------+
| d    |     1.5 |
| h    |      12 |
+------+---------+
2 rows in set (0.00 sec)

Note that 4.1 returns correct result:
mysql> select distinct   VERSION(), code,   if(code='d',1.5,12) as max_val from unit;
+------------------+------+---------+
| VERSION()        | code | max_val |
+------------------+------+---------+
| 4.1.16-debug-log | d    |     1.5 |
| 4.1.16-debug-log | h    |    12.0 |
+------------------+------+---------+

Thank you for your quick reaction, Alexander.
Obviously, the example posted does not (necessarily) have to use DISTINCT. However, I do use such a construction in a more complex SQL, where is needed the distinct clause. Do you have any suggestions for a workaround?
Kind regards,
Ducu

The bug also appears with a MyISAM table:

heikki@127:~/mysql-5.0.16/client$ ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `unit` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `code` varchar(10) NOT NULL default '',
    ->   PRIMARY KEY  (`id`)
    -> ) type = myisam;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> INSERT INTO `unit` (`id`,`code`) VALUES
    ->  (1,'d'),
    ->  (2,'h');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select distinct
    ->   code,
    ->   if(code='d',1.5,12) as max_val
    -> from unit
    -> ;
+------+---------+
| code | max_val |
+------+---------+
| d    |     0.9 |
| h    |     0.9 |
+------+---------+
2 rows in set (0.05 sec)

mysql>

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

Tried it with version 5.0.23-BK (FC5) and the query returns the correct results.