[MDEV-10500] CASE/IF Statement returns multiple values and shifts further result values to the next column Created: 2016-08-05  Updated: 2016-08-08  Resolved: 2016-08-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5.50, 10.0.19, 10.0.22
Fix Version/s: 5.5.51, 10.1.17, 10.0.27, 10.2.2

Type: Bug Priority: Critical
Reporter: Dmitri Malinovski Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: innodb, tokudb

Issue Links:
Duplicate
is duplicated by MDEV-10461 Multiple left join, group by query re... Closed

 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.

 Comments   
Comment by Sergei Golubchik [ 2016-08-05 ]

Thanks for the perfect test case!

In debug builds of 5.5 it fails an assert:

mysqld: /home/serg/Abk/5.5/sql/item_func.h:435: my_decimal* Item_func_hybrid_result_type::decimal_op_with_null_check(my_decimal*): Assertion `(res != __null) ^ null_value' failed.
 
160805 15:48:40 [ERROR] mysqld got signal 6 ;

Comment by Elena Stepanova [ 2016-08-05 ]

See also MDEV-10461 – it might be related or a duplicate.

Comment by Sergei Golubchik [ 2016-08-08 ]

ok to push

Generated at Thu Feb 08 07:42:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.