Spring batch with column alias as sort key - malformed "where" statement

Question

I'm using Spring-batch version 3.0.6.RELEASE to query a MySQL DB for some data and then process it.

Details about usage:

query provider is MySqlPagingQueryProvider
when setting up the query provider I'm specifying an alias of one of the columns in the query as the sort key (please check the query below for more details)

Query:

SELECT
    target.tx_timestamp AS event_datetime,
    ....
FROM
    some_table AS target
    ....
WHERE
    target.tx_timestamp > :startTime AND target.tx_timestamp <= :endTime;

The code:

Map<String, Order> sortKeys = new HashMap<>();
sortKeys.put("event_datetime", Order.ASCENDING);
MySqlPagingQueryProvider queryProvider = new MySqlPagingQueryProvider();
queryProvider.setSortKeys(sortKeys);

The generated query (please note the part with event_datetime):

WHERE
    (target.tx_timestamp > ? AND target.tx_timestamp <= ?) AND
    ((event_datetime > ?))

The error:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar .... Unknown column 'event_datetime' in 'where clause'.

Is it possible to use alias as the sort key ?

Also worth noting are this thread with same issue and this Spring batch Jira ticket which is marked as resolved for version 3.0.4.

I have also tried using 3.0.4.RELEASE, 3.0.5.RELEASE and 3.0.7.RELEASE versions with the same result.

Edit: After a few more tries I can add that the issue arises only when there is enough records for more than one page. The query for the first page passes without issues but the queries for subsequent pages fail.

Edit2: As pointed out by Sabir, the issue is in fact with column alias in the where clause. Spring batch is working correctly in this case.

For people running into the same issue: You can work around this by nesting the whole query in another select like this:

SELECT * FROM (
    SELECT
        target.tx_timestamp AS event_datetime,
        ....
    FROM
        some_table AS target
        ....
    WHERE
        target.tx_timestamp > :startTime AND target.tx_timestamp <= :endTime) our_inner_select;

Unknown to me at the moment when I posted the question, the alias cannot be used in WHERE clause and since spring batch was given the alias event_datetime as sort key, the queries for chunks after the 1st one were generated with additional conditions based on sort keys.

With the query above, the inner select is evaluated before the spring batch added WHERE condition and then the usage of column alias for sort key is possible.

Is this a duplicate of [this problem](http://stackoverflow.com/questions/39078497/for-second-page-onwards-jdbcpagingitemreader-is-not-putting-values-automaticall)? — Sabir Khan, Sep 21 '16 at 06:35
@Michael Minella I'm not at liberty to do that, but I have some good news, the issue is not with spring batch, please check Edit2 in the original post. — bbd127, Sep 23 '16 at 08:56

score 5 · Accepted Answer · edited May 23 '17 at 12:26

5

When you specify a column alias of SELECT as sort key, page - 1 ( i.e. except page -0 ) onward queries generated by Spring Batch use that alias in WHERE clause as shown in your question and as per this SO question , using a column alias in WHERE clause is not allowed if evaluation of SELECT clause is not forced before WHERE clause.

So to answer your question - No, you can't use an alias as sort key unless you force alias evaluation as suggested in other SQL question.

edited May 23 '17 at 12:26

Community

1
1

answered Sep 21 '16 at 06:45

Sabir Khan

9,826
7
45
98

You're correct Sabir, the column alias in where clause was the issue. Thanks – bbd127 Sep 23 '16 at 08:55

score 1 · Answer 2 · answered Nov 14 '19 at 23:54

We ran into this issue, Spring must get your sort key from the returned result set in order to page correctly. As previous answer explains sort key gets used in a WHERE clause which will not work with column aliases.

Our solution was to SELECT target.tx_timestamp as "target.tx_timestamp" so that the sort key can be retrieved from the result set using the same name. Doing SELECT target.tx_timestamp puts the column tx_timestamp into your result set which causes issues since your sort key needs to be "target.tx_timestamp". We preferred this approach to the nested query.

Spring batch with column alias as sort key - malformed "where" statement

2 Answers2

Linked