[DRILL-8513] Right Hash Join with empty Left table ruturns 0 result

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.22.0
• Assignee: Maksym Rymar
• Reporter: Maksym Rymar
• Created: 14/Oct/24 12:25
• Updated: 21/Oct/24 09:24
• Resolved: 21/Oct/24 09:24

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:

alter session set planner.enable_mergejoin = false;
alter session set planner.enable_nestedloopjoin= false; 

2. Disable join order optimization to prevent Drill from flipping join tables:

alter session set planner.enable_join_optimization = false;  

3. Execute a query with empty left table outcome:

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 

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.

Issue Links

• is caused by: DRILL-4232 Support for EXCEPT set operator