Mondrian ignores rows on dimensions not present in MDX but present in schema

[kamil-sita]

SETUP

Mondrian version 9.2.0.0-6, but I'm pretty sure it's reproducible on all versions.

I have created this rather simple database with one table:

CREATE TABLE public.newtable (
	fact1 varchar NULL,
	fact2 varchar NULL,
	value numeric NULL
);

INSERT INTO public.newtable (fact1,fact2,value) 
VALUES 
('1','1',1),
('1','2',2),
('1','3',3),
('2','1',1),
('2','3',3),
('2','4',2),
('3','2',2),
('3','3',4),
('3','4',1);

(database is PostgreSQL 12.2 64bit, but I'm sure it's not related as this problem appears with other databases as well).

With following schema file and basic code:

<Schema name="Schema">
    <Cube name="TestCube">
        <Table name="newtable"/>
        <Dimension name="fact1dim">
            <Hierarchy hasAll="false">
                <Level name="fact1" column="fact1"/>
            </Hierarchy>
        </Dimension>
        <Dimension name="fact2dim">
            <Hierarchy hasAll="false">
                <Level name="fact2" column="fact2"/>
            </Hierarchy>
        </Dimension>
        <Measure name="value sum" column="value" aggregator="sum"/>
    </Cube>
</Schema>

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
   ...
    Connection connection = DriverManager.getConnection(
            "jdbc:mondrian:"
                    + "JdbcDrivers=" + driver + ";"
                    + "Jdbc=" + jdbcString + ";"
                    + "Catalog=" + xmlLoc + ";JdbcUser=" + username + ";JdbcPassword=" + password + ";PoolNeeded=true;"
    );
    OlapWrapper wrapper = (OlapWrapper) connection;
    OlapConnection connection1 = wrapper.unwrap(OlapConnection.class);
    CellSet cellSet = connection1.createStatement().executeOlapQuery(
            "select {[fact1dim].Members} on 0, {[fact2dim].Members} on 1, {[Measures].Members} on 2 from [TestCube]"
    );
    PrintWriter pw = new PrintWriter(System.out);
    new RectangularCellSetFormatter(false).format(cellSet, pw);
    pw.flush();
}

---

PROBLEM

Running the code results in the following:

PAGES: [Measures].[value sum]
|   | 1 | 2 | 3 |
+---+---+---+---+
| 1 | 1 | 1 |   |
| 2 | 2 |   | 2 |
| 3 | 3 | 3 | 4 |
| 4 |   | 2 | 1 |

PAGES: [Measures].[Fact Count]
|   | 1 | 2 | 3 |
+---+---+---+---+
| 1 | 1 | 1 |   |
| 2 | 1 |   | 1 |
| 3 | 1 | 1 | 1 |
| 4 |   | 1 | 1 |

With final SQL query being:

select
    "newtable"."fact1" as "c0",
    "newtable"."fact2" as "c1",
    sum("newtable"."value") as "m0",
    count(*) as "m1"
from
    "newtable" as "newtable"
group by
    "newtable"."fact1",
    "newtable"."fact2"

Which seems correct. However, changing the query from:

select {[fact1dim].Members} on 0, {[fact2dim].Members} on 1, {[Measures].Members} on 2 from [TestCube]

to

select {[fact1dim].Members} on 0, {[Measures].Members} on 1 from [TestCube]

without changing schema file results in the following (FACTUAL):

|            | 1 | 2 | 3 |
+------------+---+---+---+
| value sum  | 1 | 1 |   |
| Fact Count | 1 | 1 |   |

With final SQL query being:

select
    "newtable"."fact1" as "c0",
    "newtable"."fact2" as "c1",
    sum("newtable"."value") as "m0",
    count(*) as "m1"
from
    "newtable" as "newtable"
where
    "newtable"."fact2" = '1'
group by
    "newtable"."fact1",
    "newtable"."fact2"

As you can see, Mondrian decided for some reason that "fact2" row contains only values of "1", which is wrong - it leads to all data being ignored from rows where "fact1" is 3, and for some data where "fact1" differs from 3.

Removing "fact2dim" from schema file leads to the results being correct once again (EXPECTED):

|            | 1 | 2 | 3 |
+------------+---+---+---+
| value sum  | 6 | 6 | 7 |
| Fact Count | 3 | 3 | 3 |

which is what I would expect from query that does not use dimensions that exist in schema file. SQL query for this query was:

select
    "newtable"."fact1" as "c0",
    sum("newtable"."value") as "m0",
    count(*) as "m1"
from
    "newtable" as "newtable"
group by
    "newtable"."fact1"

For me that seems like a bug, but maybe it is some expected/undefined behavior? I could not find any mention of it anywhere.

[kamil-sita]

Also this bug does not happen when hasAll="true" is set in all hierarchies, however then "All XYZ" member is also present.