| 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: | |
| 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 | ||
[8 May 2012 19:03]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior with any current version. Which exact version of MySQL server do you use?
[9 May 2012 12:35]
Mike Palm
+-------------------------+------------------------------+ | 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)
[10 May 2012 15:28]
Sveta Smirnova
Thank you for the feedback. But version 5.6.2-m5-log is outdated. Please try current version 5.6.5.
[11 Jun 2012 1:00]
Bugs System
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".
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)