[
https://issues.apache.org/jira/browse/CALCITE-5186?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17898551#comment-17898551
]
yanjing.wang commented on CALCITE-5186:
---------------------------------------
I meet the same problem and test on several dbs,
test sql
{code:java}
select c, char_length(c) from
(select case when sal > 100 then 'abc' when sal > 50 then 'abcde' when sal > 0
then 'abcdefgh' else 'a' end as c from emp) t; {code}
result with default settings
||Result/Db||Mysql||BigQuery||Oracle||PostgresSQL||Calcite||MSSQL||Hive||Presto||Spark||ClickHouse||
|padding|N|N|N|N|Y|N|N|N|N|N|
|no-padding|Y|Y|Y|Y|N|Y|Y|Y|Y|Y|
Hello, [~jiajunbernoulli], I believe it would be beneficial to set
*{{shouldConvertRaggedUnionTypesToVarying}}* to true by default for
*{{SqlConformance}}* and {*}{{{}RelDataTypeSystemImpl{}}}.{*}{*}{*}
do you agree?
> 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
> Assignee: Jiajun Xie
> 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.10#820010)
