Thread: BUG #5053: domain constraints still leak
The following bug has been logged online: Bug reference: 5053 Logged by: Andrew Gierth Email address: andrew@tao11.riddles.org.uk PostgreSQL version: 8.5devel Operating system: FreeBSD Description: domain constraints still leak Details: Domain NOT NULL constraints (and probably other constraints too) aren't being enforced in some code paths. e.g. \pset null '<NULL>' create domain tstdom as integer not null; create table test (a tstdom); insert into test values (null); ERROR: domain tstdom does not allow null values all correct up to now, but: insert into test select (r).* from (select null::test as r) s; INSERT 0 1 oops. select * from test; a -------- <NULL> (1 row)
"Andrew Gierth" <andrew@tao11.riddles.org.uk> writes:
> Domain NOT NULL constraints (and probably other constraints too) aren't
> being enforced in some code paths. e.g.
The example you give seems to tie really closely into the debate about
whether a composite null is identically the same thing as ROW(NULL,NULL)
or not. In short, we have
regression=# create domain tstdom as integer not null;
CREATE DOMAIN
regression=# create table test (a tstdom);
CREATE TABLE
regression=# select null::test;
test
------
(1 row)
regression=# select row(null)::test;
ERROR: domain tstdom does not allow null values
It's possible to argue that in the first form, there isn't any tstdom
column there at all, so no constraint violation. So I guess this is
a case that we need to think about while debating the what-is-a-null
question.
regards, tom lane
PS: of course, domain not null constraints are horribly broken and
impossible to make behave sanely anyhow ...
On Sun, Sep 13, 2009 at 10:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Andrew Gierth" <andrew@tao11.riddles.org.uk> writes: >> Domain NOT NULL constraints (and probably other constraints too) aren't >> being enforced in some code paths. e.g. > > The example you give seems to tie really closely into the debate about > whether a composite null is identically the same thing as ROW(NULL,NULL) > or not. =A0In short, we have It seems like regardless of this discussion you oughtn't to be able to store a NULL into that table column. But maybe I'm just confused. ...Robert
Robert Haas <robertmhaas@gmail.com> writes:
> It seems like regardless of this discussion you oughtn't to be able to
> store a NULL into that table column. But maybe I'm just confused.
The system is relying on the not-unreasonable assumption that if it
extracts a column of type X from someplace, what it has is a valid value
of type X. Depending on what we decide about the whole composite-null
mess, maybe we will be forced to abandon that assumption ... but I'm
sure not going to do so until my back is to the wall.
regards, tom lane
On Mon, Sep 14, 2009 at 10:22:34AM -0400, Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > It seems like regardless of this discussion you oughtn't to be able to > > store a NULL into that table column. But maybe I'm just confused. > > The system is relying on the not-unreasonable assumption that if it > extracts a column of type X from someplace, what it has is a valid value > of type X. Yup; the deeper problem seems to be that the table was created as: create table test (a tstdom); and not as: create table test (a tstdom not null); which is how you seem to be treating it. > Depending on what we decide about the whole composite-null > mess, maybe we will be forced to abandon that assumption ... but I'm > sure not going to do so until my back is to the wall. There seems to be a little space yet! This whole issue seems only distantly related to the treatment of null rows to me. I think PG has got its semantics confused along the way somewhere and things need tweaking. The only way I can get it all to work nicely in my head is if ROW(NULL) evaluates to a NULL value (and not a row containing a NULL value, as it does at the moment) and the NULL/NOT NULL constraint on the CREATE DOMAIN is used somehow for the nullness constraint of any columns using this domain. It's the second part that seems to be more critical, but there are various ways of interpreting the meaning. I'm tempted to say that the nullness specified in the domain puts a bound on the amount of nullness available--i.e. it would be impossible to create a nullable column from a domain that specified NOT NULL. The reason it's only a "limit" is that it seems useful to be able to say that a normally nullable domain can't be null for this column in this table. Not sure if this is what people want though. You then get into fun cases like: create domain tstdom as integer; create domain tstdom2 as tstdom; -- Sam http://samason.me.uk/
On Mon, Sep 14, 2009 at 10:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> It seems like regardless of this discussion you oughtn't to be able to >> store a NULL into that table column. =A0But maybe I'm just confused. > > The system is relying on the not-unreasonable assumption that if it > extracts a column of type X from someplace, what it has is a valid value > of type X. =A0Depending on what we decide about the whole composite-null > mess, maybe we will be forced to abandon that assumption ... but I'm > sure not going to do so until my back is to the wall. I haven't read the code in this area, but for what it's worth, I guess I lean toward the view that treating a row of NULLs as being the same thing as an undecorated NULL does not make very much sense. If I have a table row which contains (1, NULL, NULL) and I update the first column to be NULL, I feel like I now have (NULL, NULL, NULL), not just NULL. Every other programming language I'm aware of makes this distinction - for good reasons - and I don't really see any reason why SQL should do anything different. Under that view, null::test is not itself a test, but denotes the absence of one. Trying to store this value in a table can either fail outright (on the theory that you can't store the absence of something in a table), or else we can - as a special case - treat assignment from null to an actual object as a request to assign null to each column (which will fail if there exists a column into which a NULL of the associated column type can't be stored). ...Robert
On Mon, Sep 14, 2009 at 11:16:23AM -0400, Robert Haas wrote:
> I haven't read the code in this area, but for what it's worth, I guess
> I lean toward the view that treating a row of NULLs as being the same
> thing as an undecorated NULL does not make very much sense.
I agree; when compared to most languages it doesn't. When compared
to the semantics of the other operators in SQL it gets better. I
personally think PG should strive to be internally consistent rather
than consistency with other (non-SQL based) languages.
> If I have
> a table row which contains (1, NULL, NULL) and I update the first
> column to be NULL, I feel like I now have (NULL, NULL, NULL), not just
> NULL. Every other programming language I'm aware of makes this
> distinction - for good reasons - and I don't really see any reason why
> SQL should do anything different.
I'm not aware of any other language that does the automatic "lifting"
(to borrow nomenclature from Haskell) that SQL does, allowing NULL
appear in *every* type. Java, for example, has null references,
but these are very different creatures from nulls in databases--the
programmer has to explicitly deal with them all the time and also they
only apply to references. Taken another way, each object in a normal
imperative language has its own identity, but in a database two rows
that "look" the same are the same. Thirdly, IS NULL is defined to look
"inside" composite values to see if they're "really" null. Its these
differences in semantics that seem to make it all OK.
> Under that view, null::test is not itself a test, but denotes the
> absence of one.
OK, but how can you distinguish NULL from ROW(NULL,NULL)?
SELECT v IS NULL, v.a, v.b
FROM (SELECT NULL, NULL) v(a,b);
Would appear to return the same thing if ROW(NULL,NULL) evaluated to
NULL or not. The only time it would show up is when you're trying to
save the value into a table and I think this would tend to do the right
thing more often. For example:
INSERT INTO t (id,rv)
SELECT f.id, b
FROM foo f
LEFT JOIN bar b ON (f.id = b.id);
Would fail if any bar's didn't exist, whereas the current behavior is
to insert a row with rv containing all null values. You can't test for
this case because IS NULL would return the "wrong" thing as it looks
inside composites.
--
Sam http://samason.me.uk/
Sam Mason <sam@samason.me.uk> wrote: > the deeper problem seems to be that the table was created as: > > create table test (a tstdom); > > and not as: > > create table test (a tstdom not null); Given that tstdom is declared as NOT NULL, is this difference considered a *feature* or is it an implementation quirk? -Kevin
On Mon, Sep 14, 2009 at 10:54:07AM -0500, Kevin Grittner wrote: > Sam Mason <sam@samason.me.uk> wrote: > > the deeper problem seems to be that the table was created as: > > > > create table test (a tstdom); > > > > and not as: > > > > create table test (a tstdom not null); > > Given that tstdom is declared as NOT NULL, is this difference > considered a *feature* or is it an implementation quirk? That's why I pointed it out! Based on my reading of the SQL spec (and reading about Codd's descriptions of domains) I'd say it was a bug/implementation quirk. -- Sam http://samason.me.uk/
Sam Mason wrote:
> On Mon, Sep 14, 2009 at 10:54:07AM -0500, Kevin Grittner wrote:
> > Sam Mason <sam@samason.me.uk> wrote:
> > > the deeper problem seems to be that the table was created as:
> > >
> > > create table test (a tstdom);
> > >
> > > and not as:
> > >
> > > create table test (a tstdom not null);
> >
> > Given that tstdom is declared as NOT NULL, is this difference
> > considered a *feature* or is it an implementation quirk?
>
> That's why I pointed it out!
>
> Based on my reading of the SQL spec (and reading about Codd's
> descriptions of domains) I'd say it was a bug/implementation quirk.
Do we need a TODO for this item? Also, I see this odd behavior even
without domains:
test=> CREATE TYPE xx AS (x INT);
CREATE TYPE
test=> CREATE TABLE test4(col1 xx NOT NULL);
CREATE TABLE
test=> INSERT INTO test4 VALUES (ROW(NULL));
INSERT 0 1
test=> SELECT * FROM test4 WHERE col1 IS NULL;
col1
------
()
(1 row)
Here I am illustrating that NOT NULL and IS NULL have different ideas of
what a NULL is? That seems odd too.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Mon, Sep 21, 2009 at 12:31 PM, Bruce Momjian <bruce@momjian.us> wrote: > Sam Mason wrote: >> On Mon, Sep 14, 2009 at 10:54:07AM -0500, Kevin Grittner wrote: >> > Sam Mason <sam@samason.me.uk> wrote: >> > > the deeper problem seems to be that the table was created as: >> > > >> > > =A0 create table test (a tstdom); >> > > >> > > and not as: >> > > >> > > =A0 create table test (a tstdom not null); >> > >> > Given that tstdom is declared as NOT NULL, is this difference >> > considered a *feature* or is it an implementation quirk? >> >> That's why I pointed it out! >> >> Based on my reading of the SQL spec (and reading about Codd's >> descriptions of domains) I'd say it was a bug/implementation quirk. > > Do we need a TODO for this item? =A0Also, I see this odd behavior even > without domains: > > =A0 =A0 =A0 =A0test=3D> CREATE TYPE xx AS (x INT); > =A0 =A0 =A0 =A0CREATE TYPE > =A0 =A0 =A0 =A0test=3D> CREATE TABLE test4(col1 xx NOT NULL); > =A0 =A0 =A0 =A0CREATE TABLE > =A0 =A0 =A0 =A0test=3D> INSERT INTO test4 VALUES (ROW(NULL)); > =A0 =A0 =A0 =A0INSERT 0 1 > =A0 =A0 =A0 =A0test=3D> SELECT * FROM test4 WHERE col1 IS NULL; > =A0 =A0 =A0 =A0 col1 > =A0 =A0 =A0 =A0------ > =A0 =A0 =A0 =A0 () > =A0 =A0 =A0 =A0(1 row) > > Here I am illustrating that NOT NULL and IS NULL have different ideas of > what a NULL is? =A0That seems odd too. I think what you're demonstrating is that there is a difference between a NULL, and a row consisting of a single NULL. We've had some dispute (on this thread) about whether that ought to be the case, but this is certainly a lot less weird and more debatable than the domain example, at least IMO. ...Robert