NamedParameterUtils cannot bind reused parameters to anonymous bind markers

[Gardelll]

Steps

Create a table named foo:

CREATE TABLE `foo`
(
    `id`   BIGINT  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `test_key` VARCHAR(100) NOT NULL,
    `name` VARCHAR(100) NOT NULL
);

Create a query with this SQL:

SELECT `id`, `test_key`, `name` FROM `foo`
WHERE (`test_key` = :key AND `name` IN (:names)) 
  OR (`test_key` IN (:names) AND `name` = :key)

Stacktrace

java.lang.IllegalStateException: Parameter 3 has no binding
	at dev.miku.r2dbc.mysql.ParametrizedStatementSupport$Bindings.validatedFinish(ParametrizedStatementSupport.java:187)
	at dev.miku.r2dbc.mysql.ParametrizedStatementSupport$Bindings.access$100(ParametrizedStatementSupport.java:149)
	at dev.miku.r2dbc.mysql.ParametrizedStatementSupport.execute(ParametrizedStatementSupport.java:109)
	at dev.miku.r2dbc.mysql.ParametrizedStatementSupport.execute(ParametrizedStatementSupport.java:39)
	at org.springframework.r2dbc.core.StatementFilterFunction.lambda$static$0(StatementFilterFunction.java:45)
	at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$execute$3(DefaultDatabaseClient.java:375)
	at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:46)
	at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:31)
	at org.springframework.r2dbc.core.DefaultFetchSpec.lambda$all$2(DefaultFetchSpec.java:88)
	at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:46)
	at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:31)
	at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$6(DefaultDatabaseClient.java:138)
	...
	at io.r2dbc.pool.MonoDiscardOnCancel$MonoDiscardOnCancelSubscriber.onNext(MonoDiscardOnCancel.java:92)
	at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
	at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.complete(MonoIgnoreThen.java:292)
	at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onNext(MonoIgnoreThen.java:187)
	at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:236)
	at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203)
	at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89)
	at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onComplete(FluxHandleFuseable.java:236)
	at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1817)
	at reactor.core.publisher.MonoSupplier.subscribe(MonoSupplier.java:62)
	at reactor.core.publisher.Mono.subscribe(Mono.java:4397)
	at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263)
	at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)
	at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)
	at io.r2dbc.pool.MonoDiscardOnCancel.subscribe(MonoDiscardOnCancel.java:50)
        ...

Example Project

link

Clone and run that project use:

mvnw test

Do not forget change mysql credential in src/test/java/com/example/r2dbctest/FooRepoTest.java

Related code

In method:
org.springframework.data.r2dbc.core.NamedParameterUtils.ExpandedQuery#bind(org.springframework.r2dbc.core.binding.BindTarget, java.lang.String, java.lang.Object)

When the size of bindMarkers is larger than the parameter collection (which can be caused by two parameters with the same name), the remaining bindMarkers placeholders will not be bound.

[mp911de]

That issue should be ideally adressed in Spring Framework as well, specifically https://github.com/spring-projects/spring-framework/blob/main/spring-r2dbc/src/main/java/org/springframework/r2dbc/core/NamedParameterUtils.java.

The problem lies in the parameter expansion as we do not identify that a parameter collection should be bound twice. Instead of List<BindMarker> bindMarkers = getBindMarkers(identifier), we should have List<List<BindMarker>> bindMarkers = getBindMarkers(identifier) to iterate over the sets of bind markers to ensure proper binding. Otherwise, we would just consider binding the collection once.