NamedParameterJdbcOperations batchUpdate: column index is out of range

[koalaa13]

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!

[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:

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.

[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.

[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[]|

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

3. 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.

4. 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?

[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

3. 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.
4. 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

[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);

[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.

[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.