Description
Currently spring-data-jdbc does not support SELECT statements that use the VALUES keyword:
interface MyRepository {
@Query("SELECT * FROM my_entity WHERE id IN (VALUES (:ids))")
List<MyEntity> myQuery(List<String> ids)
}:ids is not properly expanded (it needs to put every list entry into ()) and thus generates wrong SQL.
I know this would be complex to support for complex objects types as IN-statements works with tuples in some databases but the simple, one-valued variant should be pretty straightforward.
At least PostgreSQL generates different plans for a simple IN vs IN VALUES clause, especially when the input list is big (>100) which perform quite differently (10-30% worse for us).
Using a VALUES list is also interesting when using CTE (WITH) to populate a temporary table with user provided input, this is not achievable with an IN statement.
Disclaimer: I haven't checked if VALUES expansion works in custom INSERTS