[
https://issues.apache.org/jira/browse/IGNITE-16694?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yury Gerzhedovich resolved IGNITE-16694.
----------------------------------------
Resolution: Invalid
> 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', '[email protected]');
> INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10002, 'Lily', '[email protected]');
> INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10003, 'Sherry',
> '[email protected]');
> INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10004, 'Petter',
> '[email protected]');
> INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10005, 'Livia',
> '[email protected]');
> 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)
