Re: Catalog domain not-null constraints

On 3/22/24 00:17, Tom Lane wrote:
> Vik Fearing <vik(at)postgresfriends(dot)org> writes:
>> On 3/21/24 15:30, Tom Lane wrote:
>>> The SQL spec's answer to that conundrum appears to be "NULL is
>>> a valid value of every domain, and if you don't like it, tough".
>
>> I don't see how you can infer this from the standard at all.
>
> I believe where we got that from is 6.13 <cast specification>,
> which quoth (general rule 2):
>
> 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.
>
> In particular, that short-circuits application of the domain
> constraints (GR 23), implying that CAST(NULL AS some_domain) is
> always successful. Now you could argue that there's some other
> context that would reject nulls, but being inconsistent with
> CAST would seem more like a bug than a feature.

I think the main bug is in what you quoted from <cast specification>.

I believe that the POLA for casting to a domain is for all constraints
of the domain to be verified for ALL values including the null value.

>> As also said somewhere in that thread, I think that <cast specification>
>> short-cutting a NULL input value without considering the constraints of
>> a domain is a bug that needs to be fixed in the standard.
>
> I think it's probably intentional. It certainly fits with the lack of
> syntax for DOMAIN NOT NULL. Also, it's been like that since SQL99;
> do you think nobody's noticed it for 25 years?

Haven't we (postgres) had bug reports of similar age?

There is also the possibility that no one has noticed because major
players have not implemented domains. For example, Oracle only just got
them last year:
https://blogs.oracle.com/coretec/post/less-coding-with-sql-domains-in-23c

Anyway, I will bring this up with the committee and report back. My
proposed solution will be for CAST to check domain constraints even if
the input is NULL.
--
Vik Fearing