Hello Enrico, This does seem like a bug to me, so please open a bug on the bug tracker. If possible please mention the database schema and the queries you used so that we can reproduce the problem. Best regards, Panos.

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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list