The logical algebra is about what the query returns, not how it does it or how long it takes to execute, so expanding CTEs is fine.
Even in SQL, if you define a CTE using the WITH clause there is no guarantee that the engine will materialize the expression as a temporary table. If you want to go from relational algebra directly to SQL, and to have that SQL contain common expressions in a WITH clause, then there is currently no solution in Calcite. If you are prepared to contribute some code, I think the easiest course of action is to develop the Spool operator as described in https://issues.apache.org/jira/browse/CALCITE-481 <https://issues.apache.org/jira/browse/CALCITE-481>, then modify RelToSqlConverter to generate a WITH clause when it sees a Spool. Julian > On Mar 14, 2018, at 6:36 AM, Guohui Xiao <[email protected]> wrote: > > Hi, > > We are experimenting calcite in order to generate SQL out of RA algebra. So > far it works quite well. > > Now we want to construct some relatively complex SQL queries with CTEs > using RelBuilder but we failed. > > We noticed that SQL queries with CTE can be correctly parsed by the > SQLParser; however, after converting the SqlNode instance into a RelNode, > it seems that the definition of CTEs are just expanded. We understood that > this behavior might be due the there is no "spool" operator to generate > CTE-style query plans to reuse RA expressions like the issue in [1]. > > Here, our need is a bit simpler. We only use Calcite to generate SQL > queries out of some algebra. I am wondering if there are some subclasses of > RelNode corresponding to "SqlWith" . > More ideally, we would like to construct such queries from RelBuilder > interface. > > After some study of the code base, if we understood correctly, it seems to > us that such features are not available yet. Could you confirm this and > suggest some alternatives? > > Thanks a lot! > > Best, > > Guohui & Elem > > [1] https://issues.apache.org/jira/browse/CALCITE-481 > > > > -- > Guohui Xiao, PhD > Assistant Professor with a fixed-term contract > KRDB - Faculty of Computer Science > Free University of Bozen-Bolzano > Piazza Domenicani, 3 > I-39100 Bolzano, Italy > > http://www.ghxiao.org
