Re: BUG #4997: Expression evaluation rules

The following bug has been logged online:

Bug reference: 4997
Logged by: Dmitry Samokhin
Email address: sdld(at)mail(dot)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(at)mail(dot)ru>:
>
> The following bug has been logged online:
>
> Bug reference:      4997
> Logged by:          Dmitry Samokhin
> Email address:      sdld(at)mail(dot)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.
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)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()
>  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;
>

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 = 'UPDATE' OR TG_OP = 'DELETE' THEN
EXECUTE 'CASE WHEN $1 = '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.