I'm querying a large table with 99 columns of different types and expect to get a resultset of 3000 records. If I use a TYPE_SCROLL_INSENSITIVE ResultSet and iterate through it calling the next() method inside a while loop, It seems that after the second fetch to the database, the result set gets corrupted. In particular, I get blocks of records duplicated between consecutive fecthes, they seem to incorrectly overlap to the previous block.
Practically speaking, when iteration reaches the 1004th record, I get the 135th record instead of the correct one. The 1005th record is actually the 136th and so on. Then at record 1020th I find a duplicate of the 162th record (skipping 11 records), at record 1038th there is another hole of 11 records. This keeps going on and at the 2009th record there is a jump back to the 388th record.
I'm not performing any suspicious operation while iterating through the result set, I just invoke the getObject() method to get all row values and store them in a list of Object arrays.
I had to avoid this issue using a TYPE_FORWARD_ONLY ResultSet.
This behaviour was detected with:
- JDBC drivers versions 14.10 and 15.10
- database version 13.10 and 14.10
Please, can anyone confirm this is an unexpected behaviour of the Teradata JDBC driver?
If you're a customer, please open an incident, so we can get the details from you for reproducing the problem.
At the present time, there is one known issue with the Teradata Database returning incorrect results for a Scrollable Result Set:
Teradata Database Defect DR 173418 "TOP m PERCENT returns incorrect results when used with cursor positioning and FetchRowCount parcel"
The problem occurs when a Scrollable Result Set is used in conjunction with the setFetchSize method, and also in conjuction with the "TOP m PERCENT" clause in the query. All three features must be present for the problem to occur, so a possible workaround is to avoid using one of the three features.
Thanks, I just wanted to know if this was an already known issue and it seems so.
I confirm that in my case:
- a scrollable result set is being used;
- the setFetchSize has been invoked;
- a TOP n clause (not the TOP m PERCENT you mentioned) is used.
I already found a workaround by using a forward only result set type.
Thanks a lot!