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)
