Ok an update on this matter. Now it works. I've also added
<property name="MaxDependentInPredicates" value="8"/>
There was something strange with this. First I added 9 for MaxDependentInPredicates but then Teiid generated 10 in statements. With 8 as a value it did generate 8 in statements.
The defaults for Sybase are maxincriteriasize=250 and maxdependentinpredicates=7. That is a nominal number of dependent values = maxincriteriasize*maxdependentinpredicates = 1750.
If you set maxincriteriasize to a higher value without restricting maxdependentinpredicates, then yes you would expect to see an issue.
As for seeing a different number of predicates than the max. That can occur when there are multiple dependent predicates, which there are in this case. When we attempt to use up to the max number of values we'll output a partial value set for one but since they are correlated will have to put out a partial value set for the other - but the logic doesn't seem to account for that against the max setting. We should open an issue there.
One workaround for this case is that the multiple dependent sets are effectively redundant. You have the join predicates:
wdp.admissionid = dd.PatientID, gd.PatientID = wdp.admissionid from which we infer dd.PatientID = gd.PatientID, dd.PatientID in <dependent values>, gd.PatientID in <dependent values>
If it is proper to have a relationship between dd and gd, then it would be better if the user query were written with that explicitly (using the join predicates dd.PatientID = gd.PatientID, dd.PatientID = wdp.admissionid) so that we would know that only 1 dependent in predicate should be used.
And in general you can always just disable the use of prepared statements. Or we could add an enhancement to not treat dependent values specifically are bind values - however when they are not bind values with string and other long values that can cause the SQL string to be too long on some databases.
Thank your for the feedback! I understand the reason and indeed it works if I change the logic by changing gd.patientid = wdp.admissionid to gd.patientid = dd.patientid.
I'll keep that in mind when writing queries.