[IGNITE-13961] Hierarchical table join causes "Failed to generate REDUCE query. Data table found"

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.8.1, 2.9.1
• Component/s: sql
• Ignite Flags: Release Notes Required
• Assignee: Unassigned
• Reporter: Ilya Kasnacheev
• Created: 07/Jan/21 15:45
• Updated: 07/Jan/21 15:47

Description

create table tasks (id int primary key, parent_id int);
insert into tasks (id, parent_id) values (1, 1);
insert into tasks (id, parent_id) values (2, 1);
insert into tasks (id, parent_id) values (5, 3);
insert into tasks (id, parent_id) values (7, null);
SELECT * FROM (SELECT DISTINCT id, parent_id FROM tasks) AS orphan WHERE orphan.parent_id NOT IN (SELECT id FROM tasks) or orphan.parent_id is null;

Expected: (5, 3), (7, null)

Actual:

Error: Failed to generate REDUCE query. Data table found: PUBLIC.TASKS 
SELECT
ORPHAN__Z1.ID,
ORPHAN__Z1.PARENT_ID
FROM (SELECT DISTINCT
__C0_0 ID,
__C0_1 PARENT_ID
FROM PUBLIC.__T0) ORPHAN__Z1
WHERE (ORPHAN__Z1.PARENT_ID IS NULL) OR (NOT (ORPHAN__Z1.PARENT_ID IN( SELECT
__Z2.ID
FROM PUBLIC.TASKS __Z2 ))) (state=50000,code=1)

Issue Links

• is related to: IGNITE-8720 SQL: Failed to generate REDUCE query
• is related to: IGNITE-8822 SQL: Failed to run map query remotely.
• links to: Userlist thread