-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512
Hi,
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 behaviour: changing
the alias (one or both) from the view to the underlying table
resolve the issue.
Any ideas how investigate further or may I fill a bug?
Thanks
Enrico
original 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"
;
result:
c0 c1 m0
- --------------------
2019 1103 152202
query plan:
project (
| group by (
| | project (
| | | group by (
| | | | project (
| | | | | group by (
| | | | | | project (
| | | | | | | group by (
| | | | | | | | project (
| | | | | | | | | join (
| | | | | | | | | | project (
| | | | | | | | | | | join (
| | | | | | | | | | | | crossproduct (
| | | | | | | | | | | | | join (
| | | | | | | | | | | | | |
table(dw_hospital.bri_classi_gruppi_movimenti) [
"bri_classi_gruppi_movimenti"."classe_movimento_id" NOT NULL HASHCOL
, "bri_classi_gruppi_movimenti"."gruppo_classe_movimento_id" NOT
NULL ] COUNT ,
| | | | | | | | | | | | | | select (
| | | | | | | | | | | | | | |
table(dw_hospital.dim_classi_movimenti) [
"dim_classi_movimenti"."id" NOT NULL HASHCOL ,
"dim_classi_movimenti"."codice" NOT NULL ] COUNT
| | | | | | | | | | | | | | ) [ "dim_classi_movimenti"."codice" NOT
NULL = varchar(16) "1103" ]
| | | | | | | | | | | | | ) [
"bri_classi_gruppi_movimenti"."gruppo_classe_movimento_id" NOT NULL
= "dim_classi_movimenti"."id" NOT NULL HASHCOL ],
| | | | | | | | | | | | | table(dw_hospital.facts_costi_2019) [
"facts_costi_2019"."periodo_id" NOT NULL as
"facts_costi"."periodo_id", "facts_costi_2019"."classe_movimento_id"
NOT NULL as "facts_costi"."classe_movimento_id" ] COUNT
| | | | | | | | | | | | ) [ ],
| | | | | | | | | | | | table(dw_hospital.dim_periodi) [
"dim_periodi"."id" NOT NULL HASHCOL , "dim_periodi"."year4" NOT NULL
] COUNT
| | | | | | | | | | | ) [ "facts_costi"."periodo_id" NOT NULL =
"dim_periodi"."id" NOT NULL HASHCOL ]
| | | | | | | | | | ) [
"bri_classi_gruppi_movimenti"."classe_movimento_id" NOT NULL HASHCOL
, "bri_classi_gruppi_movimenti"."gruppo_classe_movimento_id" NOT
NULL, "dim_classi_movimenti"."id" NOT NULL HASHCOL as "L2"."id",
"dim_classi_movimenti"."codice" NOT NULL as "L4"."codice",
"dim_periodi"."id" NOT NULL HASHCOL , "dim_periodi"."year4" NOT
NULL, "facts_costi"."periodo_id" NOT NULL,
"facts_costi"."classe_movimento_id" NOT NULL ],
| | | | | | | | | | table(dw_hospital.dim_classi_movimenti) [
"dim_classi_movimenti"."id" NOT NULL HASHCOL ] COUNT
| | | | | | | | | ) [ "facts_costi"."classe_movimento_id" NOT NULL =
"dim_classi_movimenti"."id" NOT NULL HASHCOL ,
"dim_classi_movimenti"."id" NOT NULL HASHCOL =
"bri_classi_gruppi_movimenti"."classe_movimento_id" NOT NULL
HASHCOL ]
| | | | | | | | ) [ "dim_periodi"."year4" NOT NULL,
"facts_costi"."classe_movimento_id" NOT NULL, "L4"."codice" NOT NULL
as "gruppi"."codice",
"bri_classi_gruppi_movimenti"."classe_movimento_id" NOT NULL HASHCOL
, "dim_classi_movimenti"."id" NOT NULL HASHCOL as "classi"."id" ]
| | | | | | | ) [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice"
NOT NULL ] [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT
NULL, sys.count() NOT NULL as "L76"."L76" ]
| | | | | | ) [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice"
NOT NULL, "L76"."L76" ]
| | | | | ) [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT
NULL ] [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT NULL,
sys.sum no nil ("L76"."L76") as "L76"."L76" ]
| | | | ) [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT
NULL, "L76"."L76" ]
| | | ) [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT NULL
] [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT NULL,
sys.sum no nil ("L76"."L76") as "L76"."L76" ]
| | ) [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT NULL,
"L76"."L76" ]
| ) [ "dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT NULL ] [
"dim_periodi"."year4" NOT NULL, "gruppi"."codice" NOT NULL, sys.sum
no nil ("L76"."L76") as "L76"."L76" ]
) [ "dim_periodi"."year4" NOT NULL as "L72"."c0", "gruppi"."codice"
NOT NULL as "L74"."c1", "L76"."L76" NOT NULL as "L77"."m0" ]
slightly modified query: use of "dim_classi_movimenti" instead
"v_dim_classi_movimenti"
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"
;
result:
c0 c1 m0
- --------------------
2000 1103 69743
2001 1103 195770
2002 1103 224634
2003 1103 232636
2004 1103 239884
2005 1103 238547
2006 1103 239546
2007 1103 243460
2008 1103 246955
2009 1103 147065
2010 1103 218059
2011 1103 214932
2012 1103 202381
2013 1103 200803
2014 1103 208968
2015 1103 173349
2016 1103 205914
2017 1103 221103
2018 1103 332020
2019 1103 152202
- --
Ciao
Enrico
The truth is more important than the facts.
-- Frank Lloyd Wright
-----BEGIN PGP SIGNATURE-----
iQEzBAEBCgAdFiEEpdq1Yxf7+kxI5zNpW4CBs6DXKFIFAl0vSKEACgkQW4CBs6DX
KFKTMggApERqMMEqf6ZvF8f1RmyZ/boUm1eFTavATan5ykpyjS0jNo9nVU9gm0GW
76c3smhxHxXtZUk/8hn+rhY+RI8U5J0MP6RiY4LrbWitx2wiRQbDosvC8yzV9IjV
1kli9C3uanKH2ipwFx9B2wjd+XauNQVlqdrRfdpSonFbtLTLZlQREYGFtniXlNS/
oELUM6B4LmEIcXKV8utCfw0WCQJsO3wGZ3/9+FgR00f3gcJv+z7Ml1mnhzA46zcK
kXqpLb4/iF8DNHCJq766yKHH+c0K/e4VbRaXTXUTt4AD599/3ZPz5k7c6K8jBlvK
ubcZ8B7Erpz/iA3OMR032vJMLgGGkQ==
=xfCa
-----END PGP SIGNATURE-----