Alexey Melchakov created IGNITE-12847: -----------------------------------------
Summary: 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 * 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} Reporter: Alexey Melchakov 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: {{+--------+------------+-------+-------+}} {{| DEPTNO | DNAME | EMPNO | ENAME |}} {{+--------+------------+-------+-------+}} {{| 10 | ACCOUNTING | 7839 | KING |}} {{+--------+------------+-------+-------+}} *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} -- This message was sent by Atlassian Jira (v8.3.4#803005)