[MCOL-1196] Error when using OR in case THEN portion Created: 2018-02-02  Updated: 2018-03-29  Resolved: 2018-03-29

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 1.1.2
Fix Version/s: 1.1.4

Type: Bug Priority: Major
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MCOL-1234 Nested CASE filters not processed Closed
Sprint: 2018-06, 2018-07

 Description   

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');

innodb:
SELECT astr FROM test_inno WHERE CASE WHEN 1 = 1 THEN (astr = 'test' OR astr = 'unknown') END;
------

astr

------

test

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

columnstore:
SELECT astr FROM test_mcs WHERE CASE WHEN 1 = 1 THEN (astr = 'test' OR astr = 'unknown') END;
ERROR 1178 (42000): The storage engine for the table doesn't support Unknown item type



 Comments   
Comment by David Hall (Inactive) [ 2018-03-27 ]

The default in buildReturnedColumn() in the big switch for Item type is to throw this error. By putting in an empty case for COND_ITEM, we can remove the error.

To make it actually work, we need to reverse the order of parameter processing for WHERE clause case_searched so that the gwi.ptWorkStack.pop() statements are popping the same objects we're working on in the same order. Some Item types may use the stack and the front to back ordering caused the stack to get all out of whack. We were processing the case arguments first to last, but popping the stack, which had the result of working last to first. They quickly became out of sync.

On the execution side, LogicOperator::getBoolVal() wants the left and right side objects passed in. These were not passed in, so the base class's getBoolVal() was called. The objects are available in Func_searched_case, so they are now passed in if they exist.

Comment by David Hall (Inactive) [ 2018-03-27 ]

The syncing of argument processing and the ptWorkStack has the happy side affect of fixing MCOL-1234
Nested CASE filters not processed

Comment by David Hall (Inactive) [ 2018-03-27 ]

Check MCOL-1234 with this patch. It worked for me.

Comment by Andrew Hutchings (Inactive) [ 2018-03-28 ]

Unfortunately the more complex version of MCOL-1234 still remains with this fix so leaving that one open

Comment by Daniel Lee (Inactive) [ 2018-03-29 ]

Build verified: 1.1.4-1 source

Reproduced the issue in 1.1.3-1 and verified fixed.
[root@localhost mariadb-columnstore-server]# git show
commit 3b5242143b394423dbdf96d888410a3c33f9ff97
Merge: b7d93b7 23900e6
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Wed Mar 7 10:39:40 2018 -0600

Merge pull request #104 from mariadb-corporation/davidhilldallas-patch-3

update version

[root@localhost mariadb-columnstore-engine]# git show
commit 25661c08512e8527e448b172853c5d23bfe423a4
Merge: acfddce c838966
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Wed Mar 28 09:45:58 2018 +0100

Merge pull request #432 from mariadb-corporation/MCOL-1196

Mcol 1196

[root@localhost ~]# mcsmysql mytest
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.2.13-MariaDB-log Columnstore 1.1.4-1

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

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

MariaDB [mytest]> create table test_inno (astr VARCHAR(50))ENGINE = INNODB ;
Query OK, 0 rows affected (0.02 sec)

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

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

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

MariaDB [mytest]> SELECT astr FROM test_inno WHERE CASE WHEN 1 = 1 THEN (astr = 'test' OR astr = 'unknown') END;
------

astr

------

test

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

MariaDB [mytest]> SELECT astr FROM test_mcs WHERE CASE WHEN 1 = 1 THEN (astr = 'test' OR astr = 'unknown') END;
------

astr

------

test

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

MariaDB [mytest]>

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