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