[
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}|
If you change the PRIMARY KEY of the BLOOD_GROUP_INFO_P table and use the ID
column instead of BLOOD_GROUP, the query works correctly.
Plans comparison
||Heading 1||Heading 2||
|Exchange
distribution: single
est: (rows=1)
Project
fieldNames: [ID, NAME, BLOOD_GROUP, UNIVERSAL_DONOR]
projection: [ID, NAME, BLOOD_GROUP, UNIVERSAL_DONOR]
est: (rows=1)
HashJoin
predicate: =(BLOOD_GROUP$0, BLOOD_GROUP)
fieldNames: [ID, NAME, NAME0, BLOOD_GROUP, BLOOD_GROUP$0,
UNIVERSAL_DONOR]
type: left
est: (rows=1)
Project
fieldNames: [ID, NAME, NAME0, BLOOD_GROUP]
projection: [ID, NAME$0, NAME, BLOOD_GROUP]
est: (rows=1)
HashJoin
predicate: =(NAME, NAME$0)
fieldNames: [NAME, BLOOD_GROUP, ID, NAME$0]
type: right
est: (rows=1)
Exchange
distribution: table PUBLIC.PERSON in zone "Default" by [NAME]
est: (rows=1)
TableScan
table: PUBLIC.MEDICAL_INFO
fieldNames: [NAME, BLOOD_GROUP]
est: (rows=1)
TableScan
table: PUBLIC.PERSON
fieldNames: [ID, NAME]
est: (rows=1)
TableScan
table: PUBLIC.BLOOD_GROUP_INFO_P
fieldNames: [BLOOD_GROUP, UNIVERSAL_DONOR]
est: (rows=1)|Project
fieldNames: [ID, NAME, BLOOD_GROUP, UNIVERSAL_DONOR]
projection: [ID, NAME, BLOOD_GROUP, UNIVERSAL_DONOR]
est: (rows=1)
HashJoin
predicate: =(BLOOD_GROUP$0, BLOOD_GROUP)
fieldNames: [ID, NAME, NAME$0, BLOOD_GROUP, BLOOD_GROUP$0,
UNIVERSAL_DONOR]
type: left
est: (rows=1)
HashJoin
predicate: =(NAME$0, NAME)
fieldNames: [ID, NAME, NAME$0, BLOOD_GROUP]
type: left
est: (rows=1)
Exchange
distribution: single
est: (rows=1)
TableScan
table: PUBLIC.PERSON
fieldNames: [ID, NAME]
est: (rows=1)
Exchange
distribution: single
est: (rows=1)
TableScan
table: PUBLIC.MEDICAL_INFO
fieldNames: [NAME, BLOOD_GROUP]
est: (rows=1)
Exchange
distribution: single
est: (rows=1)
TableScan
table: PUBLIC.BLOOD_GROUP_INFO_P
fieldNames: [BLOOD_GROUP, UNIVERSAL_DONOR]
est: (rows=1)|
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} |
If you change the PRIMARY KEY of the BLOOD_GROUP_INFO_P table and use the ID
column instead of BLOOD_GROUP, the query works correctly.
Plans comparison
|| PK = BLOOD_GROUP (incorrect result) || PK = ID (correct result) ||
> 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}|
> If you change the PRIMARY KEY of the BLOOD_GROUP_INFO_P table and use the ID
> column instead of BLOOD_GROUP, the query works correctly.
> Plans comparison
> ||Heading 1||Heading 2||
> |Exchange
> distribution: single
> est: (rows=1)
> Project
> fieldNames: [ID, NAME, BLOOD_GROUP, UNIVERSAL_DONOR]
> projection: [ID, NAME, BLOOD_GROUP, UNIVERSAL_DONOR]
> est: (rows=1)
> HashJoin
> predicate: =(BLOOD_GROUP$0, BLOOD_GROUP)
> fieldNames: [ID, NAME, NAME0, BLOOD_GROUP, BLOOD_GROUP$0,
> UNIVERSAL_DONOR]
> type: left
> est: (rows=1)
> Project
> fieldNames: [ID, NAME, NAME0, BLOOD_GROUP]
> projection: [ID, NAME$0, NAME, BLOOD_GROUP]
> est: (rows=1)
> HashJoin
> predicate: =(NAME, NAME$0)
> fieldNames: [NAME, BLOOD_GROUP, ID, NAME$0]
> type: right
> est: (rows=1)
> Exchange
> distribution: table PUBLIC.PERSON in zone "Default" by [NAME]
> est: (rows=1)
> TableScan
> table: PUBLIC.MEDICAL_INFO
> fieldNames: [NAME, BLOOD_GROUP]
> est: (rows=1)
> TableScan
> table: PUBLIC.PERSON
> fieldNames: [ID, NAME]
> est: (rows=1)
> TableScan
> table: PUBLIC.BLOOD_GROUP_INFO_P
> fieldNames: [BLOOD_GROUP, UNIVERSAL_DONOR]
> est: (rows=1)|Project
> fieldNames: [ID, NAME, BLOOD_GROUP, UNIVERSAL_DONOR]
> projection: [ID, NAME, BLOOD_GROUP, UNIVERSAL_DONOR]
> est: (rows=1)
> HashJoin
> predicate: =(BLOOD_GROUP$0, BLOOD_GROUP)
> fieldNames: [ID, NAME, NAME$0, BLOOD_GROUP, BLOOD_GROUP$0,
> UNIVERSAL_DONOR]
> type: left
> est: (rows=1)
> HashJoin
> predicate: =(NAME$0, NAME)
> fieldNames: [ID, NAME, NAME$0, BLOOD_GROUP]
> type: left
> est: (rows=1)
> Exchange
> distribution: single
> est: (rows=1)
> TableScan
> table: PUBLIC.PERSON
> fieldNames: [ID, NAME]
> est: (rows=1)
> Exchange
> distribution: single
> est: (rows=1)
> TableScan
> table: PUBLIC.MEDICAL_INFO
> fieldNames: [NAME, BLOOD_GROUP]
> est: (rows=1)
> Exchange
> distribution: single
> est: (rows=1)
> TableScan
> table: PUBLIC.BLOOD_GROUP_INFO_P
> fieldNames: [BLOOD_GROUP, UNIVERSAL_DONOR]
> est: (rows=1)|
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
