JDBC Merging fails with Sort Error when results are already sorted

[gmilunich (Geoff Milunich)]

I am working with a JDBC Connector to bring in accounts and entitlements. I have set it up so that the Account ID is USER_ID and Account name is another field. The query used includes the following order clause:

ORDER BY
  USER_ID ASC,
  PERMISSION_NAME ASC

For Merging, I set the USER_ID in the indexColumns and the PERMISSION_NAME in the mergeColumns. I aggregated a subset of the data and everything worked as expected, the data was merged properly, and the entitlements show correctly.

After testing, I updated the query to include all data, rather than the subset. After doing this, the system processes ~600 records, then fails with the following error.

I pulled a snippet of the data into Excel to show what we are working with where this error occurs:

Based on this, it looks like when it is comparing for the MERGE that it is using an Alphanumeric compare of the USER_ID, rather than a numeric compare, which does not see to be correct.

I did check the Account Schema, and the USER_ID field is set to string, which was brought in from the Discover Schema run. I changed it to INT in the Account Schema and ran an Aggregation followed by an Unoptimized Aggregation to see if that would resolve it, and both failed with the same error as before.

Is there something else to try here, or is the only option to do as the error suggests and set disableOrderingCheck to true?

[GOKUL_ANANTH_M]

Hi @gmilunich ,

I was also experienced the same error. When I set disableOrderingCheck as the error suggests, it worked. I have tried the other ways as like you did. Finally, I have pushed to take this decision.

Thanks!!

[enistri_devo (Emanuele Nistri)]

yes, I think is a little bug of SP is present in IIQ and ISC. Even if you want to merge the data with sorting as well, it doesn’t work.

[gmilunich (Geoff Milunich)]

Interesting, so there is no way currently to make it do a Numeric sort for the Index field.

I have tried it with the disableOrderingCheck set to true and it works correctly for me. I guess this will have to be the method until this is resolved.

@developer_advocates Would this constitute something that should be reported as a Bug?

[drosenbauer (Devin Rosenbauer)]

I’ve done plenty of merging in IIQ, but not with the very latest version. As far as I remember, all it’s doing is checking that it doesn’t see a value for the key field(s) that it’s already seen before.

Is this a behavior that was recently introduced?

Disabling the ordering check unfortunately will make it slower, because it will need to read the entire ResultSet before it can proceed.

[enistri_devo (Emanuele Nistri)]

what I can tell you is that in IIQ, I saw this behavior in v7.2, 8.1, 8.2 & 8.3 and one time in IDNOW but some time ago.

@drosenbauer

Disabling the ordering check unfortunately will make it slower, because it will need to read the entire ResultSet before it can proceed.

I know

[colin_mckibben]

@gmilunich

@developer_advocates Would this constitute something that should be reported as a Bug?

Yes, but since this spans IIQ and ISC, please submit it as a support case. Support will do a better job of triaging than our team.

[gmilunich (Geoff Milunich)]

Thanks Colin!

This looks like a similar topic for reference:

JDBC Merge rows error for multivalued records

Hi all, I would like to merge columns from a specific database table so we can aggregate in multivalued attributes. When we follow the steps listed in the guide doc we get a “data should be sorted in ascending order. Last identifier NOCLOGS\testuser current noclogs\testuser” error message which we’ve attempted to resolve using this by querying accounts using order by “SELECT * FROM dbo.mytable ORDER BY ADUsername ASC” but still the same error. We updated the JDBC Source to enable merge by sett…