[
https://issues.apache.org/jira/browse/TRAFODION-1673?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15364404#comment-15364404
]
liu ming commented on TRAFODION-1673:
-------------------------------------
To fully support WITH, I agree with both Hans and QiFan. Especially the idea of
evaluating the CTE only once. However, we can do this step by step.
The very first step is as Hans' suggest, Trafodion support WITH function in the
parser. The 'Temp View' can be simply be a 'subquery', which is a RenameTable
in the parse tree. As initial testing, it can support recursive reference as
well, but with limitation that the WITH definition must occur before it is
referred , for example:
with w1 as (select * from witht1),
w2 as (select * from w1)
select * from w2;
is good, since w1 is defined before it is used in w2.
But
with w1 as (select * from w2),
w2 as (select * from t1)
select * from w1;
will not work, however, I don't see the second usage in all TPCDS queries. So I
feel it should be good enough to support the first syntax.
This first iteration can implement the functionality, and parser change can be
reused later of a 'common CTE', and test case can be used also.
Later, we can implement a new CTE type of node, which can feed multiple data
consumers, but I feel that is not easy in current architecture.
> Implement the WITH clause in Trafodion SQL for simple use cases
> ---------------------------------------------------------------
>
> Key: TRAFODION-1673
> URL: https://issues.apache.org/jira/browse/TRAFODION-1673
> Project: Apache Trafodion
> Issue Type: New Feature
> Components: sql-cmp
> Reporter: Hans Zeller
> Assignee: liu ming
>
> We keep running into queries that use a WITH clause to define a temporary
> view that can be used once or multiple times in a FROM clause in the query.
> For non-recursive queries, the WITH clause could probably be handled very
> similar to a view. When it is defined, we create an in-memory view
> descriptor, containing the name and the definition. When it is used in a FROM
> clause, we could go through a code path similar to that of a view - bind the
> (temporary) view text and substitute it in the query. The fix could probably
> be handled entirely in the binder.
> This JIRA is *not* about recursive queries, those would require a lot more
> effort, involving many components in addition to the binder.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
