8.2.2 (fixpack 9)

Simple function - corrects call for substr. But doesn't work.
Look this sample - any call with wrong Idx

values( Test_Substr('01234567890123456789', -1 ))

SQL0901N The SQL statement failed because of a non-severe system
error.
Subsequent SQL statements can be processed. (Reason "Invalid position
or length parameters".) SQLSTATE=58004

CREATE FUNCTION Test_Substr
(vStr VARCHAR(20), Idx INTEGER
)
RETURNS CHARACTER(1)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
return
case
when Idx < 1 THEN '0'
when LENGTH(vStr) < Idx THEN '1'
when LENGTH(vStr) >= Idx THEN SUBSTR(vStr, Idx, 1)
else 'x'
end
@

Rewritten UDF for test case work correctly...

drop FUNCTION Test_Substr

DB20000I The SQL command completed successfully.



CREATE FUNCTION Test_Substr
(vStr VARCHAR(20), Idx INTEGER
)
RETURNS CHARACTER(1)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
return
case
when Idx < 1 THEN '0'
when LENGTH(vStr) < Idx THEN '1'
when LENGTH(vStr) >= Idx THEN '!'
else 'x'
end


DB20000I The SQL command completed successfully.



values( Test_Substr('01234567890123456789', -1 ))



1

-

0



1 record(s) selected.





values( Test_Substr('01234567890123456789', 2 ))



1

-

!



1 record(s) selected.





values( Test_Substr('01234567890123456789', 32 ))



1

-

1



1 record(s) selected.

bughunter@ru wrote:
> 8.2.2 (fixpack 9)
>
> Simple function - corrects call for substr. But doesn't work.
> Look this sample - any call with wrong Idx
>
> values( Test_Substr('01234567890123456789', -1 ))
>
> SQL0901N The SQL statement failed because of a non-severe system
> error.
> Subsequent SQL statements can be processed. (Reason "Invalid position
> or length parameters".) SQLSTATE=58004
>
> CREATE FUNCTION Test_Substr
> (vStr VARCHAR(20), Idx INTEGER
> )
> RETURNS CHARACTER(1)
> LANGUAGE SQL
> DETERMINISTIC
> CONTAINS SQL
> return
> case
> when Idx < 1 THEN '0'
> when LENGTH(vStr) < Idx THEN '1'
> when LENGTH(vStr) >= Idx THEN SUBSTR(vStr, Idx, 1)
> else 'x'
> end
> @
>
This is an incarnation of IY70983 (will be fixed in FP10).
You should get a -138 though, instead of a -901, I'll look into that.

The problem lies in overzealous "constant folding" and (in case of
variable) pre-execution.
You can try this for a workaround:
THEN SUBSTR(vStr, Idx, COALESCE(1, INT(RAND())))

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Strangely enough, this works fine on my RHEL4 Linux (2.6 kernel) DB2 8.1
FP9a! Maybe it was fixed a little early.

Phil Sherman



Serge Rielau wrote:

> This is an incarnation of IY70983 (will be fixed in FP10).
> You should get a -138 though, instead of a -901, I'll look into that.
>
> The problem lies in overzealous "constant folding" and (in case of
> variable) pre-execution.
> You can try this for a workaround:
> THEN SUBSTR(vStr, Idx, COALESCE(1, INT(RAND())))
>
> Cheers
> Serge