[
https://issues.apache.org/jira/browse/CALCITE-7616?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
leishp updated CALCITE-7616:
----------------------------
Description:
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}
was:
{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}
> 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
> Priority: Major
>
> 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}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
