[
https://issues.apache.org/jira/browse/CALCITE-1440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18028433#comment-18028433
]
TJ Banghart commented on CALCITE-1440:
--------------------------------------
Thank you for taking a look and I apologize for my delayed response!
Focusing on a simple use case makes sense. The case of collapsing two CTEs into
one common one sounds like a great place to start and perhaps something that
should be added to the PR as a proof of concept and will help guide the rest of
this discussion.
There is also a related test case of wrapping two queries with identical scans
in a {{Combine}} and seeing if we can pull it out into a single CTE using
{{Spool}}. I'd imagine this would be it's own rule, say {{CombineCommonScan}}.
It would be very similar to what is described in Stamatis's talk but here these
would be entering the planner as two sub plans with different row types.
{noformat}
SELECT u.first_name, COUNT(o.id)
FROM users u LEFT JOIN orders o ON u.orders_id = o.id
GROUP BY 1;
SELECT u.age, SUM(o.cost)
FROM users u LEFT JOIN orders o ON u.orders_id = o.id
GROUP BY 1;
{noformat}
->
{noformat}
WITH cte as (
SELECT u.first_name as first_name, u.age as age, o.id as id, o.cost as cost
FROM users u LEFT JOIN orders o ON u.orders_id = o.id
)
SELECT cte.first_name, COUNT(cte.id)
FROM cte
GROUP BY 1;
SELECT cte.age, SUM(cte.cost)
FROM cte
GROUP BY 1;
{noformat}
^ In this case would a implementation of {{Combine}} return a two results sets,
one for each distinct query? Might be getting ahead myself here - I'm not sure
if we support temporary tables natively. Perhaps {{Spool}} does this already to
some capacity.
> Implement planner for converting multiple SQL statements to unified RelNode
> Tree
> --------------------------------------------------------------------------------
>
> Key: CALCITE-1440
> URL: https://issues.apache.org/jira/browse/CALCITE-1440
> Project: Calcite
> Issue Type: New Feature
> Reporter: Chinmay Kolhatkar
> Assignee: TJ Banghart
> Priority: Major
> Labels: pull-request-available
>
> This can be implemented as a separate planner or in {{VolcanoPlanner}}
> itself. The planner should take multiple SQL statements as input and return a
> unified {{RelNode}} tree.
> Example of above is as follows:
> {{SELECT COL1, COL2 FROM TABLE WHERE COL3 > 10;}}
> {{SELECT COL1, COL2 FROM TABLE WHERE COL4 = 'abc';}}
> The above 2 statements have a common path and hence can provide a unified
> {{RelNode}} tree as follows:
> {noformat}
> [Scan] -> [Project (COL1, COL2)] -> [Filter (COL4 = 'abc')] -> [Delta]
> |
> V
> [Filter (COL3 > 10)]
> |
> v
> [Delta]
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
