[ https://issues.apache.org/jira/browse/IGNITE-16694?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17524197#comment-17524197 ]
Yury Gerzhedovich commented on IGNITE-16694: -------------------------------------------- [~liwen.cui] , your data is not colocated. Please check the documentation [https://ignite.apache.org/docs/latest/SQL/distributed-joins] . You should colocate data or use distributedJoins flag. > The primary key of the primary table as a condition for a multi-table join > query > -------------------------------------------------------------------------------- > > Key: IGNITE-16694 > URL: https://issues.apache.org/jira/browse/IGNITE-16694 > Project: Ignite > Issue Type: Bug > Affects Versions: 2.8.1, 2.12 > Reporter: Livia > Priority: Major > > When I use Ignite as a SQL database. I encountered an issue in 2.8.1 version > or the latest verison 2.12.0. > There is a multi-table join query, when I ues +primary table primary key+ > with '=' or 'IN' condition, the corresponding result is unexpected. But if I > use a +primary table non-primary key+ with '=' or 'IN', the result is ok, and > if I use a +primary table primary key+ with '!=' or 'NOT IN' as the > conditon, evenything is normal. And the issue did not happen in 2.7.5 version. > > I create three tables. > > {code:java} > CREATE TABLE STUDENT( > ID BIGINT PRIMARY KEY, > NAME VARCHAR, > EMAIL VARCHAR, > ) WITH "ATOMICITY=TRANSACTIONAL_SNAPSHOT"; > INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10001, 'Tom', 't...@123.com'); > INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10002, 'Lily', 'l...@123.com'); > INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10003, 'Sherry', > 'she...@123.com'); > INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10004, 'Petter', > 'pet...@123.com'); > INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10005, 'Livia', > 'li...@123.com'); > CREATE TABLE STUDENT_COURSE( > ID BIGINT PRIMARY KEY, > STUDENT_ID BIGINT NOT NULL, > COURSE_ID BIGINT NOT NULL, > ) WITH "ATOMICITY=TRANSACTIONAL_SNAPSHOT"; > INSERT INTO STUDENT_COURSE (ID, STUDENT_ID, COURSE_ID) VALUES(1, 10001, 1); > INSERT INTO STUDENT_COURSE (ID, STUDENT_ID, COURSE_ID) VALUES(2, 10002, 2); > INSERT INTO STUDENT_COURSE (ID, STUDENT_ID, COURSE_ID) VALUES(3, 10003, 3); > INSERT INTO STUDENT_COURSE (ID, STUDENT_ID, COURSE_ID) VALUES(4, 10004, 2); > INSERT INTO STUDENT_COURSE (ID, STUDENT_ID, COURSE_ID) VALUES(5, 10005, 3); > CREATE TABLE COURSE( > ID BIGINT PRIMARY KEY, > NAME VARCHAR, > CREDIT_RATING INT, > ) WITH "ATOMICITY=TRANSACTIONAL_SNAPSHOT"; > INSERT INTO COURSE (ID, NAME, CREDIT_RATING) VALUES(1, 'Criminal Evidence', > 20); > INSERT INTO COURSE (ID, NAME, CREDIT_RATING) VALUES(2, 'Employment Law', 10); > INSERT INTO COURSE (ID, NAME, CREDIT_RATING) VALUES(3, 'Jurisprudence', > 30);{code} > > And when I run this sql. there are different result. > {code:java} > SELECT COURSE.NAME AS COURSE_NAME, STUDENT.NAME AS STUDENT_NAME, STUDENT.ID > AS STUDENT_ID FROM STUDENT > LEFT JOIN STUDENT_COURSE > ON STUDENT.ID = STUDENT_COURSE.STUDENT_ID > LEFT JOIN COURSE > ON COURSE.ID = STUDENT_COURSE.COURSE_ID > WHERE 1=1 > -- AND STUDENT.ID IN (10001,10002) -- All values in column COURSE_NAME are > null > -- AND STUDENT.ID = 10001 or STUDENT.ID = 10002 -- All values in column > COURSE_NAME are null > -- AND STUDENT.ID != 10003 and STUDENT.ID != 10004 and STUDENT.ID != 10005 > -- OK > -- AND STUDENT.ID NOT IN (10003, 10004, 10005) -- OK > -- AND STUDENT.NAME IN ('Tom','Lily') -- OK > -- AND STUDENT.NAME = 'Tom' or STUDENT.NAME = 'Lily' -- OK {code} -- This message was sent by Atlassian Jira (v8.20.1#820001)