[DRILL-4966] null equality join results in UNSUPPORTED_OPERATION ERROR

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.9.0
• Component/s: Execution - Flow
• Assignee: Unassigned
• Reporter: Khurram Faraaz
• Created: 26/Oct/16 11:23
• Updated: 26/Oct/16 11:23

Description

Below join query (HashJoin) executes. (note that the join columns are not checked if they are null).

git commit id : a29f1e29
Drill 1.9.0

select t1.c1, t2.c2 from `t_alltype.parquet` t1, `t_alltype.parquet` t2 WHERE t1.c1 = t2.c1 AND t1.c2 = t2.c2;
+------+--------------+
|  c1  |      c2      |
+------+--------------+
| 1    | 592475043    |
| 2    | 1194343172   |
...
...
142 rows selected (0.462 seconds)

checking if the join columns are null results in UNSUPPORTED_OPERATION ERROR.

0: jdbc:drill:schema=dfs.tmp> select t1.c1, t2.c2 from `t_alltype.parquet` t1, `t_alltype.parquet` t2 WHERE t1.c1 = t2.c1 AND t1.c2 = t2.c2 OR (t1.c1 IS NULL AND t2.c1 IS NULL AND t2.c2 IS NULL AND t1.c2 IS NULL);
Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join


[Error Id: f9df328d-28d8-46f4-86f5-e67d60e20589 on centos-01.qa.lab:31010] (state=,code=0)

0: jdbc:drill:schema=dfs.tmp> select t1.c1, t2.c2 from `t_alltype.parquet` t1, `t_alltype.parquet` t2 WHERE t1.c1 = t2.c1 AND t1.c2 = t2.c2 OR (t1.c1 IS NULL AND t2.c1 IS NULL);
Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join


[Error Id: df76fce9-88d4-49bf-b487-7d897667755a on centos-01.qa.lab:31010] (state=,code=0)

0: jdbc:drill:schema=dfs.tmp> select t1.c1, t2.c2 from `t_alltype.parquet` t1, `t_alltype.parquet` t2 WHERE t1.c1 = t2.c1 AND t1.c2 = t2.c2 OR (t1.c2 IS NULL AND t2.c2 IS NULL);
Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join


[Error Id: 25064c21-4197-4cf6-a1b8-6edfc2788fc9 on centos-01.qa.lab:31010] (state=,code=0)