Styjun

When sending plain SQL to PostgreSQL like the following:

CREATE TABLE things (
 id BIGINT PRIMARY KEY NOT NULL,
 foo BIGINT NOT NULL,
 bar BIGINT NOT NULL
);


INSERT INTO things VALUES (9900, 1, 2);
INSERT INTO things VALUES (9901, 3, 4);
INSERT INTO things VALUES (9902, 1, 4);
SELECT * FROM things WHERE foo IN (1, 2);
SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4));

it works as expected.

However when trying to do the same thing using Spring Boot, it fails. Here is my minimal example (Kotlin):

The entity:

import org.springframework.data.jpa.domain.AbstractPersistable
import javax.persistence.Entity
import javax.persistence.Table

@Entity
@Table(name = "things")
class Thing(
 val foo: Long,
 val bar: Long
) : AbstractPersistable<Long>()

The repository:

import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query

interface ThingRepository : JpaRepository<Thing, Long> 

 @Query(nativeQuery = true, value = "SELECT * FROM things WHERE foo IN (1, 2);")
 fun selectNativeByFoo(): Iterable<Thing>

 @Query(nativeQuery = true, value = "SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4));")
 fun selectNativeByFooAndBar(): Iterable<Thing>

A call to selectNativeByFoo works fine, but calling selectNativeByFooAndBar fails with the following exception:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4));]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement


Caused by: org.h2.jdbc.JdbcSQLException: Invalid parameter count for "VALUES", expected count: "1"; SQL statement:
SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4)); [7001-197]

What am I doing wrong?

Thanks to this comment by JB Nizet, I was able to solve it.

The syntax used

SELECT * FROM things WHERE (foo, bar) IN (VALUES (1, 2), (3, 4));

is only supported by real PostgreSQL, not by H2, which I was using for my tests, even with

spring.datasource.url: jdbc:h2:mem:db;MODE=PostgreSQL

Changing it to

SELECT * FROM things WHERE (foo, bar) IN ((1, 2), (3, 4));

makes it work in both, real Postgres and H2 in Postgres mode.

Another option is to switch to embedded-database-spring-test and use it's embedded Postgres for testing. Then the original syntax (using VALUES) also works.