[
https://issues.apache.org/jira/browse/IGNITE-26968?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pavel Pereslegin updated IGNITE-26968:
--------------------------------------
Description:
The problem was identified after running the test
{{ItJdbcJoinsSelfTest.testJoin()}} on 3 nodes (before that, this test was
performed only on a single node cluster).
We have 3 tables
PERSON
||ID||NAME (PK)||AGE||CITY_ID||
|1| 'Shravya'| 25| 1.1|
|2| 'Kiran'| 26| 1.1|
|3| 'Harika'| 26| 2.4|
|4| 'Srinivas'| 24| 3.2|
|5| 'Madhavi'| 23| 3.2|
|6| 'Deeps'| 28| 1.2|
|7| 'Hope'| 27| 1.2|
MEDICAL_INFO
||id (PK) ||name||age||blood_group
|1|'Madhavi'|23|'A+'|
|2|'Diggi'|27|'B+'|
|3|'Kiran'|26|'O+'|
|4|'Harika'|26|'AB+'|
BLOOD_GROUP_INFO_P
||id||blood_group (PK)||universal_donor||
|1|'A+'|'A+AB+'|
|2|'O+'|'O+A+B+AB+'|
|3|'B+'|'B+AB+'|
|4|'AB+'|'AB+'|
|5|'O-'|'EveryOne'|
The following query:
{code:sql}
SELECT person.id, person.name, medical_info.blood_group,
blood_group_info_P.universal_donor
FROM person
LEFT JOIN medical_info ON medical_info.name = person.name
LEFT JOIN blood_group_info_P ON blood_group_info_P.blood_group =
medical_info.blood_group
{code}
Produces incorrect result
|| expected || actual || ||
|1, Shravya, null, null | 1, Shravya, null, null |
{color:#004400}*match*{color} |
| 2, Kiran, O+, O+A+B+AB+ | 2, Kiran, O+, O+A+B+AB+ |
{color:#004400}*match*{color} |
| 3, Harika, AB+, AB+ | 3, Harika, AB+, null | {color:#440000}*not
match*{color} |
| 4, Srinivas, null, null | 4, Srinivas, null, null |
{color:#004400}*match*{color} |
| 5, Madhavi, A+, A+AB+ | 5, Madhavi, A+, null | {color:#440000}*not
match*{color} |
| 6, Deeps, null, null | 6, Deeps, null, null | {color:#004400}*match*{color} |
| 7, Hope, null, null | 7, Hope, null, null |{color:#004400}*match*{color} |
was:
The problem was identified after running the test
{{ItJdbcJoinsSelfTest.testJoin()}} on 3 nodes (before that, this test was
performed only on a single node cluster).
We have 3 tables
PERSON
||ID||NAME (PK)||AGE||CITY_ID||
|1| 'Shravya'| 25| 1.1|
|2| 'Kiran'| 26| 1.1|
|3| 'Harika'| 26| 2.4|
|4| 'Srinivas'| 24| 3.2|
|5| 'Madhavi'| 23| 3.2|
|6| 'Deeps'| 28| 1.2|
|7| 'Hope'| 27| 1.2|
MEDICAL_INFO
||id (PK) ||name||age||blood_group
|1|'Madhavi'|23|'A+'|
|2|'Diggi'|27|'B+'|
|3|'Kiran'|26|'O+'|
|4|'Harika'|26|'AB+'|
BLOOD_GROUP_INFO_P
||id||blood_group (PK)||universal_donor||
|1|'A+'|'A+AB+'|
|2|'O+'|'O+A+B+AB+'|
|3|'B+'|'B+AB+'|
|4|'AB+'|'AB+'|
|5|'O-'|'EveryOne'|
The following query:
{code:sql}
SELECT person.id, person.name, medical_info.blood_group,
blood_group_info_P.universal_donor
FROM person
LEFT JOIN medical_info ON medical_info.name = person.name
LEFT JOIN blood_group_info_P ON blood_group_info_P.blood_group =
medical_info.blood_group
{code}
Produces incorrect result
|| expected || actual || ||
|1, Shravya, null, null | 1, Shravya, null, null | {color:#004400}match{color} |
| 2, Kiran, O+, O+A+B+AB+ | 2, Kiran, O+, O+A+B+AB+ |
{color:#004400}match{color} |
| 3, Harika, AB+, AB+ | 3, Harika, AB+, null | {color:#440000}not match{color} |
| 4, Srinivas, null, null | 4, Srinivas, null, null |
{color:#004400}match{color} |
| 5, Madhavi, A+, A+AB+ | 5, Madhavi, A+, null | {color:#440000}not
match{color} |
| 6, Deeps, null, null | 6, Deeps, null, null | {color:#004400}match{color} |
| 7, Hope, null, null | 7, Hope, null, null |{color:#004400}match{color} |
> Sql. Left join of 3 tables produces incorrect result
> ----------------------------------------------------
>
> Key: IGNITE-26968
> URL: https://issues.apache.org/jira/browse/IGNITE-26968
> Project: Ignite
> Issue Type: Bug
> Components: sql ai3
> Reporter: Pavel Pereslegin
> Priority: Major
> Labels: ignite-3
>
> The problem was identified after running the test
> {{ItJdbcJoinsSelfTest.testJoin()}} on 3 nodes (before that, this test was
> performed only on a single node cluster).
> We have 3 tables
> PERSON
> ||ID||NAME (PK)||AGE||CITY_ID||
> |1| 'Shravya'| 25| 1.1|
> |2| 'Kiran'| 26| 1.1|
> |3| 'Harika'| 26| 2.4|
> |4| 'Srinivas'| 24| 3.2|
> |5| 'Madhavi'| 23| 3.2|
> |6| 'Deeps'| 28| 1.2|
> |7| 'Hope'| 27| 1.2|
> MEDICAL_INFO
> ||id (PK) ||name||age||blood_group
> |1|'Madhavi'|23|'A+'|
> |2|'Diggi'|27|'B+'|
> |3|'Kiran'|26|'O+'|
> |4|'Harika'|26|'AB+'|
> BLOOD_GROUP_INFO_P
> ||id||blood_group (PK)||universal_donor||
> |1|'A+'|'A+AB+'|
> |2|'O+'|'O+A+B+AB+'|
> |3|'B+'|'B+AB+'|
> |4|'AB+'|'AB+'|
> |5|'O-'|'EveryOne'|
> The following query:
> {code:sql}
> SELECT person.id, person.name, medical_info.blood_group,
> blood_group_info_P.universal_donor
> FROM person
> LEFT JOIN medical_info ON medical_info.name = person.name
> LEFT JOIN blood_group_info_P ON blood_group_info_P.blood_group =
> medical_info.blood_group
> {code}
> Produces incorrect result
> || expected || actual || ||
> |1, Shravya, null, null | 1, Shravya, null, null |
> {color:#004400}*match*{color} |
> | 2, Kiran, O+, O+A+B+AB+ | 2, Kiran, O+, O+A+B+AB+ |
> {color:#004400}*match*{color} |
> | 3, Harika, AB+, AB+ | 3, Harika, AB+, null | {color:#440000}*not
> match*{color} |
> | 4, Srinivas, null, null | 4, Srinivas, null, null |
> {color:#004400}*match*{color} |
> | 5, Madhavi, A+, A+AB+ | 5, Madhavi, A+, null | {color:#440000}*not
> match*{color} |
> | 6, Deeps, null, null | 6, Deeps, null, null | {color:#004400}*match*{color}
> |
> | 7, Hope, null, null | 7, Hope, null, null |{color:#004400}*match*{color} |
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
