Cannot update a field with a count query based on an other table

[monetdb-team]

Date: 2011-11-09 17:48:40 +0100
From: sellam
To: SQL devs <>
Version: 11.5.1 (Aug2011) [obsolete]
CC: @njnes

Last updated: 2011-11-22 13:55:56 +0100

Comment 16540

Date: 2011-11-09 17:48:40 +0100
From: sellam

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.9.2.23) Gecko/20110927 Fedora/3.6.23-1.fc14 Firefox/3.6.23
Build Identifier:

The bug can be created as follows:

create table ranges(
x_min decimal(8,5),
y_min decimal(7,5),
width integer,
nb integer
);

create table trips (
tripid bigint not null,
x decimal(8,5) not null,
y decimal(8,5) not null,
time bigint not null
);

UPDATE ranges SET nb = (SELECT count(*) FROM trips T
WHERE T.x between x_min and x_min + width
AND T.y between y_min and y_min + width);

Returns:

MAPI = (monetdb) /tmp/.s.monetdb.50000
ACTION= read_line
QUERY = UPDATE ranges SET nb = (SELECT count(*) FROM trips T
WHERE T.x between x_min and x_min + width
AND T.y between y_min and y_min + width);
ERROR = !Connection terminated

Reproducible: Always

Actual Results:

Error message:
MAPI = (monetdb) /tmp/.s.monetdb.50000
ACTION= read_line
QUERY = UPDATE ranges SET nb = (SELECT count(*) FROM trips T
WHERE T.x between x_min and x_min + width
AND T.y between y_min and y_min + width);
ERROR = !Connection terminated

Expected Results:

An update

bash-4.1$ hg branch
Aug2011
bash-4.1$ hg id
0299b070e196 (Aug2011) tip

Comment 16541

Date: 2011-11-09 19:41:38 +0100
From: @grobian

can you check your merovingian.log for some informative crash messages, perhaps?

Comment 16542

Date: 2011-11-10 11:19:18 +0100
From: sellam

Hi, unfortunately the crash message is not very explicit:

2011-11-09 17:44:39 MSG merovingian[14936]: database 'testbug' (15460) was killed by signal SIGSEGV
2011-11-09 17:47:55 MSG merovingian[14936]: database 'testbug' has crashed after start on 2011-11-09 17:43:55, attempting restart, up min/avg/max: 0s/0s/0s, crash average: 1.00 0.30 0.10 (3-0=3)

Precision: in the provided example, the tables are empty before the UPDATE statement. The message is the same when the tables are populated

Comment 16546

Date: 2011-11-10 22:31:30 +0100
From: @njnes

Changeset 4e67d716ffbf made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=4e67d716ffbf

Changeset description:

fix bug #2928 (update using correlated count query)

Comment 16549

Date: 2011-11-12 23:33:23 +0100
From: @njnes

fixed a bit cleaner now. In case of correlated updates (and subqueries) in the selection phase, we should use the inner query columns with in the aggregates.

Comment 16550

Date: 2011-11-13 09:28:09 +0100
From: @njnes

Changeset 64efaa8ac570 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=64efaa8ac570

Changeset description:

merge of fix for bug #2928

Comment 16551

Date: 2011-11-13 09:29:30 +0100
From: @njnes

Changeset 5acc6a38e932 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=5acc6a38e932

Changeset description:

merged fix for bug #2928 (from Dec2011 branch)

Comment 16552

Date: 2011-11-13 09:35:10 +0100
From: @njnes

added test file update_with_count.Bug-2928.sql

Comment 16553

Date: 2011-11-13 09:36:28 +0100
From: @njnes

Changeset b691e9f8af19 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=b691e9f8af19

Changeset description:

added test for bug #2928

Comment 16568

Date: 2011-11-22 13:55:56 +0100
From: @grobian

Fixed in Aug2011-SP3