[
https://issues.apache.org/jira/browse/CALCITE-5186?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David updated CALCITE-5186:
---------------------------
Description:
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.>
> 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)
