[jira] [Created] (CALCITE-4876) Converting RelNode to SQL with CalciteSqlDialect gets wrong result while EnumerableIntersect is followed by EnumerableLimit

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

ZheHu created CALCITE-4876:
------------------------------

             Summary: 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


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)