[
https://issues.apache.org/jira/browse/DRILL-8513?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksym Rymar updated DRILL-8513:
--------------------------------
Description:
Drill returns no results on the right Hash Join if the probe(left) table is
empty.
The simplest way to reproduce the issue:
1.To force Drill not to use merge join and use the hash join operator instead:
{code:java}
alter session set planner.enable_mergejoin = false;
alter session set planner.enable_nestedloopjoin= false; {code}
2. Disable join order optimization to prevent Drill from flipping join tables:
{code:java}
alter session set planner.enable_join_optimization = false; {code}
3. Execute a query with empty left table outcome:
{code:java}
SELECT *
FROM
(SELECT * FROM (VALUES (1, 'Max', 28),
(2, 'Jane', 32),
(3, 'Saymon', 29)
) AS users(id, name, age)
WHERE false
) AS users
RIGHT JOIN
(VALUES (1, 'Engineer'),
(2, 'Doctor'),
(3, 'Teacher')
) AS job(id, title)
ON users.id = job.id {code}
Expected result is:
||id||name||age||id0||title||
|null|null|null|1|Engineer|
|null|null|null|2|Doctor|
|null|null|null|3|Teacher|
But we get 0 rows.
was:
Drill returns no results on the right Hash Join if the probe(left) table is
empty.
The simplest way to reproduce the issue:
# To force Drill not to use merge join and use the hash join operator instead:
{code:java}
alter session set planner.enable_mergejoin = false;
alter session set planner.enable_nestedloopjoin= false; {code}
# Disable join order optimization to prevent Drill from flipping join tables:
{code:java}
alter session set planner.enable_join_optimization = false; {code}
# Execute a query with empty left table outcome:
{code:java}
SELECT *
FROM
(SELECT * FROM (VALUES (1, 'Max', 28),
(2, 'Jane', 32),
(3, 'Saymon', 29)
) AS users(id, name, age)
WHERE false
) AS users
RIGHT JOIN
(VALUES (1, 'Engineer'),
(2, 'Doctor'),
(3, 'Teacher')
) AS job(id, title)
ON users.id = job.id {code}
Expected result is:
||id||name||age||id0||title||
|null|null|null|1|Engineer|
|null|null|null|2|Doctor|
|null|null|null|3|Teacher|
But we get 0 rows.
> Right Hash Join with empty Left table ruturns 0 result
> ------------------------------------------------------
>
> Key: DRILL-8513
> URL: https://issues.apache.org/jira/browse/DRILL-8513
> Project: Apache Drill
> Issue Type: Bug
> Reporter: Maksym Rymar
> Assignee: Maksym Rymar
> Priority: Major
>
> Drill returns no results on the right Hash Join if the probe(left) table is
> empty.
> The simplest way to reproduce the issue:
> 1.To force Drill not to use merge join and use the hash join operator instead:
> {code:java}
> alter session set planner.enable_mergejoin = false;
> alter session set planner.enable_nestedloopjoin= false; {code}
> 2. Disable join order optimization to prevent Drill from flipping join tables:
> {code:java}
> alter session set planner.enable_join_optimization = false; {code}
> 3. Execute a query with empty left table outcome:
> {code:java}
> SELECT *
> FROM
> (SELECT * FROM (VALUES (1, 'Max', 28),
> (2, 'Jane', 32),
> (3, 'Saymon', 29)
> ) AS users(id, name, age)
> WHERE false
> ) AS users
> RIGHT JOIN
> (VALUES (1, 'Engineer'),
> (2, 'Doctor'),
> (3, 'Teacher')
> ) AS job(id, title)
> ON users.id = job.id {code}
> Expected result is:
> ||id||name||age||id0||title||
> |null|null|null|1|Engineer|
> |null|null|null|2|Doctor|
> |null|null|null|3|Teacher|
> But we get 0 rows.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
