[jira] [Commented] (CALCITE-5186) case statements returning char strings have unexpected results

["Jiajun Xie (Jira)" <ji...@apache.org>]

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

Jiajun Xie commented on CALCITE-5186:
-------------------------------------

I check the sql in other databases, MySQL, Postgres, Presto all keep char length. So I think this is a bug, and I have a question: can we think char(x) equals char(y) in *RelOptUtil#areRowTypesEqual*? 

> case statements returning char strings have unexpected results
> --------------------------------------------------------------
>
>                 Key: CALCITE-5186
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5186
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.31.0
>            Reporter: David
>            Priority: Minor
>
> When using a case statement where the return value is one of two different length strings the retsult is always padded to the length of the longer string. This is using the csv example, though I doubt the connection matters.
> The following sql shows that unless the string constants are cast as varchar the shorter of the two strings is padded in length.  This padding is unexpected. Trim() removes the padding so as a workaround I wrap my case statemens that return strings in trim(case ..... end)
> 0: jdbc:calcite:model=src/test/resources/qsg.> !connect jdbc:calcite:model=src/test/resources/qsg.json admin admin
> Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported. Default (TRANSACTION_NONE) will be used instead.
> 1: jdbc:calcite:model=src/test/resources/qsg.> select char_length(case when 1=1 then cast('abc' as varchar) else cast('defgh' as varchar) end),case when 1=1 then cast('abc' as varchar) else cast('defgh' as varchar) end || 'x',char_length(case when 1=1 then 'abc' else 'defgh' end),case when 1=1 then 'abc' else 'defgh' end || 'x';
> +--------+--------+--------+--------+
> | EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 |
> +--------+--------+--------+--------+
> | 3      | abcx   | 5      | abc  x |
> +--------+--------+--------+--------+
> 1 row selected (0.037 seconds)
> 1: jdbc:calcite:model=src/test/resources/qsg.>



--
This message was sent by Atlassian Jira
(v8.20.7#820007)