Resolution: Works as Designed
Affects Version/s: 2.1.8
Fix Version/s: None
Environment:Windows 7 32 bit
Spring Batch 2.1.8
11g Oracle DataBase
I listed this as critical because it causes a loss of data.
The situation that I am runnning into occurs when the sort key value has less rows than the page size.
The number of rows I obtain are significantly less as the page size for the job decreases.
I have a Table called CITY with columns CTY_NME and ST_CDE (Agency Code is just a random value).
Using the query "SELECT c.* FROM <Schema Name>.CITY c WHERE c.CTY_NME LIKE 'M%' AND c.ST_CDE LIKE 'C%' ORDER BY <Sort Key> ASC;" there are a total of 3 states (CA, CO, CT) and each CTY_NME and CTY_CDE are unique for all records.
For ST_CDE = CA there are 94 rows
For ST_CDE = CO there are 28 rows
For ST_CDE = CT there are 20 rows
For at total of 142 rows.
Now if I have the sort key be CTY_NME, there is a total of 142 rows returned no matter what the page size is (for this case I tested 93 and 29).
When I use ST_CDE as the Sort Key (having only 3 values) the number of rows I get are variable.
Page Size = 93 | 94 | 28 | 29
Row Count = 141 | 142 | 76 | 58
For page size 93 and 94, there are two pages. One where it picks up CA with 93 of its rows (missing the final CA row) and then CO and CT together (28 + 20 = 48 < 93). For a page size of 94, all 94 of CA rows are printed and so are CO (28) and CT (20). Again, it took two pages.
For page size of 28, the first 28 rows of CA are given, all of the 28 rows of CO are given with the 20 rows of CT is also given for a total of 28+28+20 = 76 (three pages total).
For page size of 29, the first 29 rows of CA are given, all of the 28 rows of CO are given with the first of the rows for CT given for a total of 29+28+1 = 58. (three pages total)
At this time I am unsure if this is at the OracleQueryProvider level or much deeper. I couldn't find where the error would occur.
I also tested the Oracle JDBC by itself and also obtained the correct number of rows.
I've attached several documents of my personal troubleshooting. The names that start with ST_CDE are results from a sort key of ST_CDE and the CTY_NME, CTY_NME. The numbers are the page size used. JDBCExperiment was the class I used to test the driver. Logs contain the values printed to the console.