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

[Livia (Jira)]

Livia created IGNITE-16694:
------------------------------

             Summary: The primary key of the primary table cannot be used 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
         Environment: I create three tables.

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);

 

And when I run this sql. there are different result.

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
            Reporter: Livia


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.



--
This message was sent by Atlassian Jira
(v8.20.1#820001)