Submitted by: Steve Wiser (stevewiser)

If you run the script below it will generate 3 stored procedures to test this. Basically if you cast a date to a varchar in Dialect 1 it will return the date without the timestamp on it. However, once the stored procedure also includes a variable that is declared from a domain with a CHECK constraint on it the date casting will now include the timestamp. I am not sure why it exhibits different behaviors based on whether a variable is used with a domain that contains the check constraint. Removing the check constraint from the domain allows the date casting to not include the timestamp.

set term !! ;

create or alter procedure TEST_WORKS_AS_EXPECTED
returns (
retDate date,
retDateStr varchar(15),
retSuccess varchar(5))
AS
begin
retSuccess = 'True';

select cast('today' as date)
from rdb$database
into retDate;

retDateStr = cast(retDate as varchar(15));
end!!

create domain bool_flag as VARCHAR(5) CHARACTER SET NONE
CHECK (value is null or value in ('True', 'False'))
COLLATE NONE!!

create or alter procedure TEST_FAILS
returns (
retDate date,
retDateStr varchar(15),
retSuccess bool_flag)
AS
begin
retSuccess = 'True';

select cast('today' as date)
from rdb$database
into retDate;

retDateStr = cast(retDate as varchar(15));
end!!

create or alter procedure TEST_FAILS_FIXED
returns (
retDate date,
retDateStr varchar(50),
retSuccess bool_flag)
AS
begin
retSuccess = 'True';

select cast('today' as date)
from rdb$database
into retDate;

retDateStr = cast(retDate as varchar(50));
end!!

set term ; !!

Commits: fb89087 78f1402 FirebirdSQL/fbt-repository@584d2a9 FirebirdSQL/fbt-repository@741d346