[ https://issues.apache.org/jira/browse/CALCITE-4876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17439678#comment-17439678 ]
ZheHu commented on CALCITE-4876: -------------------------------- Hi, [~julianhyde], you are right about this. When I change the precedence of *INTERSECT_ALL* from {color:#FF0000}18 to 14{color}(same with UNION and EXCEPT), I get the right SQL expression. Just as you mentioned above, I also tried the following two testcases: (1) Converting the below RelNode to SQL with HiveSqlDialect(LIMIT syntax), SQL expression is correct. {code:java} RelNode rel = builder.scan("DEPT") .project(builder.field("DEPTNO")) .scan("EMP") .project(builder.field("DEPTNO")) .minus(true) .limit(0, 2) .build(); new RelToSqlConverter(SqlDialect.DatabaseProduct.HIVE.getDialect()) .visitRoot(rel) .asStatement() .toSqlString(SqlDialect.DatabaseProduct.HIVE.getDialect()) {code} (2) Running the following SQL in JdbcTest.java, I also get correct results. {code:java} CalciteAssert.hr() .query("select \"deptno\" from \"hr\".\"depts\"\n" + "intersect all\n" + "select \"deptno\" from \"hr\".\"depts\"\n" + "limit 2") .returns("deptno=40\ndeptno=10\n"); {code} I might need a bit more time to look further into this precedence issue. > Converting RelNode to SQL with CalciteSqlDialect gets wrong result while > EnumerableIntersect is followed by EnumerableLimit > --------------------------------------------------------------------------------------------------------------------------- > > Key: CALCITE-4876 > URL: https://issues.apache.org/jira/browse/CALCITE-4876 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.28.0 > Reporter: ZheHu > Priority: Major > > When I convert the following RelNode to SQL(with CalciteSqlDialect) in > ToLogicalConverterTest.java: > {code:java} > @Test void testIntersect() { > final RelBuilder builder = builder(); > RelNode rel = > builder.scan("DEPT") > .project(builder.field("DEPTNO")) > .scan("EMP") > .project(builder.field("DEPTNO")) > .intersect(true) > .limit(0, 2) > .build(); > System.out.println(new > RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect()).visitRoot(rel).asStatement()); > {code} > I get: > {code:java} > SELECT * > FROM SELECT `DEPTNO` > FROM `scott`.`DEPT` > INTERSECT ALL > SELECT `DEPTNO` > FROM `scott`.`EMP` > FETCH NEXT 2 ROWS ONLY > {code} > But the expected SQL should be: > {code:java} > SELECT * > FROM (SELECT `DEPTNO` > FROM `scott`.`DEPT` > INTERSECT ALL > SELECT `DEPTNO` > FROM `scott`.`EMP`) > FETCH NEXT 2 ROWS ONLY > {code} > Other SetOperators like UNION、MINUS can be converted correctly. -- This message was sent by Atlassian Jira (v8.3.4#803005)