Kylin leverages Calcite for SQL parsing. Guess the CTE support is limited at the moment.
https://calcite.apache.org/docs/reference.html On Wed, Dec 30, 2015 at 11:26 PM, James Jones <[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,c.groupname from cte c > join fact.spendtripinc spdt on spdt.advertiserid = 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,c.groupname from cte c > where 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})] >
