[
https://issues.apache.org/jira/browse/IGNITE-21246?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Livia updated IGNITE-21246:
---------------------------
Description:
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}
was:
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}
> 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
> Priority: Critical
>
> 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)
