create procedure p
returns ( res varchar(10) )
res = null;
res = '0123456789';
select substring(res from 1 for 5) from p order by 1; -- success
select substring(res from 1 for 5) from p order by 1; -- error
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.
string right truncation.
The problem seems to be caused by the fact that the DSC_null flag is analyzed at both prepare-time and runtime but descriptors can be persistent and thus keep the runtime value until the next time.
In this particular case, the first select call sets DSC_null for the procedure's output and the second select call checks this flag during prepare and describes the output as VARCHAR(1) instead of VARCHAR(10), then this short length is used to prepare the sort buffer and at runtime string truncation happens, because a CHAR(10) string cannot be assigned to a CHAR(1) buffer.