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
Triage: Needs Triage: D3 (Medium)

[30 Apr 2013 9:53] Gabriel Delépine
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`;
[30 Apr 2013 18:32] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with version 5.5. Could you please send us your configuration file?
[1 May 2013 9:28] Gabriel Delépine
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)
[3 May 2013 7:49] Gabriel Delépine
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
[3 May 2013 13:42] Gabriel Delépine
One more test with Debian 6.0 x86_64 and mysql 5.6.10-log and the bug still exists.
[4 May 2013 4:18] Gabriel Delépine
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.
[6 May 2013 20:11] Sveta Smirnova
Thank you for the feedback.

Verified as described.

Issue is only repeatable with storage engine InnoDB.