VALUES multiple inserts error

[monetdb-team]

Date: 2017-06-29 07:35:26 +0200
From: @mlkersten
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2017-08-02 10:21:22 +0200

Comment 25401

Date: 2017-06-29 07:35:26 +0200
From: @mlkersten

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:53.0) Gecko/20100101 Firefox/53.0
Build Identifier:

Insertion of VALUES based on multiple scalar subqueries are silently ignored.
Test file added

Reproducible: Always

Steps to Reproduce:

START TRANSACTION;

create table data(i integer);
insert into data values(0),(1),(2);

create table multiples(i integer);
insert into multiples VALUES((select count(*) from data)), ((select count(distinct i) from data));
select * from multiples;

insert into multiples VALUES((select count(*) from data));
insert into multiples VALUES((select count(distinct i) from data));
select * from multiples;
ROLLBACK;

Actual Results:

sql>insert into multiples VALUES((select count() from data)), ((select count(distinct i) from data));
...
sql>select * from multiples;
+---+
| i |
+===+
sql>insert into multiples VALUES((select count() from data));
1 affected row (0.917ms)
sql>insert into multiples VALUES((select count(distinct i) from data));
1 affected row (1.069ms)
sql>select * from multiples;
+------+
| i |
+======+
| 3 |
| 3 |
+------+
+---+

Comment 25402

Date: 2017-06-29 09:30:08 +0200
From: @sjoerdmullender

This syntax is not supported. Is it at all legal according to the standard?

The syntax is
INSERT INTO table SELECT ...;
You can use a UNION to insert from multiple SELECT queries in one go.

Comment 25414

Date: 2017-07-05 21:19:47 +0200
From: @njnes

the standard seems to allow this as long as the list holds single values, ie
a subquery which results in a single value is allowed.

Added a fix for this, ie handle value lists with queries.

Comment 25415

Date: 2017-07-05 22:12:35 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=2cbddfe7dcb8

Changeset description:

handle subqueries in value lists, solves bug #6336

Comment 25429

Date: 2017-07-06 15:26:44 +0200
From: MonetDB Mercurial Repository <>

Changeset c162d873d44a made by Martin Kersten mk@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=c162d873d44a

Changeset description:

Addition of bug #6336

Comment 25430

Date: 2017-07-06 15:26:50 +0200
From: MonetDB Mercurial Repository <>

Changeset 37aeb1b76bbf made by Martin Kersten mk@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=37aeb1b76bbf

Changeset description:

Addition of bug #6336

Comment 25534

Date: 2017-08-02 10:21:22 +0200
From: @sjoerdmullender

Fixed in Jul2017-SP1 release.