[MCOL-1472] where does not properly evaluate case when case when on varchar columns Created: 2018-06-14  Updated: 2018-08-07  Resolved: 2018-08-07

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.1.4
Fix Version/s: 1.1.6

Type: Bug Priority: Minor
Reporter: antoine Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

debian9

Issue Links:
Relates
relates to MCOL-1535 Case when~ vs case ~ when ~ Closed
relates to MCOL-1531 ColumnStore fails to make inner join ... Closed
relates to MCOL-1535 Case when~ vs case ~ when ~ Closed
Sprint: 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

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-06-14 ]

Confirmed. This definitely isn't a UTF8 problem, more likely to do with the CASE handling changes in ColumnStore and MariaDB.

Comment by David Hall (Inactive) [ 2018-07-19 ]

As best as I can determine, this only happens if an IS NULL or IS NOT NULL occurs in an inside nested CASE.

When the CASE is in a SELECT, the code calls BuildFunction() directly, which starts with the outside case, which recursively builds the inside case and the stacks play nicely.

When the CASE is in the WHERE clause, it uses traverse_cond(), which walks a tree and starts on the inside and works the leaves, then up the tree. When this happens, the interior CASE is left on rcWorkStack when it gets to the outside CASE. At this point the outside case doesn't trust the tree, so it tries to rebuild its arguments. This shouldn't be a problem, except that IS NULL doesn't have it's argument as a leaf in the tree. When we get to processing the IS NULL, it looks on the rcWorkStack and uses what's there, which is the whole inside case statement from before. Oops.

The kludge is to set a flag just before processing the item of the outside CASE. If the flag is set, IS NULL doesn't use the element in the stack, and all is well.

Other functions and predicates are wise enough to actually have their arguments in the tree, so when prcessed the second time, the stack gets used properly.

Comment by Daniel Lee (Inactive) [ 2018-08-07 ]

Build verified: 1.1.6-1 source

/root/columnstore/mariadb-columnstore-server
commit 513775738f72ec990d055a5d47e2511e3c0e34dd
Merge: 3c37210 9236098
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Wed Jul 18 09:37:17 2018 +0100

Merge pull request #123 from drrtuy/MCOL-970

MCOL-970 Slow query log now contains original query even in vtable mode

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit ee40c3ac050ad7b64302673fc4ab08640f64892f
Merge: 0df1b92 979d00a
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Mon Aug 6 13:02:08 2018 -0500

Merge pull request #523 from mariadb-corporation/MCOL-1579

MCOL-1579 Remove chmod of /dev/shm

MariaDB [mytest]> insert into t(a) values ('a'),('b');
Query OK, 2 rows affected (0.18 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [mytest]> 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'
-> ;
------

expr

------

a

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

MariaDB [mytest]> SELECT *
-> FROM t
-> WHERE
-> CASE
-> WHEN CASE WHEN a IS NULL THEN 0 ELSE a = 'a' END
-> THEN 'a'
-> ELSE 'b'
-> END
-> ='a';
---

a

---

a

---
1 row in set (0.02 sec)

Generated at Thu Feb 08 02:29:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.