Using JdbcPagingItemReader causing join statements causing MySQLIntegrityConstraintViolationException

Question

I am running Spring Batch and using JdbcPagingItemReader. With a sample config of :

<bean id="dogQueryProvider"  class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
  <property name="databaseType" value="mysql" />
  <property name="dataSource" ref="dataSource" />
  <property name="selectClause"
   value="SELECT owner.id as ownerid, first_name, last_name, dog_name " />
  <property name="fromClause"
   value="FROM dog_owner owner INNER JOIN dog ON owner.id = dog.id " />
  <property name="sortKey" value="owner.id" />
</bean>

I am getting an error related to:

Column 'id' in order clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'id' in order clause is ambiguous.

Take it that dog_owner and dog tables have id columns. I am thinking that this is related to AbstractSqlPagingQueryProvider.getSortKeysWithoutAliases, which I think strips off owner from owner.id specified as sortKey. Any suggestions to resolve this issue?

score 3 · Answer 1 · answered Feb 07 '17 at 08:02

Apparently this seems to be happening still in some form but there is way to craft your query so that the alias won't get removed. You rename the joined column by selecting it with different name.

Your selectClause becomes

SELECT owner.id, owner.first_name, owner.last_name, dog.dog_name

FromClause becomes

      FROM dog_owner owner
INNER JOIN (SELECT d2.id AS dogId, d2.dog_name
              FROM dog d2) AS dog
        ON owner.id = dog.dogId

Now you should be able to use "id" as sortKey as there is only one column with name "id". This works even if you add the dog.dogId into the selectClause.

why sortkey is mandatory in spring batch.. any reason? ideally it should be optional or by default some ascending order if in case... i don't understand the reason spring is forcing developers for sort key — Stunner, Oct 31 '18 at 06:52

score 1 · Answer 2 · answered Mar 11 '15 at 18:21

1

You are correct in the reasoning why it is occurring. In your case, I'd expect using your alias to fix the issue. So you've aliased owner.id as ownerid, however you don't use it in the sortkey field (or the join clause for that matter). Use the alias and you should be ok.

answered Mar 11 '15 at 18:21

Michael Minella

20,843
4
55
67

1

Thanks for the feedback. I had tried that initially. I think that works for having a working query for firstPageSql query but remainingPagesSql generated seems invalid as i get something like WHERE ((ownerid > ?)) in the query causing MySQLSyntaxErrorException: Unknown column 'ownerid ' in 'where clause'. Not sure if am missing a config. A temporary fix was to override MySqlPagingQueryProvider. I would have thought this use case to be common – Dayo Mar 11 '15 at 19:58
I actually think that is a bug. I've created Jira issue BATCH-2360 to track it (https://jira.spring.io/browse/BATCH-2360). – Michael Minella Mar 13 '15 at 16:58
Just FYI. I ran into the same error when testing using H2 Database. I think H2PagingQueryProvider still does not have the fix. – Wizard Oct 07 '17 at 20:37

score 0 · Answer 3 · answered Nov 14 '19 at 23:49

We ran into this issue, Spring must get your sort key from the returned result set in order to page correctly. Under the hood sort key gets used in a WHERE clause which will not work with column aliases since WHERE gets processed before SELECT statements at the database level.

Our solution was to SELECT owner.id as "owner.id" so that the sort key can be retrieved from the result set using the same name. Doing SELECT owner.id puts the column id into your result set which in your case leads to issues with ambiguous columns. We preferred this approach to the previous answer's nested query.

Using JdbcPagingItemReader causing join statements causing MySQLIntegrityConstraintViolationException

3 Answers3

Linked