[
https://issues.apache.org/jira/browse/CALCITE-6648?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yash Mayya updated CALCITE-6648:
--------------------------------
Description:
If the {{IGNORE NULLS}} / {{RESPECT NULLS}} window function option is used when
the input to the window function is a non-nullable type and the window frame is
of type {{ROWS}} with an offset based lower or upper bound (i.e., {{{}n
PRECEDING / FOLLOWING){}}}, this results in an error
[here|https://github.com/apache/calcite/blob/9b4eff06d6a16b3013f01d5fe4bceeaff6822603/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L514-L524]
- the inferred return type for the window function from the parsed {{SqlNode}}
is a non-nullable type but the converted type is nullable (which makes sense
since the result should be {{null}} for rows where the window frame is "out of
bounds").
This issue does not occur if any of the following conditions are true:
* {{IGNORE NULLS}} / {{RESPECT NULLS}} options are not specified because in
this case the inferred return type for the window function call in the parsed
{{SqlNode}} is also a nullable type.
* The input to the window function is a nullable type.
* The window frame is of type {{ROWS}} but both the bounds are UNBOUNDED
PRECEDING / CURRENT ROW / UNBOUNDED FOLLOWING.
* The window frame is of type {{{}RANGE{}}}.
While it likely doesn't make much sense for users to specify these options when
the window function's input type is non-nullable, it should ideally not result
in an arcane error like:
{code:java}
Caused by: java.lang.AssertionError: Conversion to relational algebra failed to
preserve datatypes:
validated type:
RecordType(... INTEGER NOT NULL EXPR$3) NOT NULL
converted type:
RecordType(... INTEGER EXPR$3) NOT NULL){code}
was:
If the {{IGNORE NULLS}} / {{RESPECT NULLS}} window function option is used when
the input to the window function is a non-nullable type and the window frame is
of type {{ROWS}} with an offset based lower or upper bound (i.e., {{{}n
PRECEDING / FOLLOWING){}}}, this results in an error
[here|https://github.com/apache/calcite/blob/9b4eff06d6a16b3013f01d5fe4bceeaff6822603/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L514-L524]
- the inferred return type for the window function from the parsed {{SqlNode}}
is a non-nullable type but the converted type is nullable (which makes sense
since the result should be {{null}} for rows where the window frame is "out of
bounds").
This issue does not occur if any of the following conditions are true:
* {{IGNORE NULLS}} / {{RESPECT NULLS}} options are not specified because in
this case the inferred return type for the window function call in the parsed
{{SqlNode}} is also a nullable type.
* The input to the window function is a nullable type.
* The window frame is of type {{ROWS}} but both the bounds are UNBOUNDED
PRECEDING / CURRENT ROW / UNBOUNDED FOLLOWING.
* The window frame is of type {{{}RANGE{}}}.
> IGNORE NULLS / RESPECT NULLS window function option can result in a type
> validation error in SqlToRelConverter
> --------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6648
> URL: https://issues.apache.org/jira/browse/CALCITE-6648
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Yash Mayya
> Priority: Major
>
> If the {{IGNORE NULLS}} / {{RESPECT NULLS}} window function option is used
> when the input to the window function is a non-nullable type and the window
> frame is of type {{ROWS}} with an offset based lower or upper bound (i.e.,
> {{{}n PRECEDING / FOLLOWING){}}}, this results in an error
> [here|https://github.com/apache/calcite/blob/9b4eff06d6a16b3013f01d5fe4bceeaff6822603/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L514-L524]
> - the inferred return type for the window function from the parsed
> {{SqlNode}} is a non-nullable type but the converted type is nullable (which
> makes sense since the result should be {{null}} for rows where the window
> frame is "out of bounds").
>
> This issue does not occur if any of the following conditions are true:
> * {{IGNORE NULLS}} / {{RESPECT NULLS}} options are not specified because in
> this case the inferred return type for the window function call in the parsed
> {{SqlNode}} is also a nullable type.
> * The input to the window function is a nullable type.
> * The window frame is of type {{ROWS}} but both the bounds are UNBOUNDED
> PRECEDING / CURRENT ROW / UNBOUNDED FOLLOWING.
> * The window frame is of type {{{}RANGE{}}}.
>
> While it likely doesn't make much sense for users to specify these options
> when the window function's input type is non-nullable, it should ideally not
> result in an arcane error like:
>
>
> {code:java}
> Caused by: java.lang.AssertionError: Conversion to relational algebra failed
> to preserve datatypes:
> validated type:
> RecordType(... INTEGER NOT NULL EXPR$3) NOT NULL
> converted type:
> RecordType(... INTEGER EXPR$3) NOT NULL){code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
