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

Update column with or without parenthesis produce different results #3838

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

Update column with or without parenthesis produce different results #3838

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

Comments

@monetdb-team
Copy link

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

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.

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