Number of parentheses in check constraints affected by operator_precedence_warning - Mailing list pgsql-hackers

Hi,

We noticed on PostgreSQL 9.5.3 that the number of parentheses in check
constraints expressions
vary depending on the setting of operator_precedence_warning.

-- The following reproduces this issue which we first saw by restoring
into 9.5.3 a 9.4.8 dump
on two servers which had different operator_precedence_warning.

SET operator_precedence_warning = off; -- on would yield a different
number of parentheses

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 INT);

-- 1) Check Constraints

ALTER TABLE t1  ADD CONSTRAINT cc1 CHECK ((col1 IS NULL) OR (((col2 IS NULL) AND (col3
IS NULL)) AND (col4 IS NULL))),  ADD CONSTRAINT cc2 CHECK (((col1 IS NOT NULL) OR (col2 IS NOT NULL)) OR
(col3 IS NOT NULL));


SELECT conname, consrc FROM pg_constraint WHERE conrelid = 't1'::regclass;

-- With  9.5.3 operator_precedence_warning = on (or pg 9.4)
-- cc1 ((col1 IS NULL) OR (((col2 IS NULL) AND (col3 IS NULL)) AND (col4
IS NULL)))
-- cc2 (((col1 IS NOT NULL) OR (col2 IS NOT NULL)) OR (col3 IS NOT NULL))

-- With 9.5.3 operator_precedence_warning = off
-- cc1 ((col1 IS NULL) OR ((col2 IS NULL) AND (col3 IS NULL) AND (col4 IS
NULL)))
-- cc2 ((col1 IS NOT NULL) OR (col2 IS NOT NULL) OR (col3 IS NOT NULL))


-- 2) WHEN condition on triggers also have this issue, for example:

DROP FUNCTION IF EXISTS f1();

CREATE FUNCTION f1() RETURNS trigger   LANGUAGE plpgsql   AS $$
BEGIN
END;
$$;

CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW WHEN (((((new.col1 IS
DISTINCT FROM old.col1) OR (new.col2 IS DISTINCT FROM old.col2)) OR
((new.col3 IS NOT NULL) AND (new.col3 IS DISTINCT FROM old.col3))) OR
((new.col3 IS NULL) AND (new.col3 IS DISTINCT FROM old.col3)))) EXECUTE
PROCEDURE f1();

We got these results on Windows x64.

Thanks,
Jean-Pierre Pelletier