Details
Critical 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
- is duplicated by
-
MDEV-10461 Multiple left join, group by query returns wrong result type
-
- Closed
-