[MCOL-5049] OR Criteria in JOINS does not work Created: 2022-04-12  Updated: 2023-12-15

Status: Open
Project: MariaDB ColumnStore
Component/s: Columnstore Select Handler
Affects Version/s: None
Fix Version/s: Icebox

Type: New Feature Priority: Major
Reporter: Faisal Saeed (Inactive) Assignee: Max Mether
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MCOL-5048 Improved ANSI SQL support Open

 Description   

The case is related to Tableau or other ORMs where users have no control over the SQL being generated by the tools.

The example code:

Scenario 1: 

CREATE TABLE t3(id INT) ENGINE=COLUMNSTORE;
 
CREATE TABLE t4(id INT) ENGINE=COLUMNSTORE;
 
 
 
SELECT *
 
FROM t3
 
LEFT JOIN
 
(
 
  SELECT t4.*
 
  FROM t4
 
) t4 ON (t3.id = t4.id OR t3.id IS NULL);
 
 
 
"Internal Error: MCS-1000: 't3' and 'sub-query' are not joined".

Scenario 2:

MariaDB [testdb]> select * from t3 left join t4 on t3.id = t4.id or t3.id is null;
 
ERROR 1815 (HY000): Internal error: MCS-1000: 't3' and 't4' are not joined.
 
 
 
MariaDB [testdb]> select * from t3 join t4 on t3.id = t4.id or t3.id is null;
 
ERROR 1815 (HY000): Internal error: MCS-1000: 't3' and 't4' are not joined.
 
 
 
MariaDB [testdb]> select * from t3 inner join t4 on t3.id = t4.id or t3.id is null;
 
ERROR 1815 (HY000): Internal error: MCS-1000: 't3' and 't4' are not joined.

But if we change the above to use the `AND` operator for the second condition, it works well. With the `OR` condition, these fail. Using InnoDB, there is no such issue, and similarly, if we turn off the SQL Handler, it works as well, which is not really something that is recommended.

Same tables using `AND` condition in the joining criteria. 

MariaDB [testdb]> select * from t3 inner join t4 on t3.id = t4.id and t3.id = 1;
 
Empty set (0.018 sec)
 
 
 
MariaDB [testdb]> select * from t3 inner join t4 on t3.id = t4.id and t3.id is null;
 
Empty set (0.024 sec)

The queries are out of the user's control as these are generated by Tableau and similar tools.


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