Wrong query generate with Derby and SqlPagingQueryProviderFactoryBean

[mikehoss]

I'm playing around with Spring Batch and am having strange errors when using the SqlPagingQueryProviderFactoryBean.

My little test table is in Derby and is created like this:

create table test_table (
                id int,
                state int)

I wired up my query provider like this:

<beans:bean id="queryProvider"
             class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
        <beans:property name="dataSource" ref="dataSource"/>
        <beans:property name="selectClause" value="id,state"/>
        <beans:property name="fromClause" value="test_table"/>
        <beans:property name="whereClause" value="id%2=0"/>
        <beans:property name="sortKey" value="id"/>
    </beans:bean>

And when I start up my Spring context, and start a job, I get this error:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; 
bad SQL grammar [SELECT * FROM (
 SELECT id,state, ROW_NUMBER() OVER (ORDER BY id ASC) AS ROW_NUMBER FROM test_table WHERE id%2=0) 
WHERE ROW_NUMBER <= 10]; 
nested exception is java.sql.SQLSyntaxErrorException:
 Syntax error: Encountered "ORDER" at line 1, column 53.

Any ideas?

[Dave Syer]

Raise a JIRA?