Re: Fix optimization of foreign-key on update actions

>>>>> "Laurenz" == Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:

Laurenz> Andrew Gierth wrote:
>> SQL2016, 15.17 Execution of referential actions
>>
>> 10) If a non-null value of a referenced column RC in the referenced
>> table is updated to a value that is distinct from the current value
>> of RC, then,
>>
>> [snip all the stuff about how ON UPDATE actions work]
>>
>> does that "is distinct from" mean that IS DISTINCT FROM would be true,
>> or does it mean "is in some way distinguishable from"? Nothing I can see
>> in the spec suggests the latter.

Laurenz> My 2003 standard defines, and even condescends to be informal:

Laurenz> 3.1.6.8 distinct (of a pair of comparable values): Capable of
Laurenz> being distinguished within a given context. Informally, not
Laurenz> equal, not both null. A null value and a non-null value are
Laurenz> distinct.

Hrm. SQL2016 has similar language which I previously missed, but I don't
think it actually helps:

3.1.6.9 distinct (of a pair of comparable values)
capable of being distinguished within a given context

NOTE 8 -- Informally, two values are distinct if neither
is null and the values are not equal. A null value and a
non- null value are distinct. Two null values are not
distinct. See Subclause 4.1.5, "Properties of distinct",
and the General Rules of Subclause 8.15, "<distinct
predicate>".

Two values which are sql-equal but not identical, such as two strings in
a case-insensitive collation that differ only by case, are
distinguishable in some contexts but not others, so what context
actually applies to the quoted rule?

I think the only reasonable interpretation is that it should use the
same kind of distinctness that is being used by the unique constraint
and the equality comparison that define whether the FK is satisfied.

--
Andrew.