[MCOL-1531] ColumnStore fails to make inner join if using CASE in predicate. Created: 2018-07-05  Updated: 2020-08-25  Resolved: 2018-08-07

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 1.1.4, 1.1.5
Fix Version/s: 1.1.6

Type: Bug Priority: Major
Reporter: Roman Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Problem/Incident
is caused by MCOL-1234 Nested CASE filters not processed Closed
Relates
relates to MCOL-1472 where does not properly evaluate case... Closed
relates to MCOL-1535 Case when~ vs case ~ when ~ Closed
relates to MCOL-2147 Semi-join with correllated subquery c... Closed
Sprint: 2018-14, 2018-15, 2018-16

 Description   

The feature worked back in 1.3 and git bisect points to aa581b5dc3d88b4d0d07a224de4da5c96cf23aa3 (MCOL-1234)

Here are the steps to reproduce the issue:

MariaDB [test]> describe cs1
 
    -> ;
 
+-------+------------+------+-----+---------+-------+
 
| Field | Type       | Null | Key | Default | Extra |
 
+-------+------------+------+-----+---------+-------+
 
| t     | varchar(2) | YES  |     | NULL    |       |
 
| i     | int(11)    | YES  |     | NULL    |       |
 
+-------+------------+------+-----+---------+-------+
 
2 rows in set (0.01 sec)
 
 
 
MariaDB [test]> describe cs2
 
    -> ;
 
+-------+------------+------+-----+---------+-------+
 
| Field | Type       | Null | Key | Default | Extra |
 
+-------+------------+------+-----+---------+-------+
 
| t     | varchar(2) | YES  |     | NULL    |       |
 
| i     | int(11)    | YES  |     | NULL    |       |
 
+-------+------------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select * from cs1 inner join cs2 on (cs1.t = case cs2.t when NULL then 'MO' else cs2.t end);
 
ERROR 1815 (HY000): Internal error: IDB-1000: 'cs1' and 'cs2' are not joined.

WIth the commit previous to the above mentioned CS process the query just fine.

MariaDB [test]> select * from cs1 inner join cs2 on (cs1.t = case cs2.t when NULL then 'MO' else cs2.t end);
 
+------+------+------+------+
 
| t    | i    | t    | i    |
 
+------+------+------+------+
 
| SA   | NULL | SA   | NULL |
 
+------+------+------+------+
 
1 row in set (0.09 sec)



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

MCOL-1234 redid some of the CASE statement logic. It now depends on the equivalence (==) operator of parsetree derived objects. However, during processing, it's probable that objects that we are comparing don't have the alias field set. Not all parsetree derived objects had the compare of alias commented out as they should have been.

Comment by Ben Thompson (Inactive) [ 2018-07-24 ]

Reviewed / Merged

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

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