MySQL Bugs: #69100: CAST in IF function fails in some case

Bug #69100	CAST in IF function fails in some case
Submitted:	30 Apr 2013 9:53	Modified:	6 May 2013 20:11
Reporter:	Gabriel Delépine	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.31-0, 5.5.30-1, 5.5.29-0, 5.5.25-1, 5.5.16, 5.5.30-1.1, 5.6.10, 5.0.97, 5.1.70, 5.5.29, 5.7.2	OS:	Linux (Debian 6 / Debian 7 / Ubuntu 12.10 / Windows 8)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	cast fails, conditional, IF, order by, wrong value return

Description:
In a table, I have decimal value.

In a select, I made a conditional function (IF) to display DECIMAL value or INT value if decimal part = 0.

Bug : If I make a JOIN with an other table with a ORDER BY, all returned values of the IF function are DECIMAL. INT CAST works but value by cast a second time to DECIMAL again.

Without the ORDER BY or without JOIN, the results are : 
123.456 / 123 / 123
124 / 124 / 124

With the ORDER BY and with the JOIN, the results are :
123.456 / 123.000 / 123
124.000 / 124.000 / 124

How to repeat:
CREATE SCHEMA `mysql_report` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;
use mysql_report;

CREATE  TABLE `MY_TABLE` (`id` DECIMAL(6,3) NOT NULL );

INSERT INTO MY_TABLE VALUES (123.456);
INSERT INTO MY_TABLE VALUES (124.000);

CREATE  TABLE `MY_SECOND_TABLE` (`a_field` INT NOT NULL );

INSERT INTO MY_SECOND_TABLE VALUES (9);

SELECT
if((mv.id = cast(mv.id as unsigned integer)), cast(mv.id as unsigned),mv.id) AS `conditional`,
if(1, cast(mv.id as unsigned),mv.id) as `always`,
cast(mv.id as unsigned) as `only_cast`
FROM MY_TABLE mv
    INNER JOIN MY_SECOND_TABLE mst
        ON 1;

+-------------+--------+-----------+
| conditional | always | only_cast |
+-------------+--------+-----------+
|     123.456 |    123 |       123 |
|         124 |    124 |       124 |
+-------------+--------+-----------+

        
SELECT
if((mv.id = cast(mv.id as unsigned integer)), cast(mv.id as unsigned),mv.id) AS `conditional`,
if(1, cast(mv.id as unsigned),mv.id) as `always`,
cast(mv.id as unsigned) as `only_cast`
FROM MY_TABLE mv
    INNER JOIN MY_SECOND_TABLE mst
        ON 1
ORDER BY 1;

+-------------+---------+-----------+
| conditional | always  | only_cast |
+-------------+---------+-----------+
|     123.456 | 123.000 |       123 |
|     124.000 | 124.000 |       124 |
+-------------+---------+-----------+

drop schema `mysql_report`;

Thank you for the report.

I can not repeat described behavior with version 5.5. Could you please send us your configuration file?

If add the my.cnf file of the Ubuntu system to this bug report.

I upgrade mysql on the ubuntu system (From official ubuntu repository) to 5.5.31-0ubuntu0.12.10.1 but the bug still exists.

On the 2 debian 6 server, mysql comme from the DOTDEB repository.
5.5.25-1~dotdeb.0 (Debian)
5.5.30-1~dotdeb.0 (Debian)

I asked to other people to try and they also got the bug with the following configurations :

- Windows 8 x64 avec Mysql 5.5.16 - MySQL Community Server

- 5.5.30-1.1 / debian wheezy

One more test with Debian 6.0 x86_64 and mysql 5.6.10-log and the bug still exists.

One more test in an other server with :

mysql  Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2

Same bug.

Thank you for the feedback.

Verified as described.

Issue is only repeatable with storage engine InnoDB.