Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5.33a, 5.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Test Case: CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 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: 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`; 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`; 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`; 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`; 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. |
Test Case: {code:sql} CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4'); {code} {code:sql|title=Shows issue} 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`; 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`; {code} {code:sql|title=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`; 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`; {code} 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. |
| Description |
Test Case: {code:sql} CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4'); {code} {code:sql|title=Shows issue} 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`; 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`; {code} {code:sql|title=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`; 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`; {code} 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. |
Test Case: {code:sql} CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4'); {code} {code:sql|title=Shows issue} 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`; 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`; {code} {code:sql|title=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`; 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`; {code} 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. |
| Description |
Test Case: {code:sql} CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4'); {code} {code:sql|title=Shows issue} 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`; 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`; {code} {code:sql|title=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`; 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`; {code} 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. |
Test Case: CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 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: 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`; 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`; 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`; 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`; 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. |
| Description |
Test Case: CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 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: 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`; 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`; 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`; 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`; 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. |
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: 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`; 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`; 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`; 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`; 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. |
| 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: 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`; 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`; 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`; 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`; 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. |
Test Case: {code:sql} CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4'); {code} {code:sql|title=Shows issue} 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`; 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`; {code} {code:sql|title=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`; 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`; {code} 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. |
| Description |
Test Case: {code:sql} CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4'); {code} {code:sql|title=Shows issue} 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`; 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`; {code} {code:sql|title=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`; 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`; {code} 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. |
Test Case: {code:sql} CREATE TABLE `testing` ( `datas` VARCHAR(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `testing` VALUES ('1,2'), ('2,3'), ('3,4'); {code} {code:sql|title=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 | +-------+--------+-------+ {code} {code:sql|title=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 | +-------+--------+-------+ {code} 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. |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 5.5.33a [ 13500 ] | |
| Affects Version/s | 5.3.13 [ 12602 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0.21 [ 19406 ] | |
| Assignee | Alexander Barkov [ bar ] | |
| Summary | IF Statement returns multiple values erroneously | IF Statement returns multiple values erroneously (or Assertion `!null_value' failed in Item::send(Protocol*, String*)) |
| Assignee | Alexander Barkov [ bar ] | Oleksandr Byelkin [ sanja ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Alexander Barkov [ bar ] |
| Status | Confirmed [ 10101 ] | In Review [ 10002 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Component/s | OTHER [ 10125 ] | |
| Fix Version/s | 10.0.22 [ 19700 ] | |
| Fix Version/s | 10.1.7 [ 19604 ] | |
| Fix Version/s | 5.5.46 [ 19603 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Review [ 10002 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 71173 ] | MariaDB v4 [ 149503 ] |