Julian Hyde created CALCITE-2712: ------------------------------------ Summary: 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: Julian Hyde
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)