I also check sqlite and mssql 2017 and obtain the same results, but i also
found that standard has differ treatment:
9.3 Set operation result data types
Function
Specify the Syntax Rules and result data types for <case expres-
sion>s and <query expression>s having set operators.
... skip ...
i) If any of the data types in DTS is variable-length char-
acter string, then the result data type is variable-length
character string with maximum length in characters equal
to the maximum of the lengths in characters and maximum
lengths in characters of the data types in DTS.
ii) Otherwise, the result data type is fixed-length character
string with length in characters equal to the maximum of
the lengths in characters of the data types in DTS.
But i suppose that exact sizing (i.e. 'a' instead of 'a ') is more usable,
just imagine if someone calls :
1. Form COALESCE from table1.
2. INSERT INTO SELECT Syntax with COALESCE into table2
3. Further search in table1 using table2 predicates.
'a ' not belong to table1 and cant be found, i think this is not a single
case.
community ?
Julian, thanks !
mysql differs here:
mysql> SELECT LENGTH(a) from (SELECT COALESCE('a', 'bbbb') as a) as t;
+-----------+
| LENGTH(a) |
+-----------+
| 1 |
+-----------+
mysql> SELECT LENGTH(a) from (SELECT COALESCE('aa', 'bbbb') as a) as t;
+-----------+
| LENGTH(a) |
+-----------+
| 2 |
+-----------+
I don`t claim that calcite need to be the same, i just want to
understand what is correct.
My point of view - if COALESCE transforms into CASE-WHEN statement and
returns first not null it strange instead of expected 'a' gets 'a '.
I`m wrong ?
The current behavior looks ok to me. The expression has type CHAR(2)
because the arguments have types CHAR(1) and CHAR(2). So ‘a’ is widened
to ‘a ‘.
Julian
On Sep 22, 2021, at 12:00 AM, stanilovsky evgeny
<estanilovs...@gridgain.com> wrote:
sorry for typo, 'fill the ticket' of course ))
hi community !
I found that COALESCE('a', 'bb') will return 'a ' <-- whitespace is
present, i found that now it expands for maximum presented CHAR
(RelDataType#inferReturnType logic)
sql 92 standard tolds:
'''
COALESCE (V1, V2) is equivalent to the following <case specification>:
CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END
'''
So i suppose that existing behavior is erroneous, if it`s ok i fell
the ticket.
wdyt ?
