Lost logic whitin JdbcPagingItemReader [BATCH-2478]

Gabriel Villacis opened BATCH-2478 and commented

There is an issue present within JdbcPagingItemReader that in version 2 it is not present.

Version 2 says that the inner class PagingRowMapper gets the column value with cleaned sorted key (without alias):

private class PagingRowMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        startAfterValue = rs.getObject(queryProvider.getSortKeyWithoutAlias());
        return rowMapper.mapRow(rs, rowNum);
    }
}

Version 3 says that the inner class PagingRowMapper gets the column value with raw sorted key (with or without alias depending on the configuration):

private class PagingRowMapper implements RowMapper<T> {
    @Override
    public T mapRow(ResultSet rs, int rowNum) throws SQLException {
        startAfterValues = new LinkedHashMap<String, Object>();
        for (Map.Entry<String, Order> sortKey : queryProvider.getSortKeys().entrySet()) {
            startAfterValues.put(sortKey.getKey(), rs.getObject(sortKey.getKey()));
        }
        return rowMapper.mapRow(rs, rowNum);
    }
}

This is correct until we have 2 tables with columns with the same name.

CREATE TABLE MY_SCHEMA.MY_TABLE_1 (
    STORAGE_ID  BIGINT  NOT NULL
);

CREATE TABLE MY_SCHEMA.MY_TABLE_2 (
    STORAGE_ID  BIGINT  NOT NULL
  , CATEGORY_ID BIGINT  NOT NULL
);

CREATE TABLE MY_SCHEMA.MY_TABLE_3 (
    CATEGORY_ID BIGINT  NOT NULL
  , PRICE       INTEGER NOT NULL
);

And we execute this query with Spring Batch:

SELECT
    C.CATEGORY_ID
  , C.PRICE
FROM
    MY_SCHEMA.MY_TABLE_1        A
    JOIN MY_SCHEMA.MY_TABLE_2   B ON B.STORAGE_ID = A.STORAGE_ID
    JOIN MY_SCHEMA.MY_TABLE_3   C ON C.CATEGORY_ID = B.CATEGORY_ID
ORDER BY C.CATEGORY_ID ASC

I get an error in the 1st page saying: Unknown column C.CATEGORY_ID.

If I remove the alias I get an error in the 2nd page saying: Ambiguous column name CATEGORY_ID.

I had to copy JdbcPagingItemReader in my proyect and fix this issue adding the next code:

public final class SqlTools {

    private static final Pattern ALIAS_PATTERN = Pattern.compile("^.*\\.");

    private SqlTools() {
    }

    public static String cleanAlias(String sample) {

        if (sample != null) {
            Matcher matcher = ALIAS_PATTERN.matcher(sample);

            if (matcher.find()) {
                return matcher.replaceAll(");
            }

            return sample;
        }

        return null;
    }

}

private class PagingRowMapper implements RowMapper<T> {

        @Override
        public T mapRow(ResultSet resultSet, int index) throws SQLException {

            startAfterValues = new LinkedHashMap<String, Object>();

            for (Map.Entry<String, Order> sortKey : queryProvider.getSortKeys().entrySet()) {
                String columnName = SqlTools.cleanAlias(sortKey.getKey());
                startAfterValues.put(sortKey.getKey(), resultSet.getObject(columnName));
            }

            return rowMapper.mapRow(resultSet, index);
        }

    }

Affects: 3.0.1, 3.0.2, 3.0.4, 3.0.5, 3.0.6

Start with JdbcPagingItemReader and its PagingRowMapper, then trace how queryProvider sort keys are used to read values from the ResultSet. Reproduce the reported aliased and unaliased join queries, and consider the issue complete when paging retrieves sort-key values correctly without unknown-column or ambiguous-column errors.