Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
1.1.4
-
None
-
debian9
-
2018-14, 2018-15, 2018-16
Description
the following type of expression evaluates correctly as a column of a select query, but not in a where clause:
case when (case when ... then ... else ... end) then ... else ... end
the problem appears on varchar columns
here is an example:
create table t(a varchar(20) not null) engine=ColumnStore default character set=utf8;
insert into t(a) values ('a'),('b');
the following query correctly returns 1 row (the expression is evaluated as a column of a select):
SELECT
CASE
WHEN CASE WHEN a IS NULL THEN 0 ELSE a = 'a' END
THEN 'a'
ELSE 'b'
END
AS `expr`
FROM t
having expr='a'
the following query (the "problem query") returns no row but it should return one. the problem expression is evaluated in the where clause:
SELECT *
FROM t
WHERE
CASE
WHEN CASE WHEN a IS NULL THEN 0 ELSE a = 'a' END
THEN 'a'
ELSE 'b'
END
='a'
I did the following tests:
- the problem query works on innodb and tokudb
- it seems to be linked to the case when case when. remove one level of case when and it works
- it does not seem to affect int of boolean column types
- the server is set up in utf8, as per the instructions here: https://mariadb.com/kb/en/library/mariadb-columnstore-system-usage/ (see the part on configuring utf8 charsets). server variables relating to charset all have utf8