Date: 2016-05-09 12:19:30 +0200
From: Richard Hughes <<richard.monetdb>>
To: SQL devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: @njnes

Last updated: 2016-12-21 13:08:04 +0100

Comment 22132

Date: 2016-05-09 12:19:30 +0200
From: Richard Hughes <<richard.monetdb>>

Build is Jul2015 df58cfe6f8b9

To reproduce, starting from a blank database:

create table foo1 (id bigint not null, t timestamp not null);
create table foo2 as select * from foo1 with no data;
insert into foo1 values (4283,timestamp '2016-5-3');
insert into foo1 values (4284,timestamp '2016-5-6');
insert into foo2 values (4284,timestamp '2016-5-7');
update foo1 set (t)=(select t from foo2 where foo2.id=foo1.id);

The final line produces "UPDATE: NOT NULL constraint violated for column 'foo1.t'".

This used to work. I'm bisecting it right now...

Comment 22133

Date: 2016-05-09 13:05:43 +0200
From: Richard Hughes <<richard.monetdb>>

The first bad revision is:
changeset: 57902:2153348bd11a
branch: Jul2015
user: Niels Nes niels@cwi.nl
date: Wed Nov 25 17:26:56 2015 +0100
summary: fix bug #3838, ie handle outer joins with updates

Comment 22134

Date: 2016-05-09 13:26:03 +0200
From: Richard Hughes <<richard.monetdb>>

OK, reading through bug #3838 comment 2 implies that the old behaviour was wrong and the new behaviour is right.

That might be true - this syntax is not supported by any other vendor that I know of and I don't have access to the ANSI SQL spec in order to check.

A workaround of "update foo1 set (t)=(select t from foo2 where foo2.id=foo1.id) where exists(select * from foo2 where foo2.id=foo1.id);" does do what I want (although slightly less efficiently than before).

Please close/notabug this bug if the new behaviour is the correct behaviour, although if you could suggest a more efficient way of doing an inner join in an UPDATE statement as well then I'd appreciate it.

Comment 22235

Date: 2016-07-05 19:02:43 +0200
From: @njnes

we now also support a from clause within an update statement,

update foo1 set t = v from etc.