[jira] [Commented] (IGNITE-13019) Erroneous join result

["Stanislav Lukyanov (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17110214#comment-17110214 ] 

Stanislav Lukyanov commented on IGNITE-13019:
---------------------------------------------

It may be unintuitive but I believe this is actually the expected behavior.

The example above joins two tables with no affinity keys defined. In Ignite, the collocation of PARTITIONED caches is always done in terms of partitions, not in terms of nodes. I.e. the two tables being joined are not collocated.

Before 2.8, this could still work: if you have one node, all partitions on that node will be scanned anyway, so the data is collocated.

In 2.8 there is an optimization called Partition Pruning. That optimization causes SQL to only check one partition in case there is a `WHERE pkey = XYZ` condition. Because of that, the example above could work in 2.7, but doesn't work in 2.8.

> Erroneous join result
> ---------------------
>
>                 Key: IGNITE-13019
>                 URL: https://issues.apache.org/jira/browse/IGNITE-13019
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 2.8
>            Reporter: Stanilovsky Evgeny
>            Priority: Major
>
> Check reproducer near,
> seems something wrong with pkey logic , if we change it - results will be ok. 
> {code:java}
>     @Test
>     public void test() throws Exception {
>         inlineSize = 10;
>         startGrid(0);
>         String t1 = "CREATE TABLE dept\n" +
>             " (\n" +
>             "deptno LONG,\n" +
>             "dname VARCHAR,\n" +
>             "loc VARCHAR,\n" +
>             "CONSTRAINT pk_dept PRIMARY KEY (deptno)\n" +
>             " );";
>         execSql(t1);
>         String t2 = "CREATE TABLE emp\n" +
>             " (\n" +
>             "empno LONG,\n" +
>             "ename VARCHAR,\n" +
>             "job VARCHAR,\n" +
>             "mgr INTEGER,\n" +
>             "hiredate DATE,\n" +
>             "sal LONG,\n" +
>             "comm LONG,\n" +
>             "deptno LONG,\n" +
>             "CONSTRAINT pk_emp PRIMARY KEY (empno)\n" +
>             " );";
>         execSql(t2);
>         execSql("insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');");
>         execSql("insert into dept (deptno, dname, loc) values(20, 'RESEARCH', 'DALLAS');");
>         execSql("insert into dept (deptno, dname, loc) values(30, 'SALES', 'CHICAGO');");
>         execSql("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);");
>         execSql("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);");
>         execSql("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);");
>         List<List<?>> vals1 = execSql("SELECT d.deptno,\n" +
>             "e.ename\n" +
>             "FROM EMP e\n" +
>             "INNER JOIN dept d\n" +
>             "ON e.deptno = d.deptno AND e.deptno = 10;");
>         assertEquals(vals1.size(), 2);
>         List<List<?>> vals2 = execSql("SELECT d.deptno,\n" +
>             "e.ename\n" +
>             "FROM EMP e\n" +
>             "INNER JOIN dept d\n" +
>             "ON e.deptno = d.deptno AND d.DEPTNO = 10;");
>         //assertEquals(vals2.size(), 2); <--* uncomment for fail*
>         execSql("drop table dept");
>         String t3 = "CREATE TABLE dept\n" +
>             " (\n" +
>             "deptno LONG,\n" +
>             "dname VARCHAR,\n" +
>             "loc VARCHAR,\n" +
>             "CONSTRAINT pk_dept PRIMARY KEY (deptno, dname)\n" +
>             " );";
>         execSql(t3);
>         execSql("insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');");
>         execSql("insert into dept (deptno, dname, loc) values(20, 'RESEARCH', 'DALLAS');");
>         execSql("insert into dept (deptno, dname, loc) values(30, 'SALES', 'CHICAGO');");
>         List<List<?>> vals11 = execSql("SELECT d.deptno,\n" +
>             "e.ename\n" +
>             "FROM EMP e\n" +
>             "INNER JOIN dept d\n" +
>             "ON e.deptno = d.deptno AND e.deptno = 10;");
>         assertEquals(vals11.size(), 2);
>         List<List<?>> vals22 = execSql("SELECT d.deptno,\n" +
>             "e.ename\n" +
>             "FROM EMP e\n" +
>             "INNER JOIN dept d\n" +
>             "ON e.deptno = d.deptno AND d.DEPTNO = 10;");
>         assertEquals(vals22.size(), 2);
>     }
>     /** */
>     private List<List<?>> execSql(String qry) {
>         return grid(0).context().query()
>             .querySqlFields(new SqlFieldsQuery(qry).setLazy(true), false)
>             .getAll();
>     }
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)