I'm using the latest Spring boot 3.4.1. How to reproduce bug: 1) Create table in database: ``` @PostConstruct public void createTable() { final String sql = "CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(255), company " + "VARCHAR(255))"; jdbcTemplate.execute(sql); }

2) Use NamedParameterJdbcOperations for bulk update with this kinda query:

public void incorrectBulkUpdate(Map<String, List<String>> info) {
    final String sql = "UPDATE users SET company = :company WHERE name IN (:names)";
    SqlParameterSource[] params = info.entrySet().stream()
            .sorted((e1, e2) -> Integer.compare(e1.getValue().size(), e2.getValue().size())) // pay attention to this line!!!
            .map(e -> new MapSqlParameterSource()
                    .addValue("company", e.getKey())
                    .addValue("names", e.getValue())
            )
            .toArray(SqlParameterSource[]::new);
    jdbcOperations.batchUpdate(sql, params);
}

In general I want to use bulk update with one of query arguments is a collection (in this example argument :names is a list).
Also I sorted parameters with increasing size order of :names argument.
3) Try to execute it with different sized :names argument, for example:

@Transactional
@Test
void incorrectBulkUpdateTest() {
    simpleService.add("John", "google");
    simpleService.add("Nick", "facebook");
    simpleService.add("Anna", "netflix");

    var found = simpleService.findByName("John");

    assertEquals(1, found.size());
    assertEquals("google", found.get(0).company);

    simpleService.incorrectBulkUpdate(
            Map.of(
                    "amazon", List.of("Nick", "Anna"),
                    "tesla", List.of("John")
            )
    );

    found = simpleService.findByName("John");

    assertEquals(1, found.size());
    assertEquals("tesla", found.get(0).company);
}

And got something like:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [UPDATE users SET company = ? WHERE name IN (?)]; The column index is out of range: 3, number of columns: 2.


**But there is a twist.**
If I sort arguments in decreasing order, like

.sorted((e1, e2) -> -Integer.compare(e1.getValue().size(), e2.getValue().size()))

everything is fine and test is successful.
I think there is some bug in SQL processing, when query with named parameters is transformed into a query with question marks.

You can see full example in repo: https://github.com/koalaa13/SpringBootIssueExample

Thank you in advance!


**Comment From: quaff**

It's a Spring Framework not Boot issue.

Actual SQL is expanded by first `SqlParameterSource`.

if first param `names` has length 2 then it will be:
```sql
UPDATE users SET company = ? WHERE name IN (?, ?)

with params:

amazon,Nick,Anna
tesla,John,Anna --> Anna is unexpected

if first param names has length 1 then it will be:

UPDATE users SET company = ? WHERE name IN (?)

with params:

tesla,John
amazon,Nick,Anna --> Anna out of range

You should avoid using batchUpdate with Iterable as param, or make sure Iterable length is fixed.

Comment From: koalaa13

You should avoid using batchUpdate with Iterable as param, or make sure Iterable length is fixed.

Is there any info in docs about it? Because I didn't find anything and fixed this problem randomly actually.

Comment From: Alesiks

Hello! I have the same issue in a bit different situation. We use Postgres database and spring-jdbc version 6.1.3

Can be reproduced by next example: 1. CREATE TABLE IF NOT EXISTS events (id text PRIMARY KEY, tags text[]). Table has tags column with array datatype text[]|

  1. As in example above use NamedParameterJdbcOperations for bulk update with query:
insert into events ( id, tags)
values (:id, array[:tags]::text[])
on conflict do nothing

  1. And if inserted rows contain different number of tags then I got the same error as in example above. The column index is out of range: 4, number of columns: 3.

  2. Also sorting in descending order by tags size doesn't help -> it gets even worse, tags from previous row will be inserted for next one.

For example I insert two rows using batchUpdate

new Dbo("2", Set.of("a", "b", "c"))
new Dbo("1", Set.of("a", "b"))

and inserted vales are: Dbo(id=1, tags=[a, b, c]), Dbo(id=2, tags=[a, b, c])

Is this an expected behavior?

Comment From: quaff

Hello! I have the same issue in a bit different situation. We use Postgres database and spring-jdbc version 6.1.3

Can be reproduced by next example:

  1. CREATE TABLE IF NOT EXISTS events (id text PRIMARY KEY, tags text[]). Table has tags column with array datatype text[]|
  2. As in example above use NamedParameterJdbcOperations for bulk update with query:

insert into events ( id, tags) values (:id, array[:tags]::text[]) on conflict do nothing

  1. And if inserted rows contain different number of tags then I got the same error as in example above. The column index is out of range: 4, number of columns: 3.
  2. Also sorting in descending order by tags size doesn't help -> it gets even worse, tags from previous row will be inserted for next one.

For example I insert two rows using batchUpdate

new Dbo("2", Set.of("a", "b", "c")) new Dbo("1", Set.of("a", "b"))

and inserted vales are: Dbo(id=1, tags=[a, b, c]), Dbo(id=2, tags=[a, b, c])

Is this an expected behavior?

Have you tried to bind tags as param directly?

insert into events ( id, tags)
values (:id, :tags)
on conflict do nothing

Comment From: Alesiks

Have you tried to bind tags as param directly?

Thank you! Yes, it will work if I do like this

return new MapSqlParameterSource()
    .addValue("id", dbo.id())
    .addValue("tags", dbo.tags().toArray(new String[0]), Types.ARRAY);

Comment From: quaff

Have you tried to bind tags as param directly?

Thank you! Yes, it will work if I do like this

return new MapSqlParameterSource() .addValue("id", dbo.id()) .addValue("tags", dbo.tags().toArray(new String[0]), Types.ARRAY);

Spring will treat it as single param instead of expanding it to multiple params, expanding is problematic for batch update with unfixed length Iterable param.

Comment From: sdeleuze

Thanks for helping qualifying this issue @quaff, it looks like to me this is not a genuine issue so I will decline it. Please comment if you disagree.