Date: 2018-04-13 14:24:32 +0200
From: @swingbit
To: SQL devs <>
Version: 11.27.13 (Jul2017-SP4)
CC: @kutsurak, @njnes

Last updated: 2018-06-20 11:02:11 +0200

Comment 26365

Date: 2018-04-13 14:24:32 +0200
From: @swingbit

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36
Build Identifier:

This happens in both Jul2017 and Mar2018

Starting with these two tables, x and y:

START TRANSACTION;
CREATE TABLE "x" (
"a1" CHARACTER LARGE OBJECT,
"a2" CHARACTER LARGE OBJECT
);
COPY 5 RECORDS INTO "x" FROM stdin USING DELIMITERS '|','\n','"';
"fiets"|"damesfiets"
"fiets"|"herenfiets"
"auto"|"personenwag"
"auto"|"bedrijfsauto"
"auto"|"personenauto"
COMMIT;

START TRANSACTION;
CREATE TABLE "y" (
"paramname" CHARACTER LARGE OBJECT,
"value" CHARACTER LARGE OBJECT
);
COPY 1 RECORDS INTO "y" FROM stdin USING DELIMITERS '|','\n','"';
"1"|"something"
COMMIT;

** The following gives back 3 results:

start transaction;

insert into y values ('0','boot');
insert into y values ('0','auto');

select count(*)
from x,
(select value as a1 from y where paramname='0') as v
where x.a1 = v.a1;

rollback;

** However, the following (where the two insert statements have been merged into one statement), gives 0 results:

start transaction;

insert into y values ('0','boot'), ('0','auto');

select count(*)
from x,
(select value as a1 from y where paramname='0') as v
where x.a1 = v.a1;

rollback;

Notice that joining directly x and y behaves as expected. Only with the subselect into v, it fails.

Reproducible: Always

Comment 26369

Date: 2018-04-14 22:07:33 +0200
From: MonetDB Mercurial Repository <>

Changeset fbc3ebb69aab made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #6578

Comment 26371

Date: 2018-04-16 08:25:49 +0200
From: @kutsurak

As far as I can tell the problem appears in the subquery, but only if autocommit is off:

sql>start transaction;
auto commit mode: off
sql>insert into y values ('0','boot'), ('0','auto');
2 affected rows
sql>select * from y where paramname='0';
+-----------+-----------+
| paramname | value |
+===========+===========+
| 1 | something |
| 0 | boot |
+-----------+-----------+
2 tuples
sql>rollback;
auto commit mode: on
sql>insert into y values ('0','boot'), ('0','auto');
2 affected rows
sql>select * from y;
+-----------+-----------+
| paramname | value |
+===========+===========+
| 1 | something |
| 0 | boot |
| 0 | auto |
+-----------+-----------+
3 tuples
sql>select * from y where paramname='0';
+-----------+-------+
| paramname | value |
+===========+=======+
| 0 | boot |
| 0 | auto |
+-----------+-------+
2 tuples

I tested this in Mar2018.

Comment 26373

Date: 2018-04-16 13:15:48 +0200
From: @njnes

fixed by keeping the head seqbase stable under the first (and later) appends