You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by Julian Hyde <jh...@apache.org> on 2016/01/01 05:44:12 UTC
Re: Usage of CTE with in queries?
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 <ja...@gmail.com> 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 <liyang@apache.org <ma...@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 <https://calcite.apache.org/docs/reference.html>
>
>
> On Wed, Dec 30, 2015 at 11:26 PM, James Jones <james.aj@gmail.com <ma...@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 <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})]
>
>
Re: Usage of CTE with in queries?
Posted by Luke Han <lu...@gmail.com>.
Here are some points from me:
1. your sub query do not have any "group by" which actually will not
perform correct result since Kylin do not support query on raw data yet
(there's one JIRA to support in next few releases)
2. Union All is not fully test yet, I don't think this will work so far.
3. The purpose of your query looks like want to create a temp table or
memory dataset for further usage, may I ask what's the exactly requirement
for the query so that we may have more idea, like sub query or others.
And, please log JIRA for any issue.
Thanks.
Best Regards!
---------------------
Luke Han
On Fri, Jan 1, 2016 at 2:30 PM, Julian Hyde <jh...@apache.org> wrote:
> 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 <jh...@apache.org> 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 <ja...@gmail.com> 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 <li...@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 <ja...@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})]
>>>
>>
>>
>
>
>
Re: Usage of CTE with in queries?
Posted by Julian Hyde <jh...@apache.org>.
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 <jh...@apache.org> 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 <james.aj@gmail.com <ma...@gmail.com>> 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 <liyang@apache.org <ma...@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 <https://calcite.apache.org/docs/reference.html>
>>
>>
>> On Wed, Dec 30, 2015 at 11:26 PM, James Jones <james.aj@gmail.com <ma...@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 <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})]
>>
>>
>