[
https://issues.apache.org/jira/browse/IGNITE-26968?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Orlov updated IGNITE-26968:
--------------------------------------
Fix Version/s: 3.2
> 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
> Affects Versions: 3.1
> Reporter: Pavel Pereslegin
> Assignee: Konstantin Orlov
> Priority: Major
> Labels: ignite-3
> Fix For: 3.2
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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 (in 3-nodes cluster)
> 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
> ||PK = BLOOD_GROUP (incorrect result)||PK = ID (correct result)||
> |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)|
> Reproducer:
> {code:java}
> @Test
> void leftJoin() {
> // setup tables
> sqlScript("CREATE TABLE PUBLIC.PERSON (ID INT, NAME VARCHAR(64), AGE
> INT, CITY_ID DOUBLE, PRIMARY KEY (NAME));"
> + "CREATE TABLE PUBLIC.MEDICAL_INFO (ID INT, NAME
> VARCHAR(64), AGE INT, BLOOD_GROUP VARCHAR(64), PRIMARY KEY (ID));"
> + "CREATE TABLE PUBLIC.BLOOD_GROUP_INFO_P (ID INT,
> BLOOD_GROUP VARCHAR(64), UNIVERSAL_DONOR VARCHAR(64), PRIMARY KEY
> (BLOOD_GROUP));");
> // setup data
> sql("INSERT INTO PUBLIC.PERSON (ID,NAME,AGE,CITY_ID) VALUES "
> + "(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);");
> sql("INSERT INTO PUBLIC.MEDICAL_INFO (id,name,age,blood_group) VALUES
> "
> + "(1,'Madhavi',23,'A+'), "
> + "(2,'Diggi',27,'B+'), "
> + "(3,'Kiran',26,'O+'), "
> + "(4,'Harika',26,'AB+');");
> sql("INSERT INTO PUBLIC.BLOOD_GROUP_INFO_P
> (id,blood_group,universal_donor) VALUES "
> + "(1,'A+','A+AB+'), "
> + "(2,'O+','O+A+B+AB+'), "
> + "(3,'B+','B+AB+'), "
> + "(4,'AB+','AB+'), "
> + "(5,'O-','EveryOne');");
> // Perform JOIN
> List<List<Object>> rows = 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");
> Set<String> sortedRows = new TreeSet<>();
> rows.forEach(row ->
> sortedRows.add(
> row.stream()
> .map(String::valueOf)
> .collect(Collectors.joining(", "))
> )
> );
> IgniteStringBuilder buf = new IgniteStringBuilder();
> sortedRows.forEach(row -> buf.app(row).nl());
> String expOut = "1, Shravya, null, null\n"
> + "2, Kiran, O+, O+A+B+AB+\n"
> + "3, Harika, AB+, AB+\n"
> + "4, Srinivas, null, null\n"
> + "5, Madhavi, A+, A+AB+\n"
> + "6, Deeps, null, null\n"
> + "7, Hope, null, null\n";
> assertEquals(expOut, buf.toString());
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
