[Mondrian] RE: [ 1955815 ] Cartesian Join in SQL from MDX against virtual cube

Julian Hyde jhyde at pentaho.com
Fri Jul 18 03:23:20 EDT 2008

That sounds about right. At first I was a bit concerned that
getMeasureCube() is  always going to be populated correctly when
SqlTupleReader methods are being called - after all, you shouldn't need
measure-related stuff to populate the axes - but I suppose this is how
non-empty SQL gets generated. Gives me something else to think about as I do
the schema refactoring and make all cubes effectively virtual.

The most surprising thing about this bug is how simple the test case is.
Can't believe we haven't run into this before - or maybe we have run into it
but haven't noticed. I think the best thing you can do is 'test around' this
- write one or two similar test cases.

I'm scratching my head trying to think of an MDX query that would require us
to generate a 'where exists ...' - or semijoins - on two different fact
tables in order to find non-empty members of a particular dimension. Suppose
you were to ask for non-empty products and use a pair of measures, one from
Sales and the other from Warehouse. I'd be interested to see what happens
then. Switch the order of the measures and see whether you get the same set
of results.

Also before you check in, check that it works with grouping sets enabled and
with aggregate tables enabled. Those code paths can often be broken by stuff
like this.

Provided that the test suite passes (in the configurations noted above), go
ahead and check in. Even if that last 'double semijoin' query causes
problems, your fix is still a leap forward.


> -----Original Message-----
> From: Will Gorman [mailto:wgorman at pentaho.com] 
> Sent: Wednesday, July 16, 2008 7:21 PM
> To: Julian Hyde
> Cc: mondrian at pentaho.org
> Subject: [ 1955815 ] Cartesian Join in SQL from MDX against 
> virtual cube
> Hi Julian,
> I'm investigating bug #1955815, the virtual cube cartesian join issue.
> I've confirmed that this issue existed in 2.4.2, making sure 
> it wasn't a
> regression in 3.0.  when generating a non-empty tuple with a 
> filter, the
> SqlTupleReader creates SQL that contains the Tuple's join to it's fact
> table, but also a constraint join to the measure's fact table based on
> the current evaluation context, which contains the a filtered member.
> I've added the following if statement within
> SqlTupleReader.generateSelectForLevels to verify that the constraint's
> evaluation context base cube matches that of the current Tuple's base
> cube.  Do you think this is a reasonable approach?
>         // if we're a virtual cube (baseCube != null), only apply the 
>         // constraint if it maps to the current base cube.
>         if (baseCube == null || 
> constraint.getEvaluator().getMeasureCube().equals(baseCube)) {
>         	constraint.addConstraint(sqlQuery, baseCube);
>         }
> All the tests pass with this change, and the cartesian join no longer
> occurs.  My knowledge of how to apply non-empty constraints to virtual
> cubes where the filter doesn't match the tuple is limited, so your
> thoughts are appreciated!
> Thanks!
> Will

More information about the Mondrian mailing list