Tested on Debian 9 + MCS 1.1.2

Interesting is the error message for the (not) possible workaround with or
"ERROR 1178 (42000): The storage engine for the table doesn't support Unknown item type"

MariaDB [t]> SELECT astr FROM test_mcs WHERE  CASE WHEN COALESCE('0') = '0' THEN  astr in ('test') or astr in ('fff')  END;

ERROR 1178 (42000): The storage engine for the table doesn't support Unknown item type

MariaDB [t]> SELECT astr FROM test_inno WHERE  CASE WHEN COALESCE('0') = '0' THEN  astr in ('test') or astr in ('fff')  END;

+------+

| astr |

+------+

| test |

+------+

1 row in set (0.00 sec)

The reason this works for a single item for IN is that the IN is optimized away to "astr = 'test'" which works fine.

The problem is that case doesn't support a boolean in the THEN clause. Everything works as expected, but the lack of the getBoolVal() function meant it called the getIntVal() method instead. class ConstantFilter, which implements the IN clause, doesn't support getIntVal() so the default implementation was used, which always returns 0.

The fix is to add getBoolVal() to case.

MCOL-1196 has been opened to address the error:

SELECT astr FROM test_mcs WHERE CASE WHEN COALESCE('0') = '0' THEN astr in ('test') or astr in ('fff') END;
ERROR 1178 (42000): The storage engine for the table doesn't support Unknown item type

Build verified: Github source 1.1.3-1

root@stretch:~/columnstore/mariadb-columnstore-server# git show
commit e5499e513d88a3dfefbe9a356e20a1bceb1bde38
Merge: 99cdb0a4b5 4840a435aa
Author: david hill <david.hill@mariadb.com>
Date: Wed Jan 31 16:53:52 2018 -0600

Merge pull request #92 from mariadb-corporation/MCOL-1152

MCOL-1152: Change the debian package names.

root@stretch:~/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine# git show
commit c18950d36e750861ce4a1c15734d6ddf3c752a7c
Merge: 446cf74a 30b4edb0
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Sat Feb 3 00:03:53 2018 +0200

Merge pull request #395 from mariadb-corporation/MCOL-1178

MCOL-1178 Add bool processing to CASE

Reproduced the issue in 1.1.2-1 and verified fix in 1.1.3-1

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.12-MariaDB-log Columnstore 1.1.3-1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database ct;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use ct;
Database changed
MariaDB [ct]> create table test_inno (astr VARCHAR(50))ENGINE = INNODB ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [ct]> create table test_mcs (astr VARCHAR(50))ENGINE = COLUMNSTORE ;
Query OK, 0 rows affected (0.56 sec)

MariaDB [ct]> INSERT INTO test_inno (astr) value('test');
Query OK, 1 row affected (0.01 sec)

MariaDB [ct]> INSERT INTO test_mcs (astr) value('test');
Query OK, 1 row affected (0.11 sec)

MariaDB [ct]> SELECT astr FROM test_mcs WHERE CASE WHEN COALESCE('0') = '0' THEN astr IN ('test','unknown') END;
------

------

------
1 row in set (0.06 sec)

MariaDB [ct]>