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

CASE/IF Statement returns multiple values and shifts further result values to the next column

    XMLWordPrintable

    Details

      Description

      Statement returns wrong result values using the control flow functions IF()/CASE combined with SUM() function, when the result of SUM() function is NULL. As a result all further values are shifted to the next column.
      Test Case:

      create table STAT_TEST (
        id int not null AUTO_INCREMENT,
        active bool not null,
        data1 bigint,
        data2 bigint,
        data3 bigint,
        primary key (id)
      );
       
      insert into STAT_TEST (active,data1,data2,data3)
      values(1,null,100,200);
      

      Using CASE. Expected: C_2 = 100, C_3 = 200

      MariaDB [test]> select
          ->   case when active then sum(data1) end as C_1,
          ->   sum(data2) as C_2,
          ->   sum(data3) as C_3
          -> from STAT_TEST;
      +------+------+------+
      | C_1  | C_2  | C_3  |
      +------+------+------+
      |    0 | NULL |  100 |
      +------+------+------+
      1 row in set (0.00 sec)
      

      Using IF(). Expected: C_2 = 100, C_3 = 200

      MariaDB [test]> select
          ->   if(active, sum(data1), 5) as C_1,
          ->   sum(data2) as C_2,
          ->   sum(data3) as C_3
          -> from STAT_TEST;
      +------+------+------+
      | C_1  | C_2  | C_3  |
      +------+------+------+
      |    0 | NULL |  100 |
      +------+------+------+
      1 row in set (0.00 sec)
      

      I have tested on MariaDB versions "5.5.5-10.0.19-MariaDB-log", "5.5.5-10.0.22-MariaDB-log" using TokuDb and InnoDB and it happens to both.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              Dmitri Dmitri Malinovski
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: