On 10/13/23 02:44, Tom Lane wrote:
Vik Fearing <v...@postgresfriends.org> writes:
On 10/12/23 15:54, Tom Lane wrote:
There's been some discussion of treating the output of such a join,
subselect, etc as being of the domain's base type not the domain
proper. That'd solve this particular issue since then we'd decide
we have to cast the base type back up to the domain type (and hence
check its constraints) before inserting the row. But that choice
just moves the surprise factor somewhere else, in that queries that
used to produce one data type now produce another one. There are
applications that this would break. Moreover, I do not think there's
any justification for it in the SQL spec.
I do not believe this is a defect of the SQL standard at all.
SQL:2023-2 Section 4.14 "Domains" clearly states "The purpose of a
domain is to constrain the set of valid values that can be stored in a
column of a base table by various operations."
So I wonder what is the standard's interpretation of
regression=# create domain dpos as integer not null check (value > 0);
CREATE DOMAIN
regression=# create table t1 (x int, d dpos);
CREATE TABLE
regression=# create view v1 as select ty.d from t1 tx left join t1 ty using (x);
CREATE VIEW
regression=# \d+ v1
View "public.v1"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+------+-----------+----------+---------+---------+-------------
d | dpos | | | | plain |
View definition:
SELECT ty.d
FROM t1 tx
LEFT JOIN t1 ty USING (x);
If we are incorrect in ascribing the type "dpos" to v1.d, where
in the spec contradicts that? (Or in other words, 4.14 might lay
out some goals for the feature, but that's just empty words if
it's not supported by accurate details in other places.)
Objection, Your Honor: Relevance.
Regardless of what the spec may or may not say about v1.d, it still
remains that nulls should not be allowed in a *base table* if the domain
says nulls are not allowed. Not mentioned in this thread but the
constraints are also applied when CASTing to the domain.
Now, to answer your straw man, this might be helpful:
SQL:2023-2 Section 11.4 <column definition> Syntax Rule 9, "If the
descriptor of D includes any domain constraint descriptors, then T shall
be a persistent base table.". Your v1 is not that and therefore
arguably illegal.
As you know, I am more than happy to (try to) amend the spec where
needed, but Erki's complaint of a null value being allowed in a base
table is clearly a bug in our implementation regardless of what we do
with views.
--
Vik Fearing
