Uploaded image for project: 'Spring Batch'
  1. Spring Batch
  2. BATCH-1845

OraclePagingQueryProvider reads incorrect number of rows based on Sort Key and PageSize

    XMLWordPrintable

    Details

      Description

      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.

        Attachments

        1. CTY_NME 29.pdf
          28 kB
        2. CTY_NME 29 Log.txt
          44 kB
        3. CTY_NME 94.pdf
          28 kB
        4. CTY_NME 94 Log.txt
          42 kB
        5. JDBCExperiment.java
          1 kB
        6. JDBCExperiment Log.txt
          4 kB
        7. ST_CDE 28.pdf
          16 kB
        8. ST_CDE 28 Log.txt
          31 kB
        9. ST_CDE 29.pdf
          12 kB
        10. ST_CDE 29 Log.txt
          27 kB
        11. ST_CDE 93.pdf
          28 kB
        12. ST_CDE 93 Log.txt
          42 kB
        13. ST_CDE 94.pdf
          28 kB
        14. ST_CDE 94 Log.txt
          42 kB

          Activity

            People

            Assignee:
            mminella Michael Minella
            Reporter:
            davidlking David Leo King
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: