You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Alexey Melchakov (Jira)" <ji...@apache.org> on 2020/12/07 20:20:00 UTC
[jira] [Updated] (IGNITE-12847) Incorrect query result
[ https://issues.apache.org/jira/browse/IGNITE-12847?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Alexey Melchakov updated IGNITE-12847:
--------------------------------------
Description:
This two queries should return same result.
{code:java}
SELECT d.deptno, d.dname, e.empno,
e.ename FROM emp e
INNER JOIN dept d
ON ( e.deptno = d.deptno )
WHERE EXISTS (SELECT 1 FROM emp t
WHERE t.mgr = e.empno) AND d.deptno IN ( 10, 20, 30 );
{code}
and
{code:java}
SELECT d.deptno, d.dname, e.empno,
e.ename FROM emp e
INNER JOIN dept d
ON ( e.deptno = d.deptno )
WHERE EXISTS (SELECT 1 FROM emp t
WHERE t.mgr = e.empno) AND e.deptno IN ( 10, 20, 30 );
{code}
The difference in _WHERE_ clause: _AND *d.deptno*_ vs _AND *e.deptno*_
*But in first case there are no results.*
In second case a have this one:
{code:java}
+--------+------------+-------+-------+
| DEPTNO | DNAME | EMPNO | ENAME |
+--------+------------+-------+-------+
| 10 | ACCOUNTING | 7839 | KING |
+--------+------------+-------+-------+
{code}
*This issue is not reproduces on apache ignite 2.7.6*
Example data:
{code:java}
CREATE TABLE dept (deptno LONG,dname VARCHAR,loc VARCHAR,CONSTRAINT pk_dept PRIMARY KEY (deptno));
CREATE TABLE emp (empno LONG,ename VARCHAR, job VARCHAR, mgr INTEGER, hiredate DATE,sal LONG,comm LONG,deptno LONG,CONSTRAINT pk_emp PRIMARY KEY (empno));
INSERT INTO dept (deptno, dname, loc) VALUES (10,'ACCOUNTING', 'NEW YORK');INSERT INTO dept (deptno, dname, loc) VALUES(20,'RESEARCH', 'DALLAS');
INSERT INTO dept (deptno, dname, loc) VALUES(30,'SALES', 'CHICAGO');INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981', 'dd-mm-yyyy'), 5000,NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981', 'dd-mm-yyyy'), 2850, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981', 'dd-mm-yyyy'), 2450,NULL, 10);
{code}
Reproduces both in sqlline.sh and IntelliJ IDEA SQL command console
Command for docker:
{code:java}
docker run -it --name \
-p 47500:47500 \
-p 47501:47501 \
-p 10800:10800 \
-e "CONFIG_URI=https://raw.githubusercontent.com/apache/ignite/master/examples/config/example-cache.xml" apacheignite/ignite:2.8.0
{code}
was:
This two queries should return same result.
{code:java}
SELECT d.deptno, d.dname, e.empno,
e.ename FROM emp e
INNER JOIN dept d
ON ( e.deptno = d.deptno )
WHERE EXISTS (SELECT 1 FROM emp t
WHERE t.mgr = e.empno) AND d.deptno IN ( 10, 20, 30 );
{code}
and
{code:java}
SELECT d.deptno, d.dname, e.empno,
e.ename FROM emp e
INNER JOIN dept d
ON ( e.deptno = d.deptno )
WHERE EXISTS (SELECT 1 FROM emp t
WHERE t.mgr = e.empno) AND e.deptno IN ( 10, 20, 30 );
{code}
*But in first case there are no results.*
In second case a have this one:
{code:java}
+--------+------------+-------+-------+
| DEPTNO | DNAME | EMPNO | ENAME |
+--------+------------+-------+-------+
| 10 | ACCOUNTING | 7839 | KING |
+--------+------------+-------+-------+
{code}
*This issue is not reproduces on apache ignite 2.7.6*
Example data:
{code:java}
CREATE TABLE dept (deptno LONG,dname VARCHAR,loc VARCHAR,CONSTRAINT pk_dept PRIMARY KEY (deptno));
CREATE TABLE emp (empno LONG,ename VARCHAR, job VARCHAR, mgr INTEGER, hiredate DATE,sal LONG,comm LONG,deptno LONG,CONSTRAINT pk_emp PRIMARY KEY (empno));
INSERT INTO dept (deptno, dname, loc) VALUES (10,'ACCOUNTING', 'NEW YORK');INSERT INTO dept (deptno, dname, loc) VALUES(20,'RESEARCH', 'DALLAS');
INSERT INTO dept (deptno, dname, loc) VALUES(30,'SALES', 'CHICAGO');INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981', 'dd-mm-yyyy'), 5000,NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981', 'dd-mm-yyyy'), 2850, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981', 'dd-mm-yyyy'), 2450,NULL, 10);
{code}
Reproduces both in sqlline.sh and IntelliJ IDEA SQL command console
Command for docker:
{code:java}
docker run -it --name \
-p 47500:47500 \
-p 47501:47501 \
-p 10800:10800 \
-e "CONFIG_URI=https://raw.githubusercontent.com/apache/ignite/master/examples/config/example-cache.xml" apacheignite/ignite:2.8.0
{code}
> Incorrect query result
> -----------------------
>
> Key: IGNITE-12847
> URL: https://issues.apache.org/jira/browse/IGNITE-12847
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.8
> Environment: apache ignite 2.8.0, docker image: apacheignite/ignite:2.8.0
>
> Reporter: Alexey Melchakov
> Priority: Major
>
> This two queries should return same result.
> {code:java}
> SELECT d.deptno, d.dname, e.empno,
> e.ename FROM emp e
> INNER JOIN dept d
> ON ( e.deptno = d.deptno )
> WHERE EXISTS (SELECT 1 FROM emp t
> WHERE t.mgr = e.empno) AND d.deptno IN ( 10, 20, 30 );
> {code}
> and
> {code:java}
> SELECT d.deptno, d.dname, e.empno,
> e.ename FROM emp e
> INNER JOIN dept d
> ON ( e.deptno = d.deptno )
> WHERE EXISTS (SELECT 1 FROM emp t
> WHERE t.mgr = e.empno) AND e.deptno IN ( 10, 20, 30 );
> {code}
>
> The difference in _WHERE_ clause: _AND *d.deptno*_ vs _AND *e.deptno*_
> *But in first case there are no results.*
> In second case a have this one:
> {code:java}
> +--------+------------+-------+-------+
> | DEPTNO | DNAME | EMPNO | ENAME |
> +--------+------------+-------+-------+
> | 10 | ACCOUNTING | 7839 | KING |
> +--------+------------+-------+-------+
> {code}
> *This issue is not reproduces on apache ignite 2.7.6*
>
> Example data:
> {code:java}
> CREATE TABLE dept (deptno LONG,dname VARCHAR,loc VARCHAR,CONSTRAINT pk_dept PRIMARY KEY (deptno));
> CREATE TABLE emp (empno LONG,ename VARCHAR, job VARCHAR, mgr INTEGER, hiredate DATE,sal LONG,comm LONG,deptno LONG,CONSTRAINT pk_emp PRIMARY KEY (empno));
> INSERT INTO dept (deptno, dname, loc) VALUES (10,'ACCOUNTING', 'NEW YORK');INSERT INTO dept (deptno, dname, loc) VALUES(20,'RESEARCH', 'DALLAS');
> INSERT INTO dept (deptno, dname, loc) VALUES(30,'SALES', 'CHICAGO');INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981', 'dd-mm-yyyy'), 5000,NULL, 10);
> INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981', 'dd-mm-yyyy'), 2850, NULL, 30);
> INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981', 'dd-mm-yyyy'), 2450,NULL, 10);
> {code}
>
> Reproduces both in sqlline.sh and IntelliJ IDEA SQL command console
> Command for docker:
> {code:java}
> docker run -it --name \
> -p 47500:47500 \
> -p 47501:47501 \
> -p 10800:10800 \
> -e "CONFIG_URI=https://raw.githubusercontent.com/apache/ignite/master/examples/config/example-cache.xml" apacheignite/ignite:2.8.0
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)