Use custom paging provider for aggregate DAO queries by onobc · Pull Request #5606 · spring-attic/spring-cloud-dataflow

onobc · 2023-12-15T17:50:26Z

This commit adds a custom Oracle/MSSQl/DB2 paging provider that is used only by the aggregate DAO. This is required because the standard paging provider that ships with Spring Batch 4.x does not properly handle sort key aliases when using nested ROW_NUMBER clauses for these databases.

Resolves #5531

...main/java/org/springframework/cloud/dataflow/server/repository/JdbcAggregateJobQueryDao.java

.../src/test/java/org/springframework/cloud/dataflow/server/db/migration/AbstractSmokeTest.java

.../test/java/org/springframework/cloud/dataflow/server/db/migration/JobExecutionTestUtils.java

corneil · 2023-12-18T11:46:42Z

From org/springframework/cloud/dataflow/server/repository/JdbcAggregateJobQueryDao.java:607 in org.springframework.batch.item.database.support.AbstractSqlPagingQueryProvider#getSortKeysWithoutAliases
return SqlPagingQueryUtils.buildSortClause(provider.getSortKeysWithoutAliases()); causes:

SELECT TMP_SUB.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY **JOB_EXECUTION_ID** DESC) AS ROW_NUMBER FROM
resulting in com.microsoft.sqlserver.jdbc.SQLServerException: Ambiguous column name 'JOB_EXECUTION_ID'

The offending code is a part of batch framework.

corneil · 2023-12-18T11:47:07Z

DB2 seems to suffer the same error.

cppwfs

On the code side it looks good. The tests you added catch DB2 and SqlServer has an ambiguous jobexecution id like corneil mentioned from batch 4. And your updated tests now catch this scenario. Yeah for your newly added smoke tests!

...main/java/org/springframework/cloud/dataflow/server/repository/JdbcAggregateJobQueryDao.java

onobc · 2023-12-18T19:02:57Z

From org/springframework/cloud/dataflow/server/repository/JdbcAggregateJobQueryDao.java:607 in org.springframework.batch.item.database.support.AbstractSqlPagingQueryProvider#getSortKeysWithoutAliases return SqlPagingQueryUtils.buildSortClause(provider.getSortKeysWithoutAliases()); causes:

SELECT TMP_SUB.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY **JOB_EXECUTION_ID** DESC) AS ROW_NUMBER FROM resulting in com.microsoft.sqlserver.jdbc.SQLServerException: Ambiguous column name 'JOB_EXECUTION_ID'

The offending code is a part of batch framework.

Right, but there is a possibility of our usage upstream is causing this to surface.

...main/java/org/springframework/cloud/dataflow/server/repository/JdbcAggregateJobQueryDao.java

This commit adds a custom paging provider that is used only by the aggregate DAO. This is required because the standard paging provider that ships with Spring Batch 4.x does not properly handle sort key aliases when using nested ROW_NUMBER clauses. Resolves spring-attic#5531

README.md

onobc requested review from corneil and cppwfs and removed request for corneil December 15, 2023 17:50

onobc force-pushed the GH-5531-fix-oracle-page-nav branch from 716f5b5 to 8ca82f0 Compare December 15, 2023 17:54