OQL statement with union gives incorrect result when using offset

I have an OQL statement with a union to combine two results into one. If I don’t give the offset, the total of records found is 590. If I ask the same OQL statement, but with offset 574 and total 12, my list is empty. According to the amount of total records found, there should be a result… The first part of the OQL gives me 570 records, the second part 20 records… combined 590 records. Is this impossible due to the union statement in the OQL (does it only do the offset on the first part of the union query) or am I missing something?
3 answers

Don’t expect too much from Arjen. He did a great job building this OQL, but upkeeping bugfixes an feature requests is an unpaid voluntary task done in spare time. The same goes for most AppStore apps.

What is the better alternative: download the module and fix it yourself. Then create a pull-request to the github-repository. That way all Arjen needs to do is to check your solution or enhancement and redeploy the next version of the tool.

You might even get some credits or forum-points for it :-)


Create an issue on https://github.com/ArjenLammers/oqlmodule ...


After some more digging, I think the problem is not so much the OQL module, but more a defect in Mendix core itself (unless the usage by the module is incorrect).

In executeOQL, the limit and offset is set on IRetrievalSchema:

IRetrievalSchema schema = Core.createRetrievalSchema();

However, this results in an incorrect query result:

Where the count gives me for example 598 results (which is correct), the query with offset 588, limit 12 should give me the final 10 results, but it doesn’t, as the result is empty (the first part of the union only contains 569 items, the second part 29).

If I bypass the schema amount/offset and concatenate the limit/offset to the original query, the results seem to be correct.

So can it be the schema only looks at the first part of the query and not the result of the union?