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

empty result with "case .. when" with where condition and multiple parameter in "IN" clause

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.0.0
    • 1.1.3
    • None
    • None
    • Tested on Debian 9 + MCS 1.1.2
    • 2018-03

    Description

      to reproduce:

      Preparation:

      create database ct;
      use ct;
      create table test_inno (astr VARCHAR(50))ENGINE = INNODB ;
      create table test_mcs (astr VARCHAR(50))ENGINE = COLUMNSTORE ;
      INSERT INTO test_inno (astr) value('test');
      INSERT INTO test_mcs (astr) value('test');
      

      With one parameter , all is fine.

      MariaDB [t]> SELECT astr FROM test_inno WHERE  CASE WHEN COALESCE('0') = '0' THEN  astr IN ('test') END;
      +------+
      | astr |
      +------+
      | test |
      +------+
      1 row in set (0.00 sec)
       
      MariaDB [t]> SELECT astr FROM test_mcs WHERE  CASE WHEN COALESCE('0') = '0' THEN  astr IN ('test') END;
      +------+
      | astr |
      +------+
      | test |
      +------+
      1 row in set, 1 warning (0.03 sec)
      
      

      With two parameter, the columnstore table return no resultset, even the where condition is true.

      MariaDB [t]> SELECT astr FROM test_inno WHERE  CASE WHEN COALESCE('0') = '0' THEN  astr IN ('test','unknown') END;
      +------+
      | astr |
      +------+
      | test |
      +------+
      1 row in set (0.00 sec)
       
      MariaDB [t]> SELECT astr FROM test_mcs WHERE  CASE WHEN COALESCE('0') = '0' THEN  astr IN ('test','unknown') END;
      Empty set, 1 warning (0.01 sec)
      
      

      Attachments

        Activity

          People

            dleeyh Daniel Lee (Inactive)
            Richard Richard Stracke
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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