Date: 2019-07-22 10:43:40 +0200
From: Enrico Maria Carmona <<e.carmona>>
To: SQL devs <>
Version: 11.33.3 (Apr2019)

Last updated: 2019-09-02 16:05:26 +0200

Comment 27150

Date: 2019-07-22 10:43:40 +0200
From: Enrico Maria Carmona <<e.carmona>>

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

I recently update to April 2019 version (with yum) the monetdb container created from monetdb/monetdb-r-docker:aug2018 image.

Now the query auto generated from Mondrian MDX failed to retrive all records from star schema and only one of the child table of the merged is considered.
The fact table is merged from year-child tables, non partitioned (like previous monetdb aug2018 version).
Looking at query plan only one of the child table is referenced.
The failing query also contain two alias of a single view, joined via bridge table, which seems causing the odd behavior: changing the alias (one or both) from the view to the underlying table resolve the issue.

Reproducible: Always

Steps to Reproduce:

query:

select
"dim_periodi"."year4" as "c0",
"gruppi"."codice" as "c1",
count(*) as "m0"
from "dw_hospital"."dim_periodi" as "dim_periodi",
"dw_hospital"."facts_costi" as "facts_costi",
"dw_hospital"."v_dim_classi_movimenti" as "gruppi",
"dw_hospital"."bri_classi_gruppi_movimenti" as "bri_classi_gruppi_movimenti",
"dw_hospital"."v_dim_classi_movimenti" as "classi"
where "facts_costi"."periodo_id" = "dim_periodi"."id"
and "facts_costi"."classe_movimento_id" = "classi"."id"
and "classi"."id" = "bri_classi_gruppi_movimenti"."classe_movimento_id"
and "bri_classi_gruppi_movimenti"."gruppo_classe_movimento_id" = "gruppi"."id"
and "gruppi"."codice" = '1103'
group by "dim_periodi"."year4","gruppi"."codice"
;

steps to reproduce with attached dump.sql:

monetdb_version=aug2018
docker pull monetdb/monetdb-r-docker:$monetdb_version
docker run -d
--name monetdb-test
monetdb/monetdb-r-docker:$monetdb_version

docker exec -it monetdb-test sh -c $'echo "user=monetdb\npassword=monetdb" > /home/monetdb/.monetdb'
docker cp dump.sql monetdb-test:.
docker exec -it -u monetdb monetdb-test mclient db
</dump.sql

Run query with aug2018 version results as expected (3 tuples).

update:
docker exec -it monetdb-test yum update -y
docker exec -it monetdb-test yum upgrade -y
docker restart monetdb-test
docker exec -it -u monetdb monetdb-test mclient db

Run query with Apr2019 version results 1 tuple.

query without views (Apr2019 version) results as expected (3 tuples).

select
"dim_periodi"."year4" as "c0",
"gruppi"."codice" as "c1",
count(*) as "m0"
from "dw_hospital"."dim_periodi" as "dim_periodi",
"dw_hospital"."facts_costi" as "facts_costi",
"dw_hospital"."dim_classi_movimenti" as "gruppi",
"dw_hospital"."bri_classi_gruppi_movimenti" as "bri_classi_gruppi_movimenti",
"dw_hospital"."dim_classi_movimenti" as "classi"
where "facts_costi"."periodo_id" = "dim_periodi"."id"
and "facts_costi"."classe_movimento_id" = "classi"."id"
and "classi"."id" = "bri_classi_gruppi_movimenti"."classe_movimento_id"
and "bri_classi_gruppi_movimenti"."gruppo_classe_movimento_id" = "gruppi"."id"
and "gruppi"."codice" = '1103'
group by "dim_periodi"."year4","gruppi"."codice"
;
+------+------+------+
| c0 | c1 | m0 |
+======+======+======+
| 2017 | 1103 | 5 |
| 2018 | 1103 | 5 |
| 2019 | 1103 | 5 |
+------+------+------+
3 tuples

Actual Results:

+------+------+------+
| c0 | c1 | m0 |
+======+======+======+
| 2019 | 1103 | 5 |
+------+------+------+
1 tuple

Expected Results:

+------+------+------+
| c0 | c1 | m0 |
+======+======+======+
| 2017 | 1103 | 5 |
| 2018 | 1103 | 5 |
| 2019 | 1103 | 5 |
+------+------+------+
3 tuples

docker image:
REPOSITORY TAG IMAGE ID
monetdb/monetdb-r-docker aug2018 384ef9dcbf5a

[root@monetdb /] mserver5 --version
MonetDB 5 server 11.33.3 (Apr2019) (64-bit, 128-bit integers)
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 13.7GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.32 2012-11-30
openssl: OpenSSL 1.0.2k-fips 26 Jan 2017
libxml2: 2.9.1
Compiled by: mockbuild@ (x86_64-redhat-linux-gnu)
Compilation: gcc -std=gnu99 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -m64 -mtune=generic -Wno-format-truncation
Linking : /usr/bin/ld -m elf_x86_64 -Wl,-z,relro -specs=/usr/lib/rpm/redhat/redhat-hardened-ld -Wl,-Bsymbolic-functions
[root@monetdb /]

Comment 27151

Date: 2019-07-22 10:45:50 +0200
From: Enrico Maria Carmona <<e.carmona>>

Created attachment 620
db script

Attached file: dump.sql (application/sql, 30358 bytes)
Description: db script

Comment 27152

Date: 2019-07-22 10:48:31 +0200
From: Enrico Maria Carmona <<e.carmona>>

Created attachment 621
reproduce steps with select, plan, explain

Attached file: reproduce_steps_w_select_plan_explain (text/plain, 137447 bytes)
Description: reproduce steps with select, plan, explain

Comment 27154

Date: 2019-07-22 11:50:46 +0200
From: MonetDB Mercurial Repository <>

Changeset 6097d2479520 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

test for bug #6736.

Comment 27156

Date: 2019-07-22 16:35:56 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

Fix for bug #6736, by backporting missing check from Aug2018 branch.