Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.33a, 5.3.13, 5.5, 10.0, 10.1
-
None
-
Centos 7
Description
Test Case:
CREATE TABLE `testing` ( |
`datas` VARCHAR(25) NOT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
|
INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4'); |
|
Shows issue -- See last row |
SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`; |
+-------+--------+-------+ |
| First | Second | Third | |
+-------+--------+-------+ |
| 1.5 | 1 | 2 |
|
| 2.0 | 1 | 2 |
|
| 0.0 | NULL | 1 | |
+-------+--------+-------+ |
|
|
SELECT IF(FIND_IN_SET('1', `datas`), 1, IF(FIND_IN_SET('2', `datas`), 2.5, IF(FIND_IN_SET('5', `datas`), 5, NULL))) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`; |
+-------+--------+-------+ |
| First | Second | Third | |
+-------+--------+-------+ |
| 1.0 | 1 | 2 |
|
| 2.5 | 1 | 2 |
|
| 0.0 | NULL | 1 | |
+-------+--------+-------+ |
|
Works as expected |
SELECT IF(FIND_IN_SET('1', `datas`), '1.5', IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`; |
+-------+--------+-------+ |
| First | Second | Third | |
+-------+--------+-------+ |
| 1.5 | 1 | 2 |
|
| 2 | 1 | 2 |
|
| NULL | 1 | 2 | |
+-------+--------+-------+ |
|
|
SELECT IF(FIND_IN_SET('1', `datas`), 1, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `testing`; |
+-------+--------+-------+ |
| First | Second | Third | |
+-------+--------+-------+ |
| 1 | 1 | 2 |
|
| 2 | 1 | 2 |
|
| NULL | 1 | 2 | |
+-------+--------+-------+ |
The issue is that when an IF statement returns a NULL value and there's a decimal value being returned for one of the upstream IF options then the IF is returning a 0.0 value as well as the expected NULL and therefore causing all further values to be shifted to the next column.
If I change the decimal to a string or an integer, then the expected data is returned as shown in the last two SELECT statements.
I've tested using MyISAM and InnoDB and it happens to both. I tested and confirmed this happens on MariaDB version "10.0.21-MariaDB-log". This issue does not happen on version "5.5.32-MariaDB-log". It does not happen on 5.6.26 MySQL Community Server (GPL) either.