Thread: PL/PgSQL bug?

Hi all.

I have noticed a strange bug/feature in PL/PgSQL language. Whenever I pass 'null' as one of the parameters, every
argumentbecomes 'null'. 

For example:

CREATE FUNCTION div_mod( int4, text, int4, bool, int2 ) RETURNS int4 AS '
BEGIN
IF $1 ISNULL THEN
  RETURN 2;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';

Now perform:
mercury# select div_mod( 1, 'Test', null, 't' 10 );
 div_mod
-----------
             2

Although the function is called with $1 = 1, it is 'null' in this case. Actually all $n parameters are 'null'.
PostgreSQLversion is: 

mercury=# select version();
                        version
--------------------------------------------------------
 PostgreSQL 7.0.2 on alpha-dec-osf4.0f, compiled by cc
(1 row)

Is this a bug or a feature?

Nix.

On Wed, 25 Jul 2001, Nikola Milutinovic wrote:

> Hi all.
>
> I have noticed a strange bug/feature in PL/PgSQL language. Whenever I pass 'null' as one of the parameters, every
argumentbecomes 'null'. 
>
> For example:
>
> CREATE FUNCTION div_mod( int4, text, int4, bool, int2 ) RETURNS int4 AS '
> BEGIN
> IF $1 ISNULL THEN
>   RETURN 2;
> END IF;
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
>
> Now perform:
> mercury# select div_mod( 1, 'Test', null, 't' 10 );
>  div_mod
> -----------
>              2
>
> Although the function is called with $1 = 1, it is 'null' in this case. Actually all $n parameters are 'null'.
PostgreSQLversion is: 
>
> mercury=# select version();
>                         version
> --------------------------------------------------------
>  PostgreSQL 7.0.2 on alpha-dec-osf4.0f, compiled by cc
> (1 row)
>
> Is this a bug or a feature?

Bug or feature? You pick.

The argument would be something like, "if an argument is null, you're
saying you don't know what it is; therefore, how could we predict the
outcome of this set of arguments, one of which is unknown." It's very
SQL-like (NULLs meanining 'unknown', not just 'blank').

However, it does make for painful functional programming.

In 7.1, this is fixed (or if you thought it wasn't broken, it's
*changed* -- though you can specify in 7.1 to use the old,
NULL-as-utterly-unknown meaning for function arguments).

In 7.0.x, you could COALESCE or CASE your NULL to something else, then
have your function handle that.

hth,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

This is a well known bug/feature.  You can find plenty of discussion of it
in the mailing list archives.
Fixed in version 7.1.x.

> -----Original Message-----
> From:    Nikola Milutinovic [SMTP:Nikola.Milutinovic@ev.co.yu]
> Sent:    Wednesday, July 25, 2001 8:59 AM
> To:    PostgreSQL general
> Subject:    [GENERAL] PL/PgSQL bug?
>
> Hi all.
>
> I have noticed a strange bug/feature in PL/PgSQL language. Whenever I pass
> 'null' as one of the parameters, every argument becomes 'null'.
>
> For example:
>
> CREATE FUNCTION div_mod( int4, text, int4, bool, int2 ) RETURNS int4 AS '
> BEGIN
> IF $1 ISNULL THEN
>   RETURN 2;
> END IF;
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
>
> Now perform:
> mercury# select div_mod( 1, 'Test', null, 't' 10 );
>  div_mod
> -----------
>              2
>
> Although the function is called with $1 = 1, it is 'null' in this case.
> Actually all $n parameters are 'null'. PostgreSQL version is:
>
> mercury=# select version();
>                         version
> --------------------------------------------------------
>  PostgreSQL 7.0.2 on alpha-dec-osf4.0f, compiled by cc
> (1 row)
>
> Is this a bug or a feature?
>
> Nix.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly