[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17317017#comment-17317017 ]
Stanislav Lukyanov commented on IGNITE-13019: --------------------------------------------- [~timonin.maksim] In your case, PK is the same as AK, which is the default. Maybe that's why the engine throws away the AK info - it doesn't do anything. Try changing your PK to a complex one. E.g. instead of ```CREATE TABLE A (ID INT PRIMARY KEY, TITLE VARCHAR) WITH \"AFFINITY_KEY=ID\"``` use ```CREATE TABLE A (ID INT, ID2 INT, TITLE VARCHAR, PRIMARY KEY (ID, ID2)) WITH \"AFFINITY_KEY=ID\"``` > Unexpected JOIN result when querying a single-node cluster > ---------------------------------------------------------- > > 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 > Assignee: Maksim Timonin > Priority: Major > Time Spent: 20m > Remaining Estimate: 0h > > 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)