Lost logic whitin JdbcPagingItemReader [BATCH-2478]

[spring-projects-issues]

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

[dozim]

Hello, just wanted to mention that this issue makes it impossible for me to use the JdbcPagingItemReader and I know have to use the JdbcCursorItemReader.

[madorb]

This is an issue for me as well.

[AjinkyaM]

I was facing same issue using JdbcPagingItemReader with sql having column name alias

resolve it by nesting query in a table expression in the from (i.e. select * from (), so that I have unambiguous column names.

Before

SELECT
    C.CATEGORY_ID as 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

After

SELECT CATEGORY_ID from (
SELECT
    C.CATEGORY_ID as 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 CATEGORY_ID ASC