[jira] [Created] (CALCITE-7616) ProjectToLogicalProjectAndWindowRule should not match non-logical Project nodes with JDBC convention

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

leishp created CALCITE-7616:
-------------------------------

             Summary: ProjectToLogicalProjectAndWindowRule should not match non-logical Project nodes with JDBC convention
                 Key: CALCITE-7616
                 URL: https://issues.apache.org/jira/browse/CALCITE-7616
             Project: Calcite
          Issue Type: Bug
          Components: core
            Reporter: leishp


{quote} When a JDBC schema's dialect supports window functions (e.g. MySQL, PostgreSQL), a query with CTE and RANK() throws AssertionError during VolcanoPlanner optimization:

{code} AssertionError: Relational expression  LogicalWindow.JDBC.TEST.[]  has calling-convention JDBC.TEST but does not implement the required interface  'interface org.apache.calcite.adapter.jdbc.JdbcRel' of that convention {code}

_Reproduction:_

A JDBC schema with MySQL dialect (\{{supportsWindowFunctions() = true}}), and a query with CTE + RANK():

{code:sql} WITH ranked AS (  SELECT customer_name, contract_amount,  RANK() OVER (ORDER BY contract_amount DESC) AS rnk  FROM sales_data ) SELECT customer_name FROM ranked WHERE rnk <= 3 {code}

_Root cause:_

{\{ProjectToLogicalProjectAndWindowRule}} has no convention restriction in its operand definition:

{code:java} // ProjectToWindowRule.java:196 .withOperandSupplier(b ->  b.operand(Project.class)  .predicate(Project::containsOver)  .anyInputs()) // trait=null matches ANY convention {code}

When \{{JdbcProjectRule}} first converts a \{{LogicalProject}} with OVER to \{{JdbcProject}} (JDBC convention), and then \{{ProjectToLogicalProjectAndWindowRule}} fires on it, the JDBC traitSet propagates through \{{CalcRelSplitter}} to \{{LogicalWindow.create()}}, causing the AssertionError.

This is related to CALCITE-3352 (wrong collation on generated Window) — both bugs share the same root cause: \{{ProjectToWindowRule}} blindly propagates the parent's traitSet to the generated \{{LogicalWindow}}. Danny Chen commented on CALCITE-3352: "we should limit this rule to only match logical nodes because it is a plan rewrite."

_Fix:_

Add \{{.trait(Convention.NONE)}} to the operand:

{code:java} // ProjectToWindowRule.java .withOperandSupplier(b ->  b.operand(Project.class)  .trait(Convention.NONE) // only match logical Project  .predicate(Project::containsOver)  .anyInputs()) {code}

_Impact:_
 * When dialect supports window functions: window function is pushed down to database via \{{JdbcProject}}. Correct behavior.

 * When dialect does NOT support window functions: \{{ProjectToLogicalProjectAndWindowRule}} fires on \{{LogicalProject(NONE)}} as before. No change.

_Scope:_ This is a targeted fix for the convention issue only. The collation issue (CALCITE-3352) is tracked separately.

_Test:_ A JUnit test \{{JdbcWindowConventionBugTest}} is provided in the PR, using HSQLDB in-memory database with MySQL dialect override to reproduce the bug without an external database.

_Workaround:_ {code:java} planner.removeRule(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW); {code} {quote}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)