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

Incorrect behavior with subqueries in DELETE #3315

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

Incorrect behavior with subqueries in DELETE #3315

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

Comments

@monetdb-team
Copy link

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

Date: 2013-06-26 13:08:12 +0200
From: Pete Hollobon <>
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2013-09-27 13:47:18 +0200

Comment 18892

Date: 2013-06-26 13:08:12 +0200
From: Pete Hollobon <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.116 Safari/537.36
Build Identifier:

When using a subquery in a DELETE, if one references a column that does not exist in the subquery context, but does exist in the outer table being deleted from, strange things happen:

Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP2)
Database: MonetDB v11.15.7 (Feb2013-SP2), 'mapi:monetdb://glx:50000/test'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>create table x (a integer);
operation successful (1.714ms)
sql>create table y (b integer);
operation successful (1.790ms)
sql>insert into x values (1), (2), (3), (4);
4 affected rows (1.448ms)
sql>insert into y values (1), (2), (3), (4);
4 affected rows (1.648ms)
sql>select * from x;
+------+
| a |
+======+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 tuples (0.849ms)
sql>select * from y;
+------+
| b |
+======+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 tuples (0.920ms)
sql>select a from y;
SELECT: identifier 'a' unknown
sql>delete from x where a in (select nonexistant from y);
SELECT: identifier 'nonexistant' unknown
sql>delete from x where a in (select a from y);
4 affected rows (35.053ms)
sql>select * from x;
+---+
| a |
+===+
+---+
0 tuples (1.007ms)
sql>insert into x values (1), (2), (3), (4);
4 affected rows (1.272ms)
sql>delete from x where a in (select a from y where a < 10);
8 affected rows (3.040ms)
sql>select * from x;
+---+
| a |
+===+
+---+
0 tuples (0.506ms)

Reproducible: Always

Comment 18893

Date: 2013-06-26 13:14:18 +0200
From: Pete Hollobon <>

Actually, thinking about it, most of that is normal - the only thing that seems odd is the row count here:

sql>delete from x where a in (select a from y where a < 10);
8 affected rows (3.040ms)

Comment 18895

Date: 2013-06-26 13:49:36 +0200
From: @njnes

Indeed only the deleted row count of 8 seems is wrong (we cannot delete more rows than inserted).

Comment 18896

Date: 2013-06-26 13:53:49 +0200
From: Pete Hollobon <>

Interestingly that count increases every time one inserts more rows into the table and runs the delete again. It looks like it's including rows marked as deleted in the count.

Comment 18963

Date: 2013-07-31 13:46:50 +0200
From: MonetDB Mercurial Repository <>

Changeset 2ee5dc3af6c5 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=2ee5dc3af6c5

Changeset description:

fixed bug #3315, bailout in pushselect down on tinter

Comment 18964

Date: 2013-07-31 13:47:20 +0200
From: @njnes

fixed

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