Symptoms
If you need to merge columns on JDBC Aggregation (eg you have an SQL Statement using a JOIN to include group memberships), you might get exceptions during aggregation due to merge errors.
The exception might occur even if you have added proper 'order by' clauses in your SQL Statement.
In Compass, you can find a couple of solutions based on disabling order checks ( <entry key="disableOrderingCheck" value="true"/>). While this helps you to get rid of the problem, it might impose another one: the setting means IIQ should do the ordering on its own, and this puts an additional burden on IIQ, as it has to do sorting in memory.
Diagnosis
So why do we get the error, even if we have done proper sorting (ordering) using the SQL-Statement? The reason is the fact SQL Ordering and Java Sorting does not always produce the same output. This is especially true if you do sort on alphanumerics (eg ABCD11, ABCD111, ABCD12):
- SQL Sorting (alphabetical sorting)
- Java Sorting (Natural sorting)
Solution
To prevent the sorting problem, you should make sure that IIQ (=Java) and your JDBC Order clause produce the same output. Convincing IIQ (and java) to use some special sorting is somewhat pointless, so you should check what you can do on JDBC side. Easiest solution is to use 'Natural Sorting' (Oracle provides such solution) for your results.
If there is no such function on your DB, I recommend to either create a StoredProcedure which provides the required sorting (and put the burden on the DB-Server), or use another field to sort (if you have unique row_ids(int) in your table, you are in a nice position already)
