Обсуждение: Number of parentheses in check constraints affected by operator_precedence_warning
Number of parentheses in check constraints affected by operator_precedence_warning
От
Jean-Pierre Pelletier
Дата:
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
Jean-Pierre Pelletier <jppelletier@e-djuster.com> writes: > 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. Hmm, yeah, another place that needs to look through AEXPR_PAREN nodes :-(. Fixed, thanks for the report! regards, tom lane