How to specify an `order by` on ambiguous column using spring batch and postgres?

Question

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:

Postgres does not allow aliases in the where clause, such as i.
Postgres does not allow ambiguous columns in the 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 same
That call to ResultSet.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.

Edit

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

score 2 · Answer 1 · answered Nov 15 '19 at 00:00

We ran into this issue, if you alias the fully qualified column name in your select it will work. Your analysis is correct, and if you do select ta.my_id as "ta.my_id" then the value you need gets returned in the result set and the same sort key can be referenced in where and order by clauses.

jmmut · Accepted Answer · 2017-11-13T10:05:35.670

1

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.

edited Nov 13 '17 at 10:05

answered Nov 13 '17 at 09:49

jmmut

884
9
19

The keys of the `JdbcPagingItemReader` must be what is sorted (since they must be unique). Because of that, throwing an order by on the end like that won't work. That's not to say you can't do an order using that reader, it just takes some additional gymnastics to do so. – Michael Minella Nov 17 '17 at 20:57
I'm not sure I understand what you mean. Are you saying I shouldn't use Jdbc**Cursor**ItemReader with an order by? – jmmut Nov 21 '17 at 10:45
I'm not saying that at all. I was just answering the question you identified as being still unanswered. – Michael Minella Nov 21 '17 at 15:21

score 0 · Answer 3 · answered Mar 22 '22 at 16:31

0

You can try as below with alias and can use paginated reader

select ta.my_id as \"ta.my_id\" from table_a ta join table_b tb on ta.my_id = tb.my_id where ta.unrelated_field = 42 order by ta.my_id;

answered Mar 22 '22 at 16:31

user18544897

1

How to specify an `order by` on ambiguous column using spring batch and postgres?

Edit

3 Answers3

Linked