[jira] [Comment Edited] (CALCITE-2712) Add rule to remove null-generating side of a Join

["Steven Talbot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16816646#comment-16816646 ] 

Steven Talbot edited comment on CALCITE-2712 at 4/12/19 8:57 PM:
-----------------------------------------------------------------

Do we not also need a `ProjectJoinEliminationRule` for the below simple case?
{code:java}

 SELECT li.idFROM LineItems AS liLEFT JOIN Orders AS o ON o.id = i.orderId{code}

 Apologies if I totally missed something though.

 

If the above is needed, I also wonder if the code could be simpler if it just had `AggregateJoinEliminationRule` and `ProjectJoinEliminationRule`, and those just walked the join tree and eliminated all joins that could be eliminated, which could take 4 rules down to 2. As a consumer trying to use these rules, it might also be simpler to understand to not have to specify all four. But could also understand the objection to this approach as complicating the implementation of this rules.


was (Author: swtalbot):
Do we not also need a `ProjectJoinEliminationRule` for the below simple case?
SELECT {color:#910091}li{color}.idFROM LineItems AS liLEFT JOIN Orders AS o ON o.id = i.orderId
Apologies if I totally missed something though.

 

If the above is needed, I also wonder if the code could be simpler if it just had `AggregateJoinEliminationRule` and `ProjectJoinEliminationRule`, and those just walked the join tree and eliminated all joins that could be eliminated, which could take 4 rules down to 2. As a consumer trying to use these rules, it might also be simpler to understand to not have to specify all four. But could also understand the objection to this approach as complicating the implementation of this rules.

> Add rule to remove null-generating side of a Join
> -------------------------------------------------
>
>                 Key: CALCITE-2712
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2712
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Assignee: Chunwei Lei
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> Add a rule to remove the null-generating side of a Join. Here is an example  where we eliminate the "many" side of a "one-to-many" join:
> {code:sql}
> # Example 1: one-to-many
> SELECT c.id, COUNT(DISTINCT o.productId)
> FROM Customers AS c
> LEFT JOIN SupportCalls AS s ON c.id = s.customerId
> LEFT JOIN Orders AS o ON c.id = o.customerId{code}
> We can remove {{SupportCalls}} and the join to it, so the query becomes
> {code:sql}
> SELECT c.id, COUNT(DISTINCT o.productId)
> FROM Customers AS c
> LEFT JOIN Orders AS o ON c.id = o.customerId{code}
> Necessary conditions are:
> # no columns from {{SupportCalls}} are used
> # the join is LEFT, so customers will not be eliminated if they have no support calls,
> # there is an Aggregate on top, so we don't care if there are >1 support call per customer.
> A simpler example of one-to-many:
> {code:sql}
> # Example 2: simpler one-to-many
> SELECT DISTINCT c.id
> FROM Customers AS c
> LEFT JOIN SupportCalls AS s ON c.id = s.customerId{code}
> An example of many-to-one, where we eliminate the "one" side ({{Orders}}):
> {code:sql}
> # Example 3: many-to-one
> SELECT c.id, p.color
> FROM LineItems AS i
> LEFT JOIN Orders AS o ON o.id = i.orderId
> LEFT JOIN Products AS p ON p.id = i.orderId{code}
> so that the query becomes
> {code:sql}
> SELECT c.id, p.color
> FROM LineItems AS i
> LEFT JOIN Products AS p ON p.id = i.orderId{code}
> Here, necessary side-conditions are:
> # no columns from {{Orders}} are used;
> # unique key on {{Orders.id}}.
> We do not require aggregation, because the primary key on {{Orders.id}} ensures that {{Orders}} contributes at most one row.
> We can deal with similar cases like
> {code:sql}
> # Example 4: many-to-one, column aliasing required
> SELECT c.id, p.color
> FROM LineItems AS i
> LEFT JOIN Orders AS o ON o.id = i.orderId
> LEFT JOIN Products AS p ON p.id = o.id{code}
> if we use aliasing ({{o.id}} = {{i.orderId}}) and a foreign key that ensures the existence of an record in {{Orders}}.
> For examples 1 and 2 (one-to-many), we would need to match {{Aggregate}} on {{Join}}, therefore make a variant of {{AggregateJoinTransposeRule}}.
> For examples 3 and 4 (many-to-one or one-to-one)), we would match {{Project}} on {{Join}}, therefore make a variant of {{ProjectJoinTransposeRule}}.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)