Transactional/multi-connection issues with merge tables

[monetdb-team]

Date: 2017-05-30 15:32:20 +0200
From: Richard Hughes <<richard.monetdb>>
To: SQL devs <>
Version: 11.25.21 (Dec2016-SP4)
CC: @njnes

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

Comment 25357

Date: 2017-05-30 15:32:20 +0200
From: Richard Hughes <<richard.monetdb>>

Build is Jul2017 24dd2ff6c348 Reproducible: always.

Starting with a new, blank database:

open new mclient instance 'sql1' here
sql1>create table sub1 (i int);
sql1>create table sub2 (i int);
sql1>insert into sub1 values (10),(20);
sql1>insert into sub2 values (30),(40);
sql1>analyze sys.sub1;
sql1>analyze sys.sub2;
sql1>alter table sub1 set read only;
sql1>alter table sub2 set read only;
sql1>create merge table mt as select * from sub1 with no data;
sql1>alter table mt add table sub1;
sql1>alter table mt add table sub2;
sql1>plan select * from mt where i between 25 and 45;
+--------------------------------------------------------+
| rel |
+========================================================+
| project ( |
| | select ( |
| | | table(sys.sub2) [ mt.i, mt.%TID% NOT NULL ] COUNT |
| | ) [ int "25" <= mt.i <= int "45" ] |
| ) [ mt.i ] |
+--------------------------------------------------------+

open new mclient instance 'sql2' here
sql2>plan select * from mt where i between 25 and 45;
+--------------------------------------------------------+
| rel |
+========================================================+
| project ( |
| | select ( |
| | | table(sys.sub2) [ mt.i, mt.%TID% NOT NULL ] COUNT |
| | ) [ int "25" <= mt.i <= int "45" ] |
| ) [ mt.i ] |
+--------------------------------------------------------+

sql1>alter table sub1 set read write;
sql1>plan select * from mt where i between 25 and 45;
+-----------------------------------------------------------------------------+
| rel |
+=============================================================================+
| union ( |
| | project ( |
| | | select ( |
| | | | table(sys.sub1) [ sub1.i as mt.i, sub1.%TID% NOT NULL as mt.%TID% ] C |
: OUNT :
| | | ) [ int "25" <= mt.i <= int "45" ] |
| | ) [ mt.i ], |
| | project ( |
| | | select ( |
| | | | table(sys.sub2) [ sub2.i as mt.i, sub2.%TID% NOT NULL as mt.%TID% ] C |
: OUNT :
| | | ) [ int "25" <= mt.i <= int "45" ] |
| | ) [ mt.i ] |
| ) [ mt.i ] |
+-----------------------------------------------------------------------------+

sql2>plan select * from mt where i between 25 and 45;
+--------------------------------------------------------+
| rel |
+========================================================+
| project ( |
| | select ( |
| | | table(sys.sub2) [ mt.i, mt.%TID% NOT NULL ] COUNT |
| | ) [ int "25" <= mt.i <= int "45" ] |
| ) [ mt.i ] |
+--------------------------------------------------------+

I think this is a fix:

diff -r 24dd2ff6c348 sql/storage/store.c
--- a/sql/storage/store.c Tue May 30 13:25:19 2017 +0200
+++ b/sql/storage/store.c Tue May 30 14:25:14 2017 +0100
@@ -3402,6 +3402,7 @@

             ft->base.wtime = ft->base.rtime = 0;
             ft->cleared = 0;

•           ft->access = pft->access;
            ok = reset_changeset( tr, &ft->columns, &pft->columns, &ft->base, (resetf) &reset_column, (dupfunc) &column_dup);
            if (ok == LOG_OK)
                    ok = reset_changeset( tr, &ft->idxs, &pft->idxs, &ft->base, (resetf) &reset_idx, (dupfunc) &idx_dup);
  

Note that when preparing this bug report I managed to get the following after applying the above fix:

sql2>plan select * from mt where i between 25 and 45;
+-----------------------------------------------------------------------------+
| rel |
+=============================================================================+
| union ( |
| | project ( |
| | | select ( |
| | | | table(sys.sub2) [ sub2.i as mt.i, sub2.%TID% NOT NULL as mt.%TID% ] C |
: OUNT :
| | | ) [ int "25" <= mt.i <= int "45" ] |
| | ) [ mt.i ], |
| | project ( |
| | | select ( |
| | | | table(sys.sub2) [ sub2.i as mt.i, sub2.%TID% NOT NULL as mt.%TID% ] C |
: OUNT :
| | | ) [ int "25" <= mt.i <= int "45" ] |
| | ) [ mt.i ] |
| ) [ mt.i ] |
+-----------------------------------------------------------------------------+

Notice that it's reading from sub2 twice. I've been unable to reproduce that, but I suspect that there might be a missing line in reset_table() something like:
reset_changeset( tr, &ft->tables, &pft->tables, &ft->base, (resetf) NULL, (dupfunc) &table_of_tables_dup);

I had a couple of goes at reproducing this secondary fault but gave up fairly quickly. I'm not using merge tables therefore I'm not interested (see bug #6326).

Comment 25407

Date: 2017-07-04 13:10:12 +0200
From: MonetDB Mercurial Repository <>

Changeset ec563c09d093 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=ec563c09d093

Changeset description:

use a changeset of 'part' information in stead of
directly tables. This to solve order dependence (ie
is merge or replica table created before or after
the partition/member). This solves bug #6328

Comment 25410

Date: 2017-07-05 12:19:09 +0200
From: Richard Hughes <<richard.monetdb>>

Thanks Niels.

Just to maintain the historical record, changeset ec563c09d093 fixed bug #6325. This bug was fixed by part of changeset 5aa1c0ab39a2

Comment 25540

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

Fixed in Jul2017-SP1 release.