Date: 2018-06-27 16:15:54 +0200
From: @yzchang
To: SQL devs <>
Version: 11.29.3 (Mar2018)
CC: @PedroTadim, wocyg, zelular

Last updated: 2020-03-30 11:16:28 +0200

Comment 26515

Date: 2018-06-27 16:15:54 +0200
From: @yzchang

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/11.1.1 Safari/605.1.15
Build Identifier:

When selecting from a REPLICA TABLE, or joining a REPLICA TABLE with a MERGE TABLE, the locally available replica is not detected. Thereby, a remote replica is used.

Reproducible: Always

Steps to Reproduce:

1. Set up three MonetDB instances. We'll use 'mdb3' as the master:

$ mserver5 --dbpath=/tmp/mdb1 --set mapi_port=60001 --set monet_daemon=yes &
$ mserver5 --dbpath=/tmp/mdb2 --set mapi_port=60002 --set monet_daemon=yes &
$ mserver5 --dbpath=/tmp/mdb3 --set mapi_port=60003 --set monet_daemon=yes &

2. Create tables on each instances. s1,s2,s3 will be used in a REPLICA TABLE. t1,t2,t3 will be used in a MERGE TABLE.

$ mclient -d mdb1 -u monetdb -p 60001
create table s1 (i int);
insert into s1 values (23), (42);
create table t1 (s varchar(10));
insert into t1 values ('abc'), ('efg');

$ mclient -d mdb2 -u monetdb -p 60002
create table s2 (i int);
insert into s2 values (23), (42);
create table t2 (s varchar(10));
insert into t2 values ('foo'), ('bar');

$ mclient -d mdb3 -u monetdb -p 60003
create table s3 (i int);
insert into s3 values (23), (42);
create table t3 (s varchar(10));
insert into t3 values ('baz'), ('qux');

3. Set up the REPLICA TABLE and MERGE TABLE on 'mdb3'

$ mclient -d mdb3 -u monetdb -p 60003
create remote table s1 (i int) on 'mapi:monetdb://localhost:60001/mdb1';
create remote table s2 (i int) on 'mapi:monetdb://localhost:60002/mdb2';
create remote table t1 (s varchar(10)) on 'mapi:monetdb://localhost:60001/mdb1';
create remote table t2 (s varchar(10)) on 'mapi:monetdb://localhost:60002/mdb2';

-- s3 is a locally available replica:
create replica table repS(i int);
alter table repS add table s2;
alter table repS add table s1;
alter table repS add table s3;

create merge table mrgT (s varchar(10));
alter table mrgT add table t2;
alter table mrgT add table t1;
alter table mrgT add table t3;

4. Problem 1: when we select from repS. the local replica s3 is not used:

sql>plan select * from repS;
+------------------------------------------------------------------------------------------------+
| rel |
+================================================================================================+
| project ( |
| | table |
| | | REMOTE(sys.s2) [ "s2"."i" as "reps"."i", "s2"."%TID%" NOT NULL as "reps"."%TID%" ] REMOTE |
: mapi:monetdb://localhost:60002/mdb2 [ "reps"."i", "reps"."%TID%" NOT NULL ] :
| ) [ "reps"."i" ] |
+------------------------------------------------------------------------------------------------+
4 tuples

5. Problem 2: when we join repS with mrgT, the local replica s3 is not used to join with t3:

sql>plan select * from repS, mrgT;
+------------------------------------------------------------------------------------------------+
| rel |
+================================================================================================+
| union ( |
| | union ( |
| | | table |
| | | | project ( |
| | | | | crossproduct ( |
| | | | | | REMOTE(sys.s2) [ "s2"."i" as "reps"."i", "s2"."%TID%" NOT NULL as "reps"."%TID%" ], |
| | | | | | project ( |
| | | | | | | REMOTE(sys.t2) [ "t2"."s" as "mrgt"."s" ] COUNT |
| | | | | | ) [ "mrgt"."s" ] |
| | | | | ) [ ] |
| | | | ) [ "reps"."i", "mrgt"."s" ] REMOTE mapi:monetdb://localhost:60002/mdb2 [ "reps"."i", "m |
: rgt"."s" ], :
| | | table |
| | | | project ( |
| | | | | crossproduct ( |
| | | | | | REMOTE(sys.s1) [ "s1"."i" as "reps"."i", "s1"."%TID%" NOT NULL as "reps"."%TID%" ], |
| | | | | | project ( |
| | | | | | | REMOTE(sys.t1) [ "t1"."s" as "mrgt"."s" ] COUNT |
| | | | | | ) [ "mrgt"."s" ] |
| | | | | ) [ ] |
| | | | ) [ "reps"."i", "mrgt"."s" ] REMOTE mapi:monetdb://localhost:60001/mdb1 [ "reps"."i", "m |
: rgt"."s" ] :
| | ) [ "reps"."i", "mrgt"."s" ], |
| | project ( |
| | | crossproduct ( |
| | | | table |
| | | | | REMOTE(sys.s2) [ "s2"."i" as "reps"."i", "s2"."%TID%" NOT NULL as "reps"."%TID%" ] REM |
: OTE mapi:monetdb://localhost:60002/mdb2 [ "reps"."i", "reps"."%TID%" NOT NULL ], :
| | | | project ( |
| | | | | table(sys.t3) [ "t3"."s" as "mrgt"."s" ] COUNT |
| | | | ) [ "mrgt"."s" ] |
| | | ) [ ] |
| | ) [ "reps"."i", "mrgt"."s" ] |
| ) [ "reps"."i", "mrgt"."s" ] |
+------------------------------------------------------------------------------------------------+
31 tuples

Expected Results:

Problem 1: s3 should be used for this SELECT

Problem 2: s3 should be used for the join with t3

Comment 26532

Date: 2018-06-29 16:24:11 +0200
From: MonetDB Mercurial Repository <>

Changeset 6b85ab2bb368 made by Ying Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added tests for Bug #6620

Comment 27539

Date: 2020-02-12 11:14:03 +0100
From: MonetDB Mercurial Repository <>

Changeset 0b4e046a12ca 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=0b4e046a12ca

Changeset description:

Test local_replica_table_not_detected.Bug-6620 is no longer failing