Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
1.1.5
-
None
-
None
-
None
-
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