[MCOL-1851] CASE statement cannot handle null values Created: 2018-11-02  Updated: 2021-01-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.1.5
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Frank Groot Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

CentOS 7



 Description   

CASE statement doesn't seem to handle null values correctly using columnstore tables.

Example:
There is a columnstore table with 2 fields, one value of which is null.
The case statement should return the first of all found matches but doesn't always do that.
Catching the null value seems to correct the problem.
Changing the table engine to innodb also corrects the problem.
#########################
drop table if exists mytable;

create table if not exists mytable(mycode varchar(2), mydate date) engine=columnstore;

insert into mytable select '01' as mycode, null as mydate;

select
t.mycode
,t.mydate
,case when t.mycode = '01' then 'reason 01'
when t.mydate is null then 'date is null'
else 'reason <> 01'
end as correct_1
,case when t.mycode = '01' and t.mydate <= '2018-01-01' then 'reason 01 plus date check'
when t.mycode = '01' then 'reason 01'
else 'reason <> 01'
end as wrong
,case when t.mycode = '01' and t.mydate <= '2018-01-01' then 'reason 01 plus date check'
when t.mycode = '01' then 'reason 01'
when t.mydate is null then 'date is null'
else 'reason <> 01'
end as wrong
,case when t.mycode = '01' and coalesce(t.mydate,'2019-01-01') <= '2018-01-01' then 'reason 01 plus date check'
when t.mycode = '01' then 'reason 01'
else 'reason <> 01'
end as correct_2
from mytable t;

outcome:

mycode mydate correct_1 wrong wrong correct_2
01 <null> reason 01 reason <> 01 date is null reason 01

######################################

Can you confirm if this is indeed a columnstore bug?
Thanks,
Frank



 Comments   
Comment by Frank Groot [ 2018-11-16 ]

Please have a look at this guys!
It's important for our data analists that this issue is resolved soon.

Generated at Thu Feb 08 02:31:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.