Yes, Kylin uses Calcite, and Calcite supports CTEs. In this case, the query has passed parsing and validation and has hit an error during query optimization.
It would be helpful if you log a JIRA case against Kylin with the full error stack attached. It may be a problem in Kylin or may be an underlying issue in Calcite, in which case the Kylin devs can log a JIRA case against Calcite. Julian > On Dec 31, 2015, at 10:17 AM, James Jones <[email protected]> wrote: > > Thanks for the sql reference. > > I haven't worked with calcite directly but it is my understanding that Apache > Drill also uses calcite and does very well with CTEs. > > On Thu, Dec 31, 2015 at 4:09 AM, Li Yang <[email protected] > <mailto:[email protected]>> wrote: > Kylin leverages Calcite for SQL parsing. Guess the CTE support is limited at > the moment. > > https://calcite.apache.org/docs/reference.html > <https://calcite.apache.org/docs/reference.html> > > > On Wed, Dec 30, 2015 at 11:26 PM, James Jones <[email protected] > <mailto:[email protected]>> wrote: > Howdy does kylin support common table expressions "CTE"? > > Also is there a sql reference of what is currently supported? > > Here is an example of what I'm attempting to do: > > With cte (id,groupname) > AS (Select 22150,'merchant1' from fact.flatgeo2 UNION ALL > Select 35010,'merchant1' from fact.flatgeo2) > select distinct id,groupname from cte > > This statement works. > > However when I try to join the CTE to on of the tables in the cube I get this > response: > > null while executing SQL: > > With cte (id,groupname) > AS (Select 22150,'merchant1' from fact.flatgeo2 UNION ALL > Select 35010,'merchant1' from fact.flatgeo2) > select distinct c.id <http://c.id/>,c.groupname from cte c > join fact.spendtripinc spdt on spdt.advertiserid = c.id <http://c.id/> > > when I attempt to try this another way: > > With cte (id,groupname) > AS (Select 22150,'merchant1' from fact.flatgeo2 UNION ALL > Select 35010,'merchant1' from fact.flatgeo2) > select distinct c.id <http://c.id/>,c.groupname from cte c > where c.id <http://c.id/> in (select advertiserid from fact.spendtripinc) > > I get this error: > > Internal error: Error while applying rule SemiJoinRule, args > [rel#25012:OLAPProjectRel.OLAP.[](input=rel#25011:Subset#8.OLAP.[],ID=$0,GROUPNAME=$1), > > rel#25026:OLAPJoinRel.OLAP.[](left=rel#25024:Subset#4.OLAP.[],right=rel#25025:Subset#7.OLAP.[],condition==($2, > $3),joinType=inner), > rel#25023:OLAPProjectRel.OLAP.[](input=rel#25022:Subset#3.OLAP.[],$f0=$0,$f1=$1,$f2=$0), > > rel#25035:OLAPAggregateRel.OLAP.[](input=rel#25034:Subset#6.OLAP.[],group={0})] > >
