BUG #5043: Stored procedure returning different results for same arguments - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5043
Logged by:          Martin Edlman
Email address:      edlman@fortech.cz
PostgreSQL version: 8.2.0
Operating system:   Linux (RHEL 4.4)
Description:        Stored procedure returning different results for same
arguments
Details:

I have a stored procedure (SP) get_schemebind_date(int, date) which looks up
a date of validity of a payment scheme. Table schemebind contains valid_from
(date), schemeid (int), contractid (int).

There are these records in schemebind
'2008-11-01', 123, 1004
'2009-09-01', 456, 1004

When I call "select get_schemebind_date(1004,'2009-09-01')" from psql I get
correct result, which is 2009-09-01.
But when I call it from within another sp (let's call it xfunc(int, date,
varchar), I get wrong result 2008-11-01. The date passed to xfunc() is then
passed to get_schemebind_date().

I enabled RAISE NOTICE to see the parameters and the result, in case I call
get_schemebind_date() from console I see
NOTICE: get_schemebind_date(1004, 2009-09-01) = 2009-09-01
In case get_schemebind_date() is called from xfunc() I see
NOTICE: get_schemebind_date(1004, 2009-09-01) = 2008-11-01

I really don't understand it. All parameters are of type date, column
valid_from is of type date as well.

If you need full code of xfunc, I can send it to you.

CREATE OR REPLACE FUNCTION get_schemebind_date(integer, date)
  RETURNS date AS
$BODY$
DECLARE
    rec RECORD;
    con ALIAS FOR $1;
    dat ALIAS FOR $2;
BEGIN
    SELECT max(valid_from) AS vf
        INTO rec
        FROM schemebind
        WHERE valid_from <= dat AND contractid = con;
    RAISE NOTICE 'get_schemebind_date(%, %) = %', con, dat, rec.vf;
    IF FOUND THEN
        RETURN rec.vf;
    END IF;
    RETURN NULL;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION get_schemebind_date(integer, date) OWNER TO postgres;