kylin-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Jones <james...@gmail.com>
Subject Re: Usage of CTE with in queries?
Date Thu, 31 Dec 2015 18:17:45 GMT
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 <liyang@apache.org> 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 <james.aj@gmail.com> 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})]
>>
>
>

Mime
View raw message