Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8663

IF Statement returns multiple values erroneously (or Assertion `!null_value' failed in Item::send(Protocol*, String*))

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 5.5.33a, 5.3.13, 5.5, 10.0, 10.1
    • Fix Version/s: 5.5.46, 10.0.22, 10.1.7
    • Component/s: OTHER
    • Labels:
      None
    • Environment:
      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

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              REW Kyle Kyle Misner
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: