osdir.com

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


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)