[
https://issues.apache.org/jira/browse/CALCITE-2712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Chunwei Lei resolved CALCITE-2712.
----------------------------------
Resolution: Fixed
Fix Version/s: 1.20.0
> 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
> Fix For: 1.20.0
>
> Time Spent: 4h
> 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)
