Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Db2PagingQueryProvider creates erroneous Statement in generateJumpToItemQuery [BATCH-2040] #1554

Closed
spring-projects-issues opened this issue Jun 2, 2013 · 6 comments

Comments

@spring-projects-issues
Copy link
Collaborator

spring-projects-issues commented Jun 2, 2013

Arne Borrmann opened BATCH-2040 and commented

Hi,

we want to use the spring-batch-admin on WAS70 with DB2-Database underlying.

The list of executions is delimited to 20 per default. If we try to jump on the next page, an internal server error 500 will occur.

As we hit the "next" button, the method generateJumpToItemQuery(int itemIndex, int pageSize) in org.springframework.batch.item.database.support.Db2PagingQueryProvider is called.

The generated SQL-Statement is:
SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM T1100Z.BATCH_JOB_EXECUTION E, T1100Z.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC

After a few test i found out the right SQL:
SELECT TMP_SUB.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM T1100Z.BATCH_JOB_EXECUTION E, T1100Z.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY TMP_SUB.JOB_EXECUTION_ID DESC

As you can see, the subquery uses the "E.", but comes out as TMP_SUB.

The "E." is set by the batch-admin-manager: sortKeys.put("E.JOB_EXECUTION_ID", Order.DESCENDING);

I think a the generateJumpToItemQuery-Method in Db2PagingQueryProvider has to be overridden, or the method buildSortKeySelect(StringBuilder sql) could get a brother like: buildSortKeySelect(StringBuilder sql, String qualifierReplacement) ("E." -> "TMP_SUB.")


Affects: 2.2.0.RC2, 2.2.1

Referenced from: commits 56ccca6, 856e25a

2 votes, 4 watchers

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented Jun 3, 2013

Michael Minella commented

A couple things to note here:

  1. Spring Batch Admin hasn't been updated to work with 2.2 officially yet. There are a couple breaking changes within 2.2 that will impact it's use with Admin. The nightly release should be close but even that isn't 100% tested yet (this issue is in the nightly build as well). I think this issue can be addressed in admin without a change to batch by aliasing the field with a unique name, thereby not requiring the table alias at all.
  2. Can you submit a pull request with this change in Github? Please be sure to fill out a contributor's agreement.

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented Jun 4, 2013

Arne Borrmann commented

Without changing anything in batch, i fixed the issue by setting the sortkeys without "E.": sortKeys.put("JOB_EXECUTION_ID", Order.DESCENDING);. I think this could fix the issue without any aliasing because the column name (JOB_EXECUTION_ID) is already unique for this sql-statement.

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented Aug 5, 2013

Sebastian Schmidt commented

There appear to be some duplicates of this bug:

Also, there is a pull request attached to https://jira.springsource.org/browse/BATCHADM-168 which essentially implements your suggestion, Arne. It works for me with DB2.

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented Aug 5, 2013

Arne Borrmann commented

The implementation in the SpringBatchAdmin seems to be a workaround for me. I think it is valid to set the sort keys with qualifier. In this case, the PagingProvider fails.

Here is a pull request for the PagingProvider: #181

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented Aug 6, 2013

Sebastian Schmidt commented

Thank you for the patch. I successfully applied it to our setup.

I agree that making the API more robust might be the more appropriate approach. Let me know if I can help moving this forward.

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented Sep 8, 2013

Michael Minella commented

Merged.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant