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