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: