You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Yash Mayya (Jira)" <ji...@apache.org> on 2024/10/28 16:59:00 UTC

[jira] [Commented] (CALCITE-6648) IGNORE NULLS / RESPECT NULLS window function option can result in a type validation error in SqlToRelConverter

    [ https://issues.apache.org/jira/browse/CALCITE-6648?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17893540#comment-17893540 ] 

Yash Mayya commented on CALCITE-6648:
-------------------------------------

I think the bug is in the return type inference used for the {{SqlNullTreatmentOperator}} - [https://github.com/apache/calcite/blob/9b4eff06d6a16b3013f01d5fe4bceeaff6822603/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java#L43.] It's using {{{}ReturnTypes.ARG0{}}}, but it should probably be something like {{ARG0_FORCE_NULLABLE}} or {{ARG0_NULLABLE_IF_EMPTY}} instead?

> 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)