CASE converts empty string to spaces, results in incorrect result length [CORE4147] #4474
Comments
|
Commented by: @mrotteveel The type of a CASE expression with literals is CHAR, CHAR is padded with space up to the declared length. As one of the results is 'X' the type of the first case is CHAR(1), and the empty string therefor is actually ' ', and for the second case it is CHAR(9). You either need to explicitly cast one of the CASE branches to VARCHAR, or use TRIM before assigning the value. The if works as expected because you are assigning directly to a VARCHAR, and the empty string is coerced directly without padding. |
|
Commented by: Sean Leyne (seanleyne) Edited Summary to clean up language |
Modified by: Sean Leyne (seanleyne)summary: case convert empty string to spaces, incorrect length of result string => CASE converts empty string to spaces, results in incorrect result length |
|
Commented by: Sean Leyne (seanleyne) It seems wrong that the implied datatype for CASE would be CHAR(). This fact is not well known, and from my POV is counter-intuive. A more appropriate choice would be VARCHAR(). Should this case be closed as "as designed" and a new case "Change implied datatype for CASE to VARCHAR()" created? |
|
Commented by: @dyemanov This is as designed. The SQL spec defines string literals as CHAR (surprise! surprise!) and, again according to the SQL spec, CASE just chooses the most "common" datatype from its inputs (/ doc / sql.extensions / README.data_type_results_of_aggregations.txt). |
Submitted by: Nick Dirov (dnn)
make one thinks by two way, using if and case
as a result first row containing space before T
and length of result string also incorrect
create or alter procedure SP$TEST
returns (
R varchar(20) character set win1251,
Z varchar(20) character set win1251,
annot varchar(80),
LEN integer)
as
begin
if (1=1)
then z = ''; -- empty string
else z = 'X';
r = :z || 'T';
len = char_length(:r);
annot = 'correct';
suspend;
z = (case when (1=1) then '' /*empty string*/ else 'X' end);
r = :z || 'T';
len = char_length(:r);
annot = 'incorrect, space before T';
suspend;
z = (case when (1=1) then '' else '123456789' end);
r = :z || 'T';
len = char_length(:r);
annot = 'incorrect, got length by else clause';
suspend;
end
The text was updated successfully, but these errors were encountered: