[
https://issues.apache.org/jira/browse/IGNITE-16694?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Livia updated IGNITE-16694:
---------------------------
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', '[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);
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
was:
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', '[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);
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
Summary: The primary key of the primary table as a condition for a
multi-table join query (was: The primary key of the primary table cannot be
used as a condition for a multi-table join query)
> 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
> 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', '[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);
>
> 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
> Priority: Critical
>
> 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)
