Skip site navigation (1) Skip section navigation (2)

BUG #4997: Expression evaluation rules

From: "Dmitry Samokhin" <sdld(at)mail(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4997: Expression evaluation rules
Date: 2009-08-20 07:48:12
Message-ID: 200908200748.n7K7mCwD076227@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

Responses

pgsql-bugs by date

Next:From: Pavel StehuleDate: 2009-08-20 09:33:15
Subject: Re: BUG #4997: Expression evaluation rules
Previous:From: Alvaro HerreraDate: 2009-08-20 03:27:15
Subject: Re: fillfactor hides autovacuum parameters in 8.4.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group