You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@ignite.apache.org by acet <ad...@gmail.com> on 2017/09/18 16:36:59 UTC

Join with subquery in 1.9 or 2.0

Hello,
I was looking to do something similar to:

SELECT a.customerid, h.name, h.address 
FROM 
"customer_cache".CUSTOMER as a
JOIN
(select min(id) as id, name, address, cust_id from "second_cache".DETAILS
group by name, address, cust_id) as h on a.customerid = h.cust_id

This seems to work fine in the debug console but when trying it with ignite
I get:

javax.cache.CacheException: class org.apache.ignite.IgniteException:
org.apache.ignite.internal.processors.query.h2.sql.GridSqlJoin cannot be
cast to org.apache.ignite.internal.processors.query.h2.sql.GridSqlAlias


Is there any way to achieve this?
Thanks



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re:Re: Join with subquery in 1.9 or 2.0

Posted by 贺波 <he...@163.com>.

Hi,
    Your demo is different from my.Mine is an example of recursive,using "with as"function.My test demo is in the attachment.

Re: Join with subquery in 1.9 or 2.0

Posted by Andrey Mashenkov <an...@gmail.com>.

Hi,

I can't reproduce the issue neither on 1.9 nor 2.0 nor 2.1 version.
PFA repro attached.

Would you please check if I've missed smth?


On Wed, Sep 20, 2017 at 1:41 PM, Andrey Mashenkov <
andrey.mashenkov@gmail.com> wrote:

> Hi,
>
> Looks like a bug.
>
> Would you please share a full stacktrace and a reproducer if possible?
>
> You can try to rewrite query without join to smth like this:
>  Select .. from A, B Where A.id = B.id;
>
> On Mon, Sep 18, 2017 at 7:36 PM, acet <ad...@gmail.com> wrote:
>
>> Hello,
>> I was looking to do something similar to:
>>
>> SELECT a.customerid, h.name, h.address
>> FROM
>> "customer_cache".CUSTOMER as a
>> JOIN
>> (select min(id) as id, name, address, cust_id from "second_cache".DETAILS
>> group by name, address, cust_id) as h on a.customerid = h.cust_id
>>
>> This seems to work fine in the debug console but when trying it with
>> ignite
>> I get:
>>
>> javax.cache.CacheException: class org.apache.ignite.IgniteException:
>> org.apache.ignite.internal.processors.query.h2.sql.GridSqlJoin cannot be
>> cast to org.apache.ignite.internal.processors.query.h2.sql.GridSqlAlias
>>
>>
>> Is there any way to achieve this?
>> Thanks
>>
>>
>>
>> --
>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>>
>
>
>
> --
> Best regards,
> Andrey V. Mashenkov
>



-- 
Best regards,
Andrey V. Mashenkov

Re: Join with subquery in 1.9 or 2.0

Posted by Andrey Mashenkov <an...@gmail.com>.

Hi,

Looks like a bug.

Would you please share a full stacktrace and a reproducer if possible?

You can try to rewrite query without join to smth like this:
 Select .. from A, B Where A.id = B.id;

On Mon, Sep 18, 2017 at 7:36 PM, acet <ad...@gmail.com> wrote:

> Hello,
> I was looking to do something similar to:
>
> SELECT a.customerid, h.name, h.address
> FROM
> "customer_cache".CUSTOMER as a
> JOIN
> (select min(id) as id, name, address, cust_id from "second_cache".DETAILS
> group by name, address, cust_id) as h on a.customerid = h.cust_id
>
> This seems to work fine in the debug console but when trying it with ignite
> I get:
>
> javax.cache.CacheException: class org.apache.ignite.IgniteException:
> org.apache.ignite.internal.processors.query.h2.sql.GridSqlJoin cannot be
> cast to org.apache.ignite.internal.processors.query.h2.sql.GridSqlAlias
>
>
> Is there any way to achieve this?
> Thanks
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>



-- 
Best regards,
Andrey V. Mashenkov