Date: 2015-10-27 15:30:26 +0100
From: @yzchang
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: @njnes, @yzchang

Last updated: 2016-01-15 11:37:47 +0100

Comment 21410

Date: 2015-10-27 15:30:26 +0100
From: @yzchang

The following two query sets differ only in the use of parenthesis:
query set1: "SET (insiderange) = ("
query set2: "SET insiderange = ("

But query set1 correctly updates 1 tuple (the 2nd tuple), while query set2 incorrectly updates both tuples.
See the lined query results after each update.
This might be because, in query set 1, the assignment in the UPDATE stmt with parenthesis is a "subquery";
while in query set 2, the assignment in the UPDATE stmt without parenthesis is a "search_condition".

Only run the SELECT query in the assignment returns correct results.

-- query set 1: WITH parenthesis around "insiderange": UPDATE correctly updates one tuple
CREATE TABLE datepoint (mydate DATE, insiderange BOOLEAN DEFAULT FALSE, rangename CHAR(8));
INSERT INTO datepoint (mydate) VALUES ('2012-05-09');
INSERT INTO datepoint (mydate) VALUES ('2012-03-09');

CREATE TABLE daterange (startdate DATE, enddate DATE, name CHAR(8));
INSERT INTO daterange (startdate, enddate, name) VALUES ('2012-03-01','2012-03-31','A');

UPDATE datepoint
SET (insiderange) = (
SELECT TRUE
FROM (
SELECT startdate, enddate, name
FROM daterange
) AS B
WHERE datepoint.mydate >= B.startdate
AND datepoint.mydate <= B.enddate
);
sql>SELECT * FROM datepoint;
+------------+-------------+-----------+
| mydate | insiderange | rangename |
+============+=============+===========+
| 2012-05-09 | false | null |
| 2012-03-09 | true | null |
+------------+-------------+-----------+
2 tuples (1.442ms)

DROP TABLE datepoint;
DROP TABLE daterange;

-- query set 2: WITHOUT parenthesis around "insiderange": UPDATE updates too many tuples
CREATE TABLE datepoint (mydate DATE, insiderange BOOLEAN DEFAULT FALSE, rangename CHAR(8));
INSERT INTO datepoint (mydate) VALUES ('2012-05-09');
INSERT INTO datepoint (mydate) VALUES ('2012-03-09');

CREATE TABLE daterange (startdate DATE, enddate DATE, name CHAR(8));
INSERT INTO daterange (startdate, enddate, name) VALUES ('2012-03-01','2012-03-31','A');

UPDATE datepoint
SET insiderange = (
SELECT TRUE
FROM (
SELECT startdate, enddate, name
FROM daterange
) AS B
WHERE datepoint.mydate >= B.startdate
AND datepoint.mydate <= B.enddate
);
sql>SELECT * FROM datepoint;
+------------+-------------+-----------+
| mydate | insiderange | rangename |
+============+=============+===========+
| 2012-05-09 | true | null |
| 2012-03-09 | true | null |
+------------+-------------+-----------+
2 tuples (1.337ms)

DROP TABLE datepoint;
DROP TABLE daterange;

Comment 21411

Date: 2015-10-27 15:32:41 +0100
From: MonetDB Mercurial Repository <>

Changeset 48ad5fe96b49 made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for Bug #3838

Comment 21423

Date: 2015-10-30 22:23:37 +0100
From: @njnes

both queries should always update 2 values. As there is not sub-part of the update limiting the rows. So when the select true query fails (is empty) a null
value should be 'set'. So both queries are wrong....

Comment 21548

Date: 2015-11-17 21:05:34 +0100
From: @yzchang

Hai Niels, the subquery "(SELECT TRUE ...);" returns a single row:

sql>SELECT TRUE FROM ( SELECT startdate, enddate, name FROM daterange ) AS B, datepoint WHERE datepoint.mydate >= B.startdate AND datepoint.mydate <= B.enddate;
+--------------+
| single_value |
+==============+
| true |
+--------------+
1 tuple (3.240ms)

Then the queries are basically:

UPDATE datepoint SET (insiderange) = (SELECT TRUE);
UPDATE datepoint SET insiderange = (SELECT TRUE);

Shouldn't they update both records in DATEPOINT and set INSIDERANGE to TRUE?

Comment 21591

Date: 2015-11-25 17:27:01 +0100
From: MonetDB Mercurial Repository <>

Changeset 2153348bd11a 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=2153348bd11a

Changeset description:

fix bug #3838, ie handle outer joins with updates

Comment 21598

Date: 2015-11-26 08:47:56 +0100
From: @njnes

fixed, ie added proper left joins.