[Mondrian] SqlTupleReader.readTuples generates huge IN list causing java.sql.SQLException

[Rushan Chen]

Changelist 9577 made some changes in this area.

(1) The old parameter "strict" is renamed to "restrictMemberTypes" to 
better reflect the meaning.

(2) Before generating the IN list SQL in the code fragment you quoted, 
the additional check "!strict" is removed. As a result, maxConstraints 
limit will always be checked before attempting to generate SQL for the 
IN list. Your problem likely will go away.

(3) If SqlConstraintUtil.addMemberConstraint() is used to generate SQL 
for a native NonEmptyCrossJoin in your bug case, and if the crossjoin 
inputs contains more than maxConstraints members, the new behavior is 
that native cross join will be turned off, i.e. Mondrian will not try to 
generate a SQL that contains the long IN list in the where clause.

Information on the usage of maxConstraints can be found in this document:

http://www.eigenbase.org/wiki/index.php/MondrianMaxConstraintsProperty

Rushan


John V. Sichi wrote:
> BTW, Rushan encountered this same problem the other day and has been 
> working on a fix; she'll send out some info on it shortly (plus more 
> on a semi-related optimization).
>
> JVS
>
> Julian Hyde wrote:
>>> Robin Tharappel wrote:
>>>
>>> We tried setting the maxConstraints = 100 however the same error 
>>> occurred. It looks like this issue is caused in part by the large 
>>> volume dimension at the parent level (200,000).
>>> There appears to be some code that results in the in clause not to 
>>> be created (from Mondrian 2.2.2 source for class SqlConstraintUtil):
>>>
>>>             ColumnConstraint[] cc = getColumnConstraints(c);
>>>
>>>             if (!strict && cc.length >=
>>> MondrianProperties.instance().MaxConstraints.get()){
>>>                 // Simply get them all, do not create where-clause.
>>>                 // Below are two alternative approaches (and code). 
>>> They
>>>                 // both have problems.
>>>
>>>             } else {
>>>                 String cond = RolapStar.Column.createInExpr(q, cc, 
>>> level.isNumeric());
>>>                 sqlQuery.addWhere(cond);
>>>             }
>>>             if (level.isUnique()) {
>>>                 break; // no further qualification needed
>>>             }
>>> In our case the number of ColumnConstraints (200,000) exceeds the 
>>> max constraint property (100), however the strict variable is always 
>>> true.
>>> Under what circumstances would this be false? We also tried Mondrian
>>> 2.3.2 but received the same error. 
>>
>> I would approach this by running the code in the debugger. The simplest
>> explanation is that the code below is not hit - because this code is 
>> part of
>> the semi-join generation to implement non-empty.
>>
>> I would do this if I had time and a reproducible testcase but right 
>> now I
>> don't have either. :)
>> Julian
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian