[
https://issues.apache.org/jira/browse/IGNITE-12847?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17272167#comment-17272167
]
Stanilovsky Evgeny commented on IGNITE-12847:
---------------------------------------------
[~timonin.maksim] this is very frequent issue observed in AI, i think it would
be very helpful if such cases would throw exception or possible ERR into log
(but exception is preferable as for me, need to be discussed in dev list). One
more [1]
[1]https://issues.apache.org/jira/browse/IGNITE-13019
> 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.9
> Environment: apache ignite 2.9.0, docker image:
> apacheignite/ignite:2.9.0
>
> Reporter: Alexey Melchakov
> Assignee: Maksim Timonin
> Priority: Blocker
>
> 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.9.0
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
