Also, a CTE is internally re-written to algebra that is the same as for a query in the FROM clause. Try converting your query
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 to select distinct c.id,c.groupname from ( Select 22150 as id,'merchant1’ as groupname from fact.flatgeo2 UNION ALL Select 35010,'merchant1' from fact.flatgeo2) as c join fact.spendtripinc spdt on spdt.advertiserid = c.id and see whether you get the same error. If you get the same error it means the problem is not due to CTE. Julian > On Dec 31, 2015, at 8:44 PM, Julian Hyde <[email protected]> wrote: > > 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] >> <mailto:[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})] >> >> >
