Missing support for VALUES list in SELECT statements

[roookeee]

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

[schauder]

This should get implemented in JdbcTemplate, which already does the same thing for IN.

Please create a new issue over https://github.com/spring-projects/spring-framework/issues and leave a link to it here.

Thanks.

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[roookeee]

I created an issue in spring framework.

[schauder]

Thanks