Re: Backend misfeasance for DEFAULT NULL

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

> AFAICS this is a flat-out bug too, since the per-column default should
> override the domain's default; that's certainly how it works for any
> non-null column default value. But I wasn't expecting any regression
> failures with this patch. Is it OK to change this behavior? Should I
> back-patch, or not?

Sure it's a clear-cut bug. The spec (SQL 2003 Section 11.5) clearly says the
default of the column overrides the default of the domain:

3) When a site S is set to its default value,

Case:

a) If the descriptor of S indicates that it represents a column of which some
underlying column is an identity column or a generated column, then S is
marked as unassigned.

b) If the data descriptor for the site includes a <default option>, then S is
set to the value specified by that <default option>.

c) If the data descriptor for the site includes a <domain name> that
identifies a domain descriptor that includes a <default option>, then S is
set to the value specified by that <default option>.

d) If the default value is for a column C of a candidate row for insertion
into or update of a derived table DT and C has a single counterpart column
CC in a leaf generally underlying table of DT, then S is set to the default
value of CC, which is obtained by applying the General Rules of this
Subclause.

e) Otherwise, S is set to the null value.

I would tend to be more conservative than we've been in the past with back
patching. We keep saying people should be on the most recent point release and
people shouldn't be concerned about their application breaking. But if we make
behaviour changes, even for things which are definitely bugs, we make those
fears justified.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com