Re: BUG #13941: Different value "pg_constraint.consrc" for similar Check Constrait

On Wed, Feb 10, 2016 at 9:52 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Feb 10, 2016 at 9:47 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
>> On 2016-02-10 10:53:28 +0000, gsv371(at)ukr(dot)net wrote:
>> > PostgreSQL 9.5:
>> > 1.
>> > ALTER TABLE schedule ADD CONSTRAINT schedule_fact_quant_val CHECK
>> > (fact_quant BETWEEN 0 AND 23.59 AND (fact_quant - trunc(fact_quant)) <
>> > 0.6);
>> > result (pg_constraint.consrc): "(((fact_quant >= (0)::numeric) AND
>> > (fact_quant <= 23.59)) AND ((fact_quant - trunc(fact_quant)) < 0.6))"
>> > 2.
>> > ALTER TABLE schedule ADD CONSTRAINT schedule_fact_quant_val CHECK
>> > (((fact_quant >= (0)::numeric) AND (fact_quant <= 23.59)) AND
>> ((fact_quant -
>> > trunc(fact_quant)) < 0.6));
>> > result (pg_constraint.consrc): "((fact_quant >= (0)::numeric) AND
>> > (fact_quant <= 23.59) AND ((fact_quant - trunc(fact_quant)) < 0.6))"
>> > ----------------------------------
>> > PostgreSQL 9.4 - identical results!
>>
>> Why do you consider that a bug?
>>
>>
> ​Probably because of this 9.5 release note item:
>
> """
> ​
> Version 9.5 contains a number of changes that may affect compatibility
> with previous releases. Observe the following incompatibilities:
>
> Adjust operator precedence to match the SQL standard (Tom Lane)
>
> The precedence of <=, >= and <> has been reduced to match that of <, > and
> =. The precedence of IS tests (e.g., x IS NULL) has been reduced to be just
> below these six comparison operators. Also, multi-keyword operators
> beginning with NOT now have the precedence of their base operator (for
> example, NOT BETWEEN now has the same precedence as BETWEEN) whereas before
> they had inconsistent precedence, behaving like NOT with respect to their
> left operand but like their base operator with respect to their right
> operand. The new configuration parameter operator_precedence_warning can be
> enabled to warn about queries in which these precedence changes result in
> different parsing choices.
> ​"""
>
>
​To clarify - the release note probably explains why the 9.4 (with extra
parens) and 9.5 (without) constraints behave the same. It is not a bug in
the 9.5 version while it could be considered one in 9.4 that has now been
corrected... In either case it appears to be working as designed but I
haven't attempted to figure out exactly ​how the changes apply to this
specific expression.

David J.