Re: Catalog domain not-null constraints - Mailing list pgsql-hackers

From Vik Fearing
Subject Re: Catalog domain not-null constraints
Date
Msg-id ebd90f87-f43a-4205-b838-bbb93764aa09@postgresfriends.org
Whole thread Raw
In response to Re: Catalog domain not-null constraints  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Catalog domain not-null constraints
List pgsql-hackers
On 3/22/24 00:17, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.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




pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: documentation structure
Next
From: Peter Eisentraut
Date:
Subject: Re: documentation structure