[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17398602#comment-17398602 ] Maksim Timonin commented on IGNITE-13019: - Hi [~amashenkov]! I renamed to SqlAstTravrser. > 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: Evgeny Stanilovsky >Assignee: Maksim Timonin >Priority: Major > Time Spent: 1h 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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> 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)
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17397989#comment-17397989 ] Andrey Mashenkov commented on IGNITE-13019: --- [~timonin.maksim], PR looks good, except the one thing: class name QueryPartitionsChecker is irrelevant. QueryPartitionsChecker is used for examining the AST tree to extract some information, not only related to partitions. Let's fix the class name and proceed with merging. > 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: Evgeny Stanilovsky >Assignee: Maksim Timonin >Priority: Major > Time Spent: 1h 10m > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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> 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)
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17388691#comment-17388691 ] Evgeny Stanilovsky commented on IGNITE-13019: - [~amashenkov] is it ok for now ? > 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: Evgeny Stanilovsky >Assignee: Maksim Timonin >Priority: Major > Time Spent: 1h 10m > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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> 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)
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17373952#comment-17373952 ] Ignite TC Bot commented on IGNITE-13019: {panel:title=Branch: [pull/8744/head] Base: [master] : No blockers found!|borderStyle=dashed|borderColor=#ccc|titleBGColor=#D6F7C1}{panel} {panel:title=Branch: [pull/8744/head] Base: [master] : New Tests (6)|borderStyle=dashed|borderColor=#ccc|titleBGColor=#D6F7C1} {color:#8b}Queries 1{color} [[tests 6|https://ci.ignite.apache.org/viewLog.html?buildId=6070929]] * {color:#013220}IgniteBinaryCacheQueryTestSuite: CheckWarnJoinPartitionedTables.joinSameTableWithComplexPrimaryKey - PASSED{color} * {color:#013220}IgniteBinaryCacheQueryTestSuite: CheckWarnJoinPartitionedTables.joinSameTableWithPrimaryKey - PASSED{color} * {color:#013220}IgniteBinaryCacheQueryTestSuite: CheckWarnJoinPartitionedTables.joinSameTableWithPrimaryAffinityKey - PASSED{color} * {color:#013220}IgniteBinaryCacheQueryTestSuite: CheckWarnJoinPartitionedTables.testWrongQueryButAllAffinityKeysAreUsed - PASSED{color} * {color:#013220}IgniteBinaryCacheQueryTestSuite: CheckWarnJoinPartitionedTables.joinSameTableWithComplexPrimaryKeySingleAffKey - PASSED{color} * {color:#013220}IgniteBinaryCacheQueryTestSuite: CheckWarnJoinPartitionedTables.joinWithPrimaryKey - PASSED{color} {panel} [TeamCity *-- Run :: All* Results|https://ci.ignite.apache.org/viewLog.html?buildId=6068742buildTypeId=IgniteTests24Java8_RunAll] > 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: 1h > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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', >
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17373645#comment-17373645 ] Maksim Timonin commented on IGNITE-13019: - Hi [~slukyanov] [~amashenkov] ! Sorry for this long delay. Now I've back to this ticket, update PR and ready complete it. [~amashenkov] I've merge checks for subqueries, partitions, joins to the single AST traverse (instead of 3). [~slukyanov] # I've answered on your comments in review, add some comments and tests to code. # About INFO log about restrictions while cache is created. I don't know in which place in a code to write this. For dynamic created caches I can put this on user side. But what about caches that created with IgniteConfiguration, every node then should write this message? Also client nodes too (and thin client too)? WDYT? > 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: 1h > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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> execSql(String qry) { > return grid(0).context().query() >
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17326560#comment-17326560 ] Stanislav Lukyanov commented on IGNITE-13019: - [~amashenkov], on your comment about the performance - how bad do you think it'll be? I actually like that currently in the code all checks are separate as it makes them much either to read. Merging them all into a single method or a chain of visitors might get pretty complicated... Do you think running a set of benchmarks before merging will suffice to show that this doesn't hurt the performance too much? > 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: 0.5h > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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> 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)
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17326558#comment-17326558 ] Stanislav Lukyanov commented on IGNITE-13019: - Some comments in the PR. Also, I would like to raise a couple of additional suggestions. First, I think it would be very helpful if we print an INFO-level message when a partitioned cache is created that contains the primary and affinity key columns and explains that the JOINs with other partitioned caches must use either the affinity key or the primary key. Second, I think it would be good to have a mode when an incorrect JOIN condition throws an error instead of printing a warning. Furthermore, I think the next minor Ignite version can have the error as default. The error can be suppressed by changing a metastore property. > 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: 0.5h > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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> execSql(String qry) { > return grid(0).context().query() >
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17326504#comment-17326504 ] Andrey Mashenkov commented on IGNITE-13019: --- [~timonin.maksim], As far as I understand, the fix is to log a warning if non-collocated tables participated in JOIN and distributedJoins option is off. I see multiple calls in a row that traverse AST tree for different purposes, that impacts query latency. {code:java} map.partitioned(SplitterUtils.hasPartitionedTables(mapQry)); map.hasSubQueries(SplitterUtils.hasSubQueries(mapQry)); map.hasOuterJoinReplicatedPartitioned(SplitterUtils.hasOuterJoinReplicatedPartitioned(mapQry.from())); if (map.isPartitioned() && !distributedJoins) SplitterUtils.lookForPartitionedJoin(mapQry, null, log); if (map.isPartitioned() && canExtractPartitions) map.derivedPartitions(extractor.extract(mapQry));{code} Why don't traverse AST tree only once here? Or disable this check by default at least. > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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;"); >
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17318122#comment-17318122 ] Maksim Timonin commented on IGNITE-13019: - [~slukyanov] hi! Thanks for the answer, this is correct. I debug a code and found 2 places where I can put the check: 1. On AST parsing phase (GridSqlQuerySplitter.split0 -> splitter.splitQuery); 2. After that while building collocated model for checking distributedJoins flag (GridSqlQuerySplitter.split0). In my PR [1] I've decided to add a check on the AST parsing phase, due to there are already some similar checks (hasOuterJoinReplicatedPartitioned) for example. WDYT, is it a right direction? [1] https://github.com/apache/ignite/pull/8744/files > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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> execSql(String qry) { > return grid(0).context().query() > .querySqlFields(new SqlFieldsQuery(qry).setLazy(true), false) > .getAll(); > } > {code}
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> 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> 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> 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)
[jira] [Commented] (IGNITE-13019) Unexpected JOIN result when querying a single-node cluster
[ https://issues.apache.org/jira/browse/IGNITE-13019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17316995#comment-17316995 ] Maksim Timonin commented on IGNITE-13019: - [~slukyanov] hi! I'm working on this ticket. And found weird behavior of GridH2Table.affKeyCol. I've created a table with the query: "CREATE TABLE A (ID INT PRIMARY KEY, TITLE VARCHAR) WITH \"AFFINITY_KEY=ID\" then I'm expecting that *GridH2Table.affKeyCol* stored ID column, but actually it was _KEY. I've debugged and found that we lost info about affinity column in *QueryUtils#typeForQueryEntity* [1]. At this line *qryEntity.getKeyFields()* is empty, because we skip it here *CommandProcessor#toQueryEntity* [2]. At this line we go to the if block and then fill _keyFieldName_ only, but _keyFields_ is filled in the else block only. I don't understand is it expected behavior? [1] [QueryUtils#L557|https://github.com/apache/ignite/blob/55e57173adc5fa2a891fb6a9d8d8fefdaab87ba9/modules/core/src/main/java/org/apache/ignite/internal/processors/query/QueryUtils.java#L557] [2] [CommandProcessor#L1129|https://github.com/apache/ignite/blob/55e57173adc5fa2a891fb6a9d8d8fefdaab87ba9/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/CommandProcessor.java#L1129] (edited) > 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-'), 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-'), 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-'), 2450, null, 10);"); > 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> 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> vals11 = execSql("SELECT d.deptno,\n" + > "e.ename\n" + > "FROM EMP e\n" + > "INNER JOIN dept d\n" + > "ON