[CALCITE-435] LoptOptimizeJoinRule incorrectly re-orders outer joins

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.9.0-incubating
• Fix Version/s: 0.9.2-incubating
• Assignee: Julian Hyde
• Reporter: Laljo John Pullokkaran
• Created: 07/Oct/14 22:31
• Updated: 02/Sep/15 01:03
• Resolved: 18/Oct/14 23:43

Description

Following query reproduces the problem:
SELECT ss_item_sk, ss_ticket_number, ss_customer_sk, sr_return_quantity FROM store_sales LEFT OUTER JOIN store_returns ON (store_returns.sr_item_sk = store_sales.ss_item_sk AND store_returns.sr_ticket_number = store_sales.ss_ticket_number), reason WHERE store_returns.sr_reason_sk = reason.r_reason_sk AND r_reason_desc = 'reason 28';

The Join graph given is:
(SS --> SR) – Reason gets transformed in to
(SS x Reason) --> SR

This is an invalid reordering.

A short term solution may be to split join graph on outer join.