[jira] [Resolved] (IGNITE-16694) The primary key of the primary table as a condition for a multi-table join query

["Yury Gerzhedovich (Jira)" <ji...@apache.org>]

     [ 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', 'tom@123.com');
> INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10002, 'Lily', 'lily@123.com');
> INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10003, 'Sherry', 'sherry@123.com');
> INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10004, 'Petter', 'petter@123.com');
> INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10005, 'Livia', 'livia@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)