Re: SQL standard changed behavior of ON UPDATE SET NULL/SET DEFAULT?

2012/6/16 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> I wrote:
>> Have the SQL committee simply failed to notice that in
>> whacking this text around they changed the meaning?  Which behavior is
>> actually implemented by other RDBMSes?
>
> If anyone is up for actually trying this, here is a script to test the
> behavior in question:
>
> create table pp (f1 int, f2 int, primary key (f1,f2));
> create table cmssn (f1 int, f2 int,
>    foreign key (f1,f2) references pp(f1,f2) on update set null);
> create table cmfsn (f1 int, f2 int,
>    foreign key (f1,f2) references pp(f1,f2) match full on update set null);
> create table cmssd (f1 int default 0, f2 int default 0,
>    foreign key (f1,f2) references pp(f1,f2) on update set default);
> create table cmfsd (f1 int default 0, f2 int default 0,
>    foreign key (f1,f2) references pp(f1,f2) match full on update set default);
>
> insert into pp values (11, 22);
> insert into pp values (11, 0);
> insert into pp values (0, 0);
>
> insert into cmssn values (11, 22);
> insert into cmfsn values (11, 22);
> insert into cmssd values (11, 22);
> insert into cmfsd values (11, 22);
>
> update pp set f2 = f2 + 1 where f2 > 0;
>
> select * from cmssn;
> select * from cmfsn;
> select * from cmssd;
> select * from cmfsd;
>
> In Postgres this produces
>
>  f1 | f2
> ----+----
>  11 |
> (1 row)
>
>  f1 | f2
> ----+----
>    |
> (1 row)
>
>  f1 | f2
> ----+----
>  11 |  0
> (1 row)
>
>  f1 | f2
> ----+----
>  0 |  0
> (1 row)
>
> which shows that we are self-consistent but not actually compliant with
> either old or new wordings of the spec :-(
>
> The only other SQL DB I have handy is mysql 5.5.24, which shows up
> pretty unimpressively: it gives a syntax error on the cmssd definition,
> which would be all right because the manual says the innodb storage
> engine doesn't support SET DEFAULT, except it *doesn't* give a syntax
> error for creating cmfsd.  Then, the update fails claiming that cmfsn's
> FK constraint is violated, so they evidently don't implement that case
> correctly.  After removing cmfsn, the update fails again claiming that
> cmfsd's FK constraint is violated, so yeah they are telling the truth
> when they say SET DEFAULT doesn't work.  The upshot is that only the
> MATCH SIMPLE SET NULL case works at all in current mysql, and that
> produces the result
>
> mysql> select * from cmssn;
> +------+------+
> | f1   | f2   |
> +------+------+
> | NULL | NULL |
> +------+------+
> 1 row in set (0.00 sec)
>
> so they are nulling all the referencing columns in this case, which
> matches the more recent specs but is clearly contrary to SQL92.
>
> Anybody have DB2, or something else that might be thought to be pretty
> close to spec-compliant?

I tryed in a MS SQL Server 2012 via SQLFiddle [1]. I could only create
'cmssn' and 'cmssd' tables because as I can see in [2] MS SQL Server
2012 doesn't supports MATCH syntax.

The result was:

select * from cmssn;
F1 | F2
(null) | (null)

select * from cmssd;
F1 | F2
0 | 0

The test is in [3], and there you can try other RDBMS, just create the
schema on the left panel and testing selects on the right.

[1] http://sqlfiddle.com
[2] http://msdn.microsoft.com/en-us/library/ms174979.aspx
[3] http://sqlfiddle.com/#!6/ac7db/1

Regards.
--
Dickson S. Guedes
mail/xmpp: guedes(at)guedesoft(dot)net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br