[
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}
h3. 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}
h3. 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:
{quote}
We should limit this rule to only match logical nodes because it is a plan
rewrite.
{quote}
h3. 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}
h3. 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.
h3. Scope
This is a targeted fix for the convention issue only. The collation issue
(CALCITE-3352) is tracked separately.
h3. 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.
was:
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}
h3. 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}
h3. 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:
{quote}
We should limit this rule to only match logical nodes because it is a plan
rewrite.
{quote}
h3. 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}
h3. 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.
h3. Scope
This is a targeted fix for the convention issue only. The collation issue
(CALCITE-3352) is tracked separately.
h3. 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.
h3. Workaround
{code:java}
planner.removeRule(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW);
{code}
> 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
> Assignee: leishp
> Priority: Major
> Labels: pull-request-available
>
> 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}
> h3. 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}
> h3. 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:
> {quote}
> We should limit this rule to only match logical nodes because it is a plan
> rewrite.
> {quote}
> h3. 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}
> h3. 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.
> h3. Scope
> This is a targeted fix for the convention issue only. The collation issue
> (CALCITE-3352) is tracked separately.
> h3. 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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
