Livia created IGNITE-21246:
------------------------------
Summary: Multi-table join query results are abnormal
Key: IGNITE-21246
URL: https://issues.apache.org/jira/browse/IGNITE-21246
Project: Ignite
Issue Type: Bug
Affects Versions: 2.15
Environment: Red Hat Enterprise Linux 7
Apache Ignite In-Memory Distributed Database 2.15.0
Reporter: Livia
Hi,
When I use Ignite as an SQL database,
There are 3 tables to left join, but the result is abnormal.
There is only one Ignite server node, and persistence is enabled.
I use PARTITIONED mode to create SQL tables, then I execute a join query in a
non-colocated mode ({_}jdbc:ignite:thin://localhost;distributedJoins=true{_}).
But I got an error prompting a lack of index for the table STUDENT_COURSE.
The documentation suggests that a replicated table needs a column index, but
these are PARTITIONED tables.
_If you use a non-collocated join on a column from a [replicated
table|https://ignite.apache.org/docs/2.11.1/data-modeling/data-partitioning#replicated],
the column must have an index. Otherwise, you will get an exception._
Okay, I create indexs for it. Then I execute the query again. I expect 6 rows,
but actually, only 1 row is returned.
A more unusual situation arises when I add a WHERE condition to the query. The
result is correct.
----
The detailed commands are as follows:
# Create table use PARTITIONED mode, and insert data
#
{code:java}
CREATE TABLE STUDENT(
ID BIGINT PRIMARY KEY,
NAME VARCHAR,
EMAIL VARCHAR,
) WITH "TEMPLATE=PARTITIONED,ATOMICITY=TRANSACTIONAL_SNAPSHOT";
CREATE TABLE STUDENT_COURSE(
ID BIGINT PRIMARY KEY,
STUDENT_ID BIGINT NOT NULL,
COURSE_ID BIGINT NOT NULL,
) WITH "TEMPLATE=PARTITIONED,ATOMICITY=TRANSACTIONAL_SNAPSHOT";
CREATE TABLE COURSE(
ID BIGINT PRIMARY KEY,
NAME VARCHAR,
CREDIT_RATING INT,
) WITH "TEMPLATE=PARTITIONED,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');
INSERT INTO STUDENT (ID, NAME, EMAIL) VALUES(10006, 'Ada', 'a...@123.com');
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);
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}
2.Execute a join query in a non-colocated mode
{code:java}
SELECT s.ID, s.NAME, s.EMAIL , c.NAME FROM STUDENT s
LEFT JOIN STUDENT_COURSE j ON j.STUDENT_ID=s.id
LEFT JOIN COURSE c ON c.id=j.COURSE_ID {code}
{panel:title=Expect results:}
10001 Tom t...@123.com Criminal Evidence
10002 Lily l...@123.com Employment Law
10003 Sherry she...@123.com Jurisprudence
10004 Petter pet...@123.com Employment Law
10005 Livia li...@123.com Jurisprudence
10006 Ada a...@123.com
{panel}
{panel:title=actual results}
10006 Ada a...@123.com
{panel}
3.Execute a join query with where condition in a non-colocated mode
{code:java}
SELECT s.ID, s.NAME, s.EMAIL , c.NAME FROM STUDENT s
LEFT JOIN STUDENT_COURSE j ON j.STUDENT_ID=s.id
LEFT JOIN COURSE c ON c.id=j.COURSE_ID
WHERE s.NAME='Sherry' {code}
{panel:title=actual results}
10003 Sherry she...@123.com Jurisprudence
{panel}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
