Drill: This query cannot be planned possibly due to either a cartesian join or an inequality join

Question

SELECT *
FROM hc
LEFT OUTER JOIN n
ON hc.JoinHack=n.JoinHack AND (n.`col1` IS NULL OR n.`col1`=hc.`col1`);

I am getting the error as in the question when running the above query in Drill. It seems Drill does not like the combination of an outer join and an OR in the condition.

If I do inner join, the workaround of an equality check on a dummy column is enough to get rid of that error (JoinHack in my case). With outer join it is not enough.

Running the below before the query also does not help:

alter session set `planner.enable_nljoin_for_scalar_only` = false;

What is the solution or a good workaround to this problem?

Answer 1

Key for Drill to accept the query was separating LEFT OUTER JOIN and the condition containing OR. So do LEFT OUTER JOIN with a subquery that does an INNER JOIN with a condition containing OR or however complicated logic you want.

SELECT * 
FROM hc
LEFT OUTER JOIN
(
  SELECT 
  hc.id,
  n.*
  FROM hc
  JOIN n
  ON hc.JoinHack=n.JoinHack 
    AND (n.`col1` IS NULL OR n.`col1`=hc.`col1`)
) m
ON hc.id=m.id;