[SOLVED] JDBI/Postgress Select query failed with datetime comparison

JDBI/Postgress Select query failed with datetime comparison

Postgress Version: 10.4

table column name/type: updated_at timestamp(6) with time zone

SQL query:

final String SELECT_PAYMENT_INFO_QUERY =
    "SELECT i.code, p.* 
    FROM information i 
    INNER JOIN product p ON i.ref = p.information_ref 
    WHERE p.reference = :reference AND p.type = :paymentMethodType AND p.error_state = :errorState 
    AND p.updated_at IS NOT NULL AND p.updated_at >= :queryFromTime 
    ORDER BY p.updated_at ASC 
    FETCH FIRST :numberOfRows ROWS ONLY"

Java Method:

    public List<ProductInformationDTO> retrieveProductInformations(
      ZonedDateTime fetchFromTime, int noOfRecords)
  {
    try
    {
      return dbi.inTransaction((HandleCallback<List<ProductInformationDTO>, Exception>) handle ->
      {
        final List<ProductInformationDTO> productInfos = handle.createQuery(SELECT_PAYMENT_INFO_QUERY)
            .bind("reference", "ABCD")
            .bind("paymentMethodType", "CREDIT_CARD")
            .bind("errorState", "COMPLETED_WITH_ERRORS")
            .bind("queryFromTime", fetchFromTime.toOffsetDateTime())
            .bind("numberOfRows", noOfRecords)
            .map(productInfoMapper)
            .list();

        return productInfos;
      });
    }
    catch (Exception e)
    {
      LOG.error("Failed to complete the retrieve operation.", e);
      throw new TransactionException(e);
    }
  }

This Query and method work fine with the H2 database. However, when it is tested with the Postgress database, I get the below exception

org.jdbi.v3.core.statement.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$5" Position: 234 [statement:"SELECT i.code, p.* FROM information i INNER JOIN product p ON i.ref = p.information_ref WHERE p.reference = :reference AND p.type = :paymentMethodType AND p.error_state = :errorState AND p.updated_at IS NOT NULL AND p.updated_at >= :queryFromTime ORDER BY p.updated_at ASC FETCH FIRST :numberOfRows ROWS ONLY", arguments:{positional:{}, named:{queryFromTime:2021-04-15T16:28:20.365795+12:00,numberOfRows:10,paymentMethodType:CREDIT_CARD,reference:ABCD,errorState:COMPLETED_WITH_ERRORS}, finder:[]}] at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1794) at org.jdbi.v3.core.result.ResultProducers.lambda$getResultSet$2(ResultProducers.java:64) at org.jdbi.v3.core.result.ResultIterable.lambda$of$0(ResultIterable.java:54) at org.jdbi.v3.core.result.ResultIterable.stream(ResultIterable.java:228) at org.jdbi.v3.core.result.ResultIterable.collect(ResultIterable.java:284) at org.jdbi.v3.core.result.ResultIterable.list(ResultIterable.java:273) at au.com.abcd.products.v2.database.store.dao.ProductsDaoImpl.lambda$retrieveProductInformations$6(ProductsDaoImpl.java:260) at org.jdbi.v3.core.Handle.inTransaction(Handle.java:424) at org.jdbi.v3.core.Jdbi.lambda$inTransaction$4(Jdbi.java:375) at org.jdbi.v3.core.Jdbi.withHandle(Jdbi.java:341) at org.jdbi.v3.core.Jdbi.inTransaction(Jdbi.java:375) at au.com.abcd.products.v2.database.store.dao.ProductsDaoImpl.retrievePaymentIntents(IntentDaoImpl.java:251) at au.com.abcd.products.v2.business.services.complete.ProductServiceImpl.reProcessProducts(ProductServiceImpl.java:114) at au.com.abcd.products.v2.business.background.TransactionReProcessManager.lambda$reProcessProducts$1(TransactionReProcessManager.java:81) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$5" Position: 234 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:142) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy120.execute(Unknown Source) at org.jdbi.v3.core.statement.SqlLoggerUtil.wrap(SqlLoggerUtil.java:31) at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1786) ... 16 common frames omitted

I tried with fetchFromTime.toLocalDateTime() as well but get the same exception.

Solution

You can't use a positional parameter in the FETCH FIRST (or LIMIT) clause. One workaround, assuming you can tolerate running a native query, would be to use ROW_NUMBER instead:

SELECT *
FROM
(
    SELECT i.code, p.*, ROW_NUMBER() OVER (ORDER BY p.updated_at) rn
    FROM information i 
    INNER JOIN product p ON i.ref = p.information_ref 
    WHERE p.reference = :reference AND p.type = :paymentMethodType AND
          p.error_state = :errorState AND p.updated_at IS NOT NULL AND
          p.updated_at >= :queryFromTime 
) t
WHERE rn <= :numberOfRows