Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-1851

CASE statement cannot handle null values

    XMLWordPrintable

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

      Attachments

        Activity

          People

            Unassigned Unassigned
            frank_svb_nl Frank Groot
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.