JdbcPagingItemReader strange behaviour for record containing square symbol (²) [BATCH-2356]

Driss Amri opened BATCH-2356 and commented

Our application suddenly started throwing unique key constraints after we didn't change the code for months. The behaviour we saw was that all items in our first chunk of records/our first page were being read more than one time using the JdbcPagingItemReader.

The only thing that changed was the data we were reading, which had a strange record containing a character ² (square). When we excluded this record we didn't have any issues at all anymore.

We suddenly started seeing our Spring batch application trying to read the same records/pages twice with the JdbcPagingItemReader.

  public PagingQueryProvider queryProvider(DataSource dataSource) {
    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(dataSource);
    factory.setDatabaseType("SQLSERVER");
    factory.setSelectClause("SELECT projectnr");
    factory.setFromClause("FROM (SELECT DISTINCT projectnr FROM AQF_OZP) AQF_OZP");
    factory.setWhereClause("WHERE projectnr IS NOT NULL");
    factory.setSortKey("projectnr");

    try {
      return factory.getObject();
    } catch (Exception e) {
      throw new RuntimeException("Application intialization failed");
    }
  }

After we changed the query to have a WHERE clause to that didn't include this strange record with square symbol it worked again like it always has:

factory.setWhereClause("WHERE projectnr IS NOT NULL AND projectnr != '²21369'");

There was no exception in the reader phase, only after processing/writer we noticed this since our constraints were being triggered for proccesing the same input.

No further details from BATCH-2356

Start with JdbcPagingItemReader and the SqlPagingQueryProvider configuration shown in the issue, using the SQLSERVER query and a projectnr containing ². Reproduce the duplicate first-page reads and determine the affected paging boundary or query behavior. Done means the cause is isolated and a regression test covers the character without duplicate items.