Does Ignite support nested joins for partitioned cache?

classic Classic list List threaded Threaded
6 messages Options
DS DS
Reply | Threaded
Open this post in threaded view
|

Does Ignite support nested joins for partitioned cache?

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/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested joins for partitioned cache?

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 <[hidden email]>:
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/
DS DS
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested joins for partitioned cache?

Hi,
Thankyou for replying.

*cache configration for all 4 tables :
* SQL_PUBLIC_PERSON.SQL_PUBLIC_PERSON
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/SQL_PUBLIC_PERSON.SQL_PUBLIC_PERSON>  
SQL_PUBLIC_CITY.SQL_PUBLIC_CITY
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/SQL_PUBLIC_CITY.SQL_PUBLIC_CITY>  
SQL_PUBLIC_MEDICAL_INFO.SQL_PUBLIC_MEDICAL_INFO
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/SQL_PUBLIC_MEDICAL_INFO.SQL_PUBLIC_MEDICAL_INFO>  
SQL_PUBLIC_BLOOD_GROUP_INFO.SQL_PUBLIC_BLOOD_GROUP_INFO
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/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
*
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/person_table.png>

*Table :city
*
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/city_table.png>

*Table :medical_info
*
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/medical_info_table.png>

*Table: blood_group_info
*
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/blood_group_info_table.png>

*Result of join query :
*
<http://apache-ignite-users.70518.x6.nabble.com/file/t2210/join_result.png>






--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
DS DS
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested joins for partitioned cache?

Hi,any update on the issue ?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
DS DS
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested joins for partitioned cache?

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/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Does Ignite support nested joins for partitioned cache?

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 <[hidden email]>:
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/