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

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.33a, 5.3.13, 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 5.5.46, 10.0.22, 10.1.7
    • OTHER
    • 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

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.