Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Correlated update causes incorrect null constraint violation #4005

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Correlated update causes incorrect null constraint violation #4005

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant