I'm extending a JdbcPagingItemReader to do a query that sorts by a column, but it is not working, it seems spring batch and postgres create incompatible constraints.
In essence, the query (created using a SqlPagingQueryProviderFactoryBean) is:
select ta.my_id as i
from table_a ta join table_b tb on ta.my_id = tb.my_id
where ta.unrelated_field = 42
order by i;
but this fails on the second page of results because the PostgresPagingQueryProvider modifies it here (called from here) to where (ta.unrelated_field = 42) AND ((i > the_last_value_in_the_previous_page)), and postgres does not allow putting column aliases (i) in the where clause.
I have tried other ways to specify the sorting key, but I don't see how any way could work because of the next constraints:
where clause, such as i.order by clause, such as my_id, as it can refer to the column from 2 tables.JdbcPagingItemReader assumes here that the sort key and the column in the select clause are called exactly the sameResultSet.getObject will compare the key with a list of column names without the table qualifier, so getObject("ta.my_id") won't work.So, how can I make my initial query work? did I miss something? or is this a bug in spring batch? I can not rename my_id so that it is different in each table.
This question faces the exact same issue, but in his case he could use the unqualified column name, which doesn't work in my case, because the column appears in several tables.
More details:
To provide actual code and error messages, below there's the actual error I get when I use the alias in the order by clause (the i in the example is ss_id in the code, and my_id is subsnp_id, the code is here):
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT distinct sub.subsnp_id AS ss_id,loc.snp_id AS rs_id,hgvs.hgvs_c AS hgvs_c_string,hgvs.start_c+1 AS hgvs_c_start,hgvs.stop_c+1 AS hgvs_c_stop,hgvs.ref_allele_c AS reference_c,hgvs.hgvs_t AS hgvs_t_string,hgvs.start_t+1 AS hgvs_t_start,hgvs.stop_t+1 AS hgvs_t_stop,hgvs.ref_allele_t AS reference_t,hgvs.var_allele AS alternate,obsvariation.pattern AS alleles,ctg.contig_name AS contig_name,loc.asn_from +1 AS contig_start,loc.asn_to +1 AS contig_end,loc.loc_type AS loc_type,ctg.contig_chr AS chromosome,loc.phys_pos_from + 1 AS chromosome_start,loc.phys_pos_from + 1 + loc.asn_to - loc.asn_from AS chromosome_end,batch.loc_batch_id_upp AS batch_name,CASE WHEN hgvs.orient_c = 2 THEN -1 ELSE 1 END AS hgvs_c_orientation,CASE WHEN hgvs.orient_t = 2 THEN -1 ELSE 1 END AS hgvs_t_orientation,CASE WHEN loc.orientation = 1 THEN -1 ELSE 1 END AS snp_orientation,CASE WHEN ctg.orient = 1 THEN -1 ELSE 1 END AS contig_orientation,CASE WHEN link.substrand_reversed_flag = 1 THEN -1 ELSE 1 END AS subsnp_orientation FROM b150_snpcontigloc loc JOIN b150_contiginfo ctg ON ctg.ctg_id = loc.ctg_id JOIN snpsubsnplink link ON loc.snp_id = link.snp_id JOIN subsnp sub ON link.subsnp_id = sub.subsnp_id JOIN batch on sub.batch_id = batch.batch_id JOIN b150_snphgvslink hgvs ON hgvs.snp_link = loc.snp_id JOIN dbsnp_shared.obsvariation ON obsvariation.var_id = sub.variation_id WHERE (batch.batch_id = ? AND ctg.group_term IN (?) AND ctg.group_label LIKE ?) AND ((ss_id > ?)) ORDER BY ss_id ASC LIMIT 100]; nested exception is org.postgresql.util.PSQLException: ERROR: column "ss_id" does not exist
Position: 1472
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:684) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:711) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:761) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:199) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.batch.item.database.JdbcPagingItemReader.doReadPage(JdbcPagingItemReader.java:218) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar!/:3.0.8.RELEASE]
at org.springframework.batch.item.database.AbstractPagingItemReader.doRead(AbstractPagingItemReader.java:108) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar!/:3.0.8.RELEASE]
at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.read(AbstractItemCountingItemStreamItemReader.java:88) ~[spring-batch-infrastructure-3.0.8.RELEASE.jar!/:3.0.8.RELEASE]
at uk.ac.ebi.eva.dbsnpimporter.io.readers.SubSnpCoreFieldsReader.read(SubSnpCoreFieldsReader.java:131) ~[classes!/:0.2-SNAPSHOT]
... 59 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: column "ss_id" does not exist
Position: 1472
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116) ~[postgresql-9.4.1212.jre7.jar!/:9.4.1212.jre7]
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:692) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-4.3.10.RELEASE.jar!/:4.3.10.RELEASE]
... 68 common frames omitted
The solution I took was to use JdbcCursorItemReader instead of JdbcPagingItemReader, which solves my problem but does not answer if JdbcPagingItemReader is meant to (not) work in this use case.
The changes I made to our code is here.