Details
-
Sub-Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
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.