Thread: BUG #4997: Expression evaluation rules

The following bug has been logged online:

Bug reference:      4997
Logged by:          Dmitry Samokhin
Email address:      sdld@mail.ru
PostgreSQL version: 8.3.7
Operating system:   Windows 2003 Server
Description:        Expression evaluation rules
Details:

As described in section "4.2.12. Expression Evaluation Rules" in the
documentation, "the order of evaluation of subexpressions is not defined",
and the proposed solution is to use the CASE statement. Consider the
following query:

SELECT CASE WHEN TRUE THEN TRUE ELSE 1 / 0 = 1 END;

It returns 'TRUE' as expected, I don't see the 'division by zero' error, so
the ELSE branch is not evaluated at all.

Then, consider this test case:

-- Start of DDL script

CREATE OR REPLACE FUNCTION trg_mytable_after()
  RETURNS trigger AS
$BODY$
BEGIN
  IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
    IF (CASE WHEN TG_OP = 'DELETE' THEN TRUE ELSE OLD.a <> NEW.a END) THEN
      RAISE NOTICE 'OK!';
    END IF;
  END IF;

  RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

CREATE TABLE mytable
(
  a integer NOT NULL,
  CONSTRAINT pk_mytable PRIMARY KEY (a)
)
WITH (
  OIDS=FALSE
);

CREATE TRIGGER trg_mytable_after
  AFTER INSERT OR UPDATE OR DELETE
  ON mytable
  FOR EACH ROW
  EXECUTE PROCEDURE trg_mytable_after();

-- End of DDL script

INSERT INTO mytable (a) VALUES (1);
DELETE FROM mytable WHERE a = 1;

On DELETE statement, the error occurs:

ERROR record "new" is not assigned yet
DETAIL The tuple structure of a not-yet-assigned record is indeterminate

So it seems although (TG_OP = 'DELETE') is TRUE, the ELSE branch of the CASE
statement (OLD.a <> NEW.a) in the trigger procedure is still evaluated.

2009/8/20 Dmitry Samokhin <sdld@mail.ru>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A04997
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Dmitry Samokhin
> Email address: =C2=A0 =C2=A0 =C2=A0sdld@mail.ru
> PostgreSQL version: 8.3.7
> Operating system: =C2=A0 Windows 2003 Server
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Expression evaluation rules
> Details:
>
> As described in section "4.2.12. Expression Evaluation Rules" in the
> documentation, "the order of evaluation of subexpressions is not defined",
> and the proposed solution is to use the CASE statement. Consider the
> following query:
>
> SELECT CASE WHEN TRUE THEN TRUE ELSE 1 / 0 =3D 1 END;
>
> It returns 'TRUE' as expected, I don't see the 'division by zero' error, =
so
> the ELSE branch is not evaluated at all.
>
> Then, consider this test case:
>
> -- Start of DDL script
>
> CREATE OR REPLACE FUNCTION trg_mytable_after()
> =C2=A0RETURNS trigger AS
> $BODY$
> BEGIN
> =C2=A0IF TG_OP =3D 'UPDATE' OR TG_OP =3D 'DELETE' THEN
> =C2=A0 =C2=A0IF (CASE WHEN TG_OP =3D 'DELETE' THEN TRUE ELSE OLD.a <> NEW=
.a END) THEN
> =C2=A0 =C2=A0 =C2=A0RAISE NOTICE 'OK!';
> =C2=A0 =C2=A0END IF;
> =C2=A0END IF;
>
> =C2=A0RETURN NULL;
> END;
> $BODY$
> =C2=A0LANGUAGE 'plpgsql' VOLATILE
> =C2=A0COST 100;
>
> CREATE TABLE mytable
> (
> =C2=A0a integer NOT NULL,
> =C2=A0CONSTRAINT pk_mytable PRIMARY KEY (a)
> )
> WITH (
> =C2=A0OIDS=3DFALSE
> );
>
> CREATE TRIGGER trg_mytable_after
> =C2=A0AFTER INSERT OR UPDATE OR DELETE
> =C2=A0ON mytable
> =C2=A0FOR EACH ROW
> =C2=A0EXECUTE PROCEDURE trg_mytable_after();
>
> -- End of DDL script
>
> INSERT INTO mytable (a) VALUES (1);
> DELETE FROM mytable WHERE a =3D 1;
>
> On DELETE statement, the error occurs:
>
> ERROR record "new" is not assigned yet
> DETAIL The tuple structure of a not-yet-assigned record is indeterminate
>
> So it seems although (TG_OP =3D 'DELETE') is TRUE, the ELSE branch of the=
 CASE
> statement (OLD.a <> NEW.a) in the trigger procedure is still evaluated.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

Hello

it's not bug, but feature.


>
> CREATE OR REPLACE FUNCTION trg_mytable_after()
> =C2=A0RETURNS trigger AS
> $BODY$
> BEGIN
> =C2=A0IF TG_OP =3D 'UPDATE' OR TG_OP =3D 'DELETE' THEN
> =C2=A0 =C2=A0IF (CASE WHEN TG_OP =3D 'DELETE' THEN TRUE ELSE OLD.a <> NEW=
.a END) THEN
> =C2=A0 =C2=A0 =C2=A0RAISE NOTICE 'OK!';
> =C2=A0 =C2=A0END IF;
> =C2=A0END IF;
>

CASE expression isn't evaluated internally in plpgsql, but it is
transformed to SQL statement, that is evaluated by SQL procession. You
code is some like:

DECLARE result boolean;
BEGIN
   IF TG_OP =3D 'UPDATE' OR TG_OP =3D 'DELETE' THEN
     EXECUTE 'CASE WHEN $1 =3D 'DELETE' THEN true ELSE $2.a <> $3.a END'
USING TG_OP, OLD.a, OLD.b INTO result;
    IF result THEN
      RAISE NOTICE 'OK!';

Regards
Pavel Stehule

Yes... Described in "38.4. Expressions"...

To be more exact, it's neither bug nor feature. To evaluate an integral CASE
... END expression, the 'main' SQL engine needs all its parameters passed to
exist at least. But the evaluation rules (I think) remain the same both in
separate SQL queries and server procedures.

Thanks for reply,
closed.

Dmitry.