[IGNITE-14040] NATURAL JOIN not implemented correctly

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.9.1
• Component/s: sql
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Lukas Eder
• Created: 23/Jan/21 09:50
• Updated: 23/Jan/21 09:50

Description

Try this:

 

CREATE TABLE t1 (i int PRIMARY KEY, j int);
CREATE TABLE t2 (i int PRIMARY KEY, k int);
INSERT INTO t1 VALUES (1, 1), (2, 2);
INSERT INTO t2 VALUES (2, 2), (3, 3);
SELECT * FROM t1 NATURAL JOIN t2;

 

It doesn't yield any results, when we should be getting:

|I |J |K |
|--|--|--|
|2 |2 |2 |

The explain plan on the SELECT seems to hint at keys and values being compared

SELECT
 __Z0.I AS __C0_0,
 __Z0.J AS __C0_1,
 __Z1.K AS __C0_2
FROM PUBLIC.T1 __Z0
 /* PUBLIC.T1.__SCAN_ */
INNER JOIN PUBLIC.T2 __Z1
 /* PUBLIC."_key_PK": _KEY = PUBLIC.__Z0._KEY */
 ON 1=1
WHERE (PUBLIC.__Z0.I = PUBLIC.__Z1.I)
 AND ((PUBLIC.__Z0._KEY = PUBLIC.__Z1._KEY)
 AND (PUBLIC.__Z0._VAL = PUBLIC.__Z1._VAL))