Re: BUG #4997: Expression evaluation rules

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
>