JdbcPagingItemReader - When using sortKeys with alias, I think it should paging by column name rather than alias in the select clause.

[Gyuchool]

@Bean
@StepScope
public JdbcPagingItemReader<Point> reader() {

    return new JdbcPagingItemReaderBuilder<Point>()
            .name("reader")
            .pageSize(chunkSize)
            .fetchSize(chunkSize)
            .dataSource(datasource)
            .rowMapper(pointRowMapper)
            .parameterValues(parameters)
            .queryProvider(pagingQueryProvider())
            .build();
}

@Bean
public PagingQueryProvider pagingQueryProvider() {

    SqlPagingQueryProviderFactoryBean queryProvider = new SqlPagingQueryProviderFactoryBean();
    queryProvider.setDataSource(datasource);
    queryProvider.setSelectClause("place_id, user_id as member_id, points");
    queryProvider.setFromClause("user_point");
    queryProvider.setWhereClause("place_id = :place_id");
    queryProvider.setSortKeys(sortKeyAsc("place_id", "member_id"));

    try {
        return queryProvider.getObject();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

In mysqal datasource,
When you run the above code, the queries written in Current Behavior are sorted.

However, I think it should be done like the query in Expected Behavior

Expected Behavior

SELECT place_id, user_id as member_id, point 
FROM user_point 
WHERE (user_point.place_id = ?) 
AND ((place_id > ?) OR (place_id = ? AND user_id > ?)) # member_id -> user_id
ORDER BY place_id ASC, member_id ASC 
LIMIT ?

Current Behavior

SELECT place_id, user_id as member_id, point 
FROM user_point 
WHERE (user_point.place_id = ?) 
AND ((place_id > ?) OR (place_id = ? AND member_id > ?)) 
ORDER BY place_id ASC, member_id ASC 
LIMIT ?

Context

when using jdbcPagingItemReader and PagingQueryProvider, paging and sorting are done based on the sortKey in the where clause.
If an alias is used in the select clause and designated as the sortKey, the column name used in the where clause becomes the alias, leading to an exception since the database cannot find it.

This situation is awkward. Typically, the reason for using aliases in a select clause is for use in an order by clause. However, using aliases for paging causes problems. So I think it might be necessary to modify the paging logic to use actual column names rather than aliases.

[jens-muenker]

I encountered a same behavior in MSSQL when using JdbcPagingItemReader together with physical column names that contain special characters (in my case a dash -).

Example table:

Employee
---------
ID
FIRST_NAME
LAST_NAME
USER-INFO        -- physical column, contains a dash
A valid query:
SELECT ID, FIRST_NAME, LAST_NAME, [USER-INFO]
FROM Employee;

What goes wrong

Spring Batch uses the sort key to generate paging constraints in the WHERE clause.
If the sort key refers to a column that must be escaped, and also has an alias, the generated SQL becomes invalid for MSSQL.

Example configuration:

queryProvider.setSelectClause(
    """ID, FIRST_NAME, LAST_NAME, [USER-INFO] AS"""
);
queryProvider.setFromClause("Employee");
queryProvider.setSortKeys(Map.of("[USER-INFO]", Order.ASCENDING));

This results in: Invalid column name 'USER-INFO'

Workaround 1: Without subquery

This is the workaround that consistently works in MSSQL.

queryProvider.setSelectClause(
    """ID, FIRST_NAME, LAST_NAME, [USER-INFO] AS "[USER-INFO]""""
);

queryProvider.setFromClause("Employee");

queryProvider.setSortKeys(
    Map.of("[USER-INFO]", Order.ASCENDING)
);

Why it works: "USER-INFO" (with quotes) is a valid column alias in MSSQL. [USER-INFO] is the correct escaped identifier MSSQL expects in the WHERE clause Alias and identifier match exactly, just with different syntaxes.

Generated SQL becomes:

... WHERE (ID = ? AND [USER-INFO] > ?)
ORDER BY [USER-INFO] ASC

Workaround 2: Subquery

If you want to avoid mixing quotes and brackets, use a subquery and provide a safe alias:

queryProvider.setFromClause(
    "(SELECT ID, FIRST_NAME, LAST_NAME, \"USER-INFO\" AS USER_INFO_CLEAN FROM Employee) AS E"
);

queryProvider.setSelectClause(
    "ID, FIRST_NAME, LAST_NAME, USER_INFO_CLEAN"
);

queryProvider.setSortKeys(
    Map.of("USER_INFO_CLEAN", Order.ASCENDING)
);

This avoids escaped identifiers in the paging WHERE clause entirely.

Helpful References

• https://stackoverflow.com/questions/39577390/spring-batch-with-column-alias-as-sort-key-malformed-where-statement
• https://stackoverflow.com/questions/28989996/using-jdbcpagingitemreader-causing-join-statements-causing-mysqlintegrityconstra/28994455
• https://stackoverflow.com/questions/39846217/jdbcpagingitemreader-issue-with-table-name-aliases-in-inner-join-sql-for-db2-sor

[fmbenhassine]

@Gyuchool @jens-muenker Thank you for reporting this issue.

I do not exclude that this could be a bug in Spring Batch. However, I would like to validate that with a minimal complete verifiable example. Could you please take some time to create a minimal example that reproduces the problem? To help you in reporting your issue, we have prepared a project template that you can use as a starting point. Please check the Issue Reporting Guidelines for more details about this.

Thank you for your collaboration.