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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.28.0
• Fix Version/s: 1.29.0
• Component/s: core
• Labels: pull-request-available
• Assignee: ZheHu
• Reporter: ZheHu
• Created: 05/Nov/21 14:52
• Updated: 04/Jan/22 08:27
• Resolved: 04/Jan/22 08:27

Description

When I convert the following RelNode to SQL(with CalciteSqlDialect) in ToLogicalConverterTest.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());

I get:

SELECT *
FROM SELECT `DEPTNO`
FROM `scott`.`DEPT`
INTERSECT ALL
SELECT `DEPTNO`
FROM `scott`.`EMP`
FETCH NEXT 2 ROWS ONLY

But the expected SQL should be:

SELECT *
FROM (SELECT `DEPTNO`
FROM `scott`.`DEPT`
INTERSECT ALL
SELECT `DEPTNO`
FROM `scott`.`EMP`)
FETCH NEXT 2 ROWS ONLY

Other SetOperators like UNION、MINUS can be converted correctly.

Issue Links

• links to: GitHub Pull Request #2609