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]> wrote: > 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})] >> > >
