MySQL Bugs: #65238: Incorrect return values in IF and CASE statements

Bug #65238	Incorrect return values in IF and CASE statements
Submitted:	8 May 2012 10:13	Modified:	10 Jun 2012 15:28
Reporter:	Mike Palm	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.2-m5-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	case, if(), INTEGER

Description:
Incorrect return values for expr2 in IF statements when fixed-size integer fields are used in expr1/expr3. These incorrect return values are fuond in CASE statement return values as well.

This behavior occurs only when one or more tables are joined.

How to repeat:
CREATE DATABASE IF NOT EXISTS bug;
USE bug;

CREATE TABLE IF NOT EXISTS `t1` ( `id` tinyint(1) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) );
INSERT INTO  `bug`.`t1` (`id`) VALUES ('2'), ('4'), ('6'), ('8'), ('10');

CREATE TABLE `t2` ( `id` int(1) unsigned NOT NULL AUTO_INCREMENT, `t1_id` tinyint(1) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `t1_id` (`t1_id`) );
INSERT INTO  `bug`.`t2` (`id` ,`t1_id`) VALUES (NULL,  '2'), (NULL,  '4'), (NULL,  '6'), (NULL,  '8'), (NULL,  '10');

-- Nothing wrong with the results when no tables are joined:
SELECT IF(t1.id<=2,10,t1.id) AS `if_result`, CASE WHEN  t1.id <= 2 THEN 10 ELSE t1.id END AS `case_result`,  t1.id AS `id`
FROM t1
GROUP by t1.id;
+-----------+-------------+----+
| if_result | case_result | id |
+-----------+-------------+----+
|        10 |          10 |  2 |
|         4 |           4 |  4 |
|         6 |           6 |  6 |
|         8 |           8 |  8 |
|        10 |          10 | 10 |
+-----------+-------------+----+
5 rows in set (0.00 sec)

-- The first as well as the last record show incorrect return values:
-- Both values should be 10. 
-- If expr2=100, 99 would be returned, if expr2=1000, 999 would be returned, et cetera.
SELECT IF(t1.id<=2,10,t1.id) AS `if_result`, CASE WHEN  t1.id <= 2 THEN 10 ELSE t1.id END AS `case_result`, t1.id AS `id`
FROM t2
INNER JOIN t1 ON t1.id = t2.t1_id
GROUP by t1.id;
+-----------+-------------+----+
| if_result | case_result | id |
+-----------+-------------+----+
|         9 |           9 |  2 |
|         4 |           4 |  4 |
|         6 |           6 |  6 |
|         8 |           8 |  8 |
|         9 |           9 | 10 |
+-----------+-------------+----+

-- When using large numbers, a decimal value warning is thrown:
SELECT IF(t1.id<=2, 1000000000000000000000000000000000000000000000000000000000000000000000000000000000 ,t1.id) AS `if_result`, t1.id AS `id`
FROM t2
INNER JOIN t1 ON t1.id = t2.t1_id
GROUP by t1.id;
+-------------------------------------------------------------------+----+
| if_result                                                         | id |
+-------------------------------------------------------------------+----+
| 99999999999999999999999999999999999999999999999999999999999999999 |  2 |
|                                                                 4 |  4 |
|                                                                 6 |  6 |
|                                                                 8 |  8 |
|                                                                10 | 10 |
+-------------------------------------------------------------------+----+
5 rows in set, 1 warning (0.00 sec)

+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

Thank you for the report.

I can not repeat described behavior with any current version. Which exact version of MySQL server do you use?

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.2-m5-log                 |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

Thank you for the feedback.

But version  5.6.2-m5-log is outdated. Please try current version 5.6.5.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".