At 05:51 AM 19/04/2007, you wrote:

>Recently while working with a new stored procedure in Firebird 1.5.3
>on linux a developer tried out using a case expression. Our java
>developer then noticed that the result was being padded with spaces
>according to the largest string in the case statement. You can see
>this same behaviour in a select query
>
>select strlen( case when 1=1 then 'test' when 2=0 then 'biggertest'
>else 'control' end )
>from rdb$database
>
>If you remove the strlen it is returning test but the length returned
>is 10 the size of the biggertest string. This confirms what java was
>getting which was a result of 'test '
>
>Is this supposed to be the behavior of a case statement? Am I not
>understanding something?

The engine prepares a buffer for (in this case, because it is
strings) a char(n) that is large enough to accommodate the largest
specified return value. CHAR(n) is always returned padded with blanks.

(So far, so good..)

In DSQL, you might be able to coerce it to a varchar(10) using
CAST(), though I haven't tried that.

But, in a SP, you would be responsible for defining a variable for
returning the result value via the INTO clause, thus relieving the
engine of its need to figure out the data type for the expression result.

Now, theoretically, if you wanted it to be a varchar, you would
declare a varchar(10) for it and expect that the result would be
converted automagically to a varchar(10):

create procedure testblah
returns (
blah varchar(10),
lgth smallint)
as
begin
select case when 1=1 then 'test' when 2=0 then 'biggertest'
else 'control' end
from rdb$database
into :blah;
lgth = strlen(blah);
suspend;
end

(commit it of course!)

But - surprise! that doesn't work as expected! The result is

BLAH LGTH
======== ====
test 10

So, next, we'll do a reality check on that char-to-varchar
conversion, just to make sure it's not the UDF that is pulling the
wrong rabbit out of the hat:

recreate procedure testblah
returns (
blah varchar(15),
lgth smallint)
as
begin
select case when 1=1 then 'test' when 2=0 then 'biggertest'
else 'control' end
from rdb$database
into :blah;
blah = blah || 'plus';
lgth = strlen(blah);
suspend;
end

BLAH LGTH
============ ====
test plus 14

So --- hmmm, this looks a little anomalous, at least in
v.1.5.x. (You're using 1.5.3, I tested with 1.5.4). It's a bug
report for 1.5.4 at least.

Next thing will be to repeat this test with v.2.0.x, where lots of
things have been fixed up for string things. (I shouldn't have got
into this before breakfast!)

Interesting, though...

./heLen