Re: Does Ignite support nested joins for partitioned cache?

[Ilya Kasnacheev]

Hello!

Can you please throw together a reproducer project? One which runs some
nodes, populates some data in tables and then shows the wrong results.

Please make it runnable if you want quick response, with Maven, etc.

Regards,
-- 
Ilya Kasnacheev


пн, 9 дек. 2019 г. в 09:38, DS :

> Hi,Its really important for me to undersatnd whats going wrong here with
> joins.
> Its kind of big blocker for me.
>
> I am not getting the correct data when joining more than 3 tables (picture
> 4
> in above attachments ).
> Any help will be much appericiated.
>
> Thanx
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Does Ignite support nested joins for partitioned cache?

[DS]

Hi,Its really important for me to undersatnd whats going wrong here with
joins. 
Its kind of big blocker for me. 

I am not getting the correct data when joining more than 3 tables (picture 4
in above attachments ). 
Any help will be much appericiated. 

Thanx



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

Re: Does Ignite support nested joins for partitioned cache?

[DS]

Hi,any update on the issue ?



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

Re: Does Ignite support nested joins for partitioned cache?

[DS]

Hi, 
Thankyou for replying.

*cache configration for all 4 tables :
* SQL_PUBLIC_PERSON.SQL_PUBLIC_PERSON

  
SQL_PUBLIC_CITY.SQL_PUBLIC_CITY

  
SQL_PUBLIC_MEDICAL_INFO.SQL_PUBLIC_MEDICAL_INFO

  
SQL_PUBLIC_BLOOD_GROUP_INFO.SQL_PUBLIC_BLOOD_GROUP_INFO

  


*Expecting result :
*I am expecting 7 rows but only getting 4 .
Also,data for column blood_group and universal_donor is missing.

*Below are the screenshots and cache_configration of all 4 tables and the
query result .
*
*Table :person
*
 

*Table :city 
*
 

*Table :medical_info
*

 

*Table: blood_group_info
*

 

*Result of join query : 
*
 






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

Re: Does Ignite support nested joins for partitioned cache?

[Ilya Kasnacheev]

Hello!

Ignite supports nested queries, but only as map queries (there is just one
reduce query).

What does this mean for your query I know not. Can you show cache
configurations for your queries, some data and expected/actual result for
that data? Maybe you are getting an error of some kind?

Regards,
-- 
Ilya Kasnacheev


пн, 2 дек. 2019 г. в 13:13, DS :

> I have been trying to implement nested joins with or without subquery  but
> unable get right results.
> My caches are partitioned ,so enabling Allow non-collocated joins check.
>
> P.S.
> It has been observed that if i am tyring to join 3 tables,i get expected
> result but for more than 3 tables worries me.
>
> Please let me know if i am doing something wrong with query below:
>
> SELECT
> _medical_info.age,_medical_info.name,_medical_info.city_id,_medical_info.weight,_medical_info.blood_group,blood_group_info.universal_donor
>
> FROM
>( SELECT
> _person.age,_person.name,_person.city_id,medical_info.blood_group,medical_info.weight
>
> FROM (
> SELECT person.age,person.name,city.city_id
> FROM person
> LEFT JOIN  city ON person.city_id = city.city_id
> ) AS _person
> LEFT JOIN  medical_info ON _person.name = medical_info.name
> ) AS _medical_info
> LEFT JOIN  blood_group_info ON _medical_info.blood_group =
> blood_group_info.blood_group
>
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Does Ignite support nested joins for partitioned cache?

[DS]

I have been trying to implement nested joins with or without subquery  but
unable get right results.
My caches are partitioned ,so enabling Allow non-collocated joins check.

P.S.
It has been observed that if i am tyring to join 3 tables,i get expected
result but for more than 3 tables worries me.

Please let me know if i am doing something wrong with query below:

SELECT
_medical_info.age,_medical_info.name,_medical_info.city_id,_medical_info.weight,_medical_info.blood_group,blood_group_info.universal_donor
 
FROM 
   ( SELECT
_person.age,_person.name,_person.city_id,medical_info.blood_group,medical_info.weight
 
FROM (
SELECT person.age,person.name,city.city_id 
FROM person 
LEFT JOIN  city ON person.city_id = city.city_id
) AS _person 
LEFT JOIN  medical_info ON _person.name = medical_info.name 
) AS _medical_info  
LEFT JOIN  blood_group_info ON _medical_info.blood_group =
blood_group_info.blood_group 


 



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