Issue with Sort Keys and Case Sensitivity in Spring Batch with PostgreSQL

[nipun2024]

Description

In a Spring Batch project using PostgreSQL, a bug arises due to PostgreSQL's handling of column names and sort keys, particularly when using quoted identifiers. PostgreSQL converts all selected columns to lower case in the result set, regardless of their case in the query. This behavior leads to issues when using sort keys with quoted column names in Spring Batch.

Issue details

1. Column Name Conversion: When a query is executed with column names in double quotes (e.g., SELECT "A", "B", "C" FROM tableA ORDER BY "A"), PostgreSQL converts these column names to lower case in the result set (e.g., a, b, c).
2. Sort Key Handling: The setSortKey() function in Spring Batch requires sort keys to be provided with quoted column names (e.g., "A"). However, when rows are extracted, PostgreSQL's result set converts the column name "A" to lowercase (e.g., a), causing a mismatch.
3. Error Encountered: The JDBCPagingItemReader class attempts to retrieve data using the quoted column names from the sort keys. Since PostgreSQL has converted these column names to lower case, the retrieval fails with an error indicating that the quoted column name is not found in the result set.

Steps to Reproduce

1. Define a primary key column with an upper-case name in PostgreSQL (e.g., "A")
2. Use this column name as a sort key in the Spring Batch configuration with quotes (e.g., setSortKey("A")).
3. Run a batch job that utilizes JDBCPagingItemReader
4. Observe the error indicating that the column (e.g., "A") is not found in the result set..

Expected Behaviour

The JDBCPagingItemReader should handle sort keys with quoted column names correctly by matching PostgreSQL's conversion behavior. Specifically, it should convert the quoted column names to lower case when retrieving values from the result set to ensure compatibility.

Suggested Fix

To address this issue, modify the PagingRowMapper class in JDBCPagingItemReader to convert sort key names to lower case before attempting to retrieve values from the result set. This adjustment will align with PostgreSQL's behavior of converting column names to lower case.