CODEWITHSUNDEEP
sqlouter-joinapache-drill
Tom
Tom

Reputation: 149

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

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?

Upvotes: 0

Views: 1586

Answers (4)

Tom
Tom

Reputation: 149

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

This is very tricky logic. I think it is usually equivalent to:

SELECT *
FROM hc INNER JOIN
     n
     ON hc.JoinHack = n.JoinHack AND n.`col1` IS NULL
UNION ALL
SELECT *
FROM hc INNER JOIN
     n
     ON hc.JoinHack = n.JoinHack AND n.`col1` = hc.`col1`
UNION ALL
SELECT *
FROM hc LEFT JOIN
     n
     ON hc.JoinHack = n.JoinHack AND n.`col1` = hc.`col1`
WHERE n.JoinHack IS NULL;

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Use a derived table.

select * from 
(
SELECT *
FROM hc
LEFT OUTER JOIN n
ON hc.JoinHack=n.JoinHack
) derivedTable
where n.`col1` IS NULL OR n.`col1`=hc.`col1`

This is the general idea. The query as is will throw errors because n and hc don't exist outside the subquery. You can sort that out on your own.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175826

You could try COALESCE:

SELECT *
FROM hc
LEFT OUTER JOIN n
  ON hc.JoinHack=n.JoinHack 
  AND COALESCE(n.`col1`,hc.`col1`)=hc.`col1`;

alternatively using UNION:

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

Upvotes: 2

Related Questions