Re: [BUGS] BUG #5053: domain constraints still leak

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> [ probably time to move this thread to -hackers ]

Tom> There is some moderately interesting reading material in section
Tom> 4.17.4 "Domain constraints" of SQL:2008. In particular, it
Tom> appears to me that the standard goes out of its way to NOT claim
Tom> that every value that "is of" a domain type satisfies the
Tom> domain's constraints. It looks to me that the implementation
Tom> they have in mind is that domain constraints are to be checked:

Tom> (1) when a value is assigned to a *table* column having that
Tom> domain type;

Tom> (2) when a value is converted to that domain type by an
Tom> *explicit* cast construct;

Tom> (3) nowhere else.

By my reading it's a bit more involved than that. In particular, if
you cast from one rowtype to another, that seems to be defined in terms
of individual casts of each column, so CAST(ROW(null) TO rowtype) where
rowtype has one column of a not-null domain type would still count as an
explicit cast to the domain.

But there's a kicker: in Subclause 6.12, <cast specification>, in the
General Rules is:

2) Case:
a) If the <cast operand> specifies NULL, then the result of CS is
the null value and no further General Rules of this Subclause
are applied.
b) If the <cast operand> specifies an <empty specification>, then
the result of CS is an empty collection of declared type TD
and no further General Rules of this Subclause are applied.
c) If SV is the null value, then the result of CS is the null
value and no further General Rules of this Subclause are
applied.

That "no further General Rules" clause implies (assuming it's not a
blatant mistake in the spec) that this rule is therefore skipped in
the case of nulls:

21) If the <cast specification> contains a <domain name> and that
<domain name> refers to a domain that contains a <domain
constraint> and if TV does not satisfy the <check constraint
definition> simply contained in the <domain constraint>, then an
exception condition is raised: integrity constraint violation.

Which would imply that you can cast a NULL to a domain type even if
that would violate a constraint. Which would pretty much leave
actual assignment to storage as being the only place for the check
to happen.

--
Andrew (irc:RhodiumToad)