spring-r2dbc can't correctly fill multiple named parameters with list

[Dieken]

https://github.com/spring-projects/spring-framework/blob/v7.0.0-M3/spring-r2dbc/src/main/java/org/springframework/r2dbc/core/NamedParameterUtils.java#L528

if (parameter.getValue() instanceof Collection collection) {
				Iterator<Object> iterator = collection.iterator();
				Iterator<BindMarker> markers = bindMarkers.iterator();
				while (iterator.hasNext()) {
					Object valueToBind = iterator.next();
					if (valueToBind instanceof Object[] objects) {
						for (Object object : objects) {
							bind(target, markers, object);
						}
					}
					else {
						bind(target, markers, valueToBind);
					}
				}
			}

For example, for SELECT ... WHERE fund_codeA in (:fundCodes) OR fund_codeB in (:fundCodes), fundCodes references a List, then the code above only fill the first occurrence of :fundCodes.

parameter = ["00001"]
markers = [?, ?]    // two occurrence

// iterate on parameter won't fill second "?"
// SELECT .... WHERE fund_codeA in (?)   OR fund_codeB in (?)

[Dieken]

Possible fix below, sorry my network speed to download gradle and dependencies is too slow, I haven't tested it.

$ git diff -w

diff --git a/spring-r2dbc/src/main/java/org/springframework/r2dbc/core/NamedParameterUtils.java b/spring-r2dbc/src/main/java/org/springframework/r2dbc/core/NamedParameterUtils.java

index f6696e8d9c..91ae7b35a4 100644
--- a/spring-r2dbc/src/main/java/org/springframework/r2dbc/core/NamedParameterUtils.java
+++ b/spring-r2dbc/src/main/java/org/springframework/r2dbc/core/NamedParameterUtils.java
@@ -529,6 +529,7 @@ abstract class NamedParameterUtils {
                        if (parameter.getValue() instanceof Collection collection) {
                                Iterator<Object> iterator = collection.iterator();
                                Iterator<BindMarker> markers = bindMarkers.iterator();
+                               do {
                                        while (iterator.hasNext()) {
                                                Object valueToBind = iterator.next();
                                                if (valueToBind instanceof Object[] objects) {
@@ -540,6 +541,13 @@ abstract class NamedParameterUtils {
                                                        bind(target, markers, valueToBind);
                                                }
                                        }
+
+                                       if (markers.hasNext()) {
+                                               iterator = collection.iterator();
+                                       } else {
+                                               break;
+                                       }
+                               } while (true);
                        }
                        else {
                                for (BindMarker bindMarker : bindMarkers) {

[sbrannen]

Hi @Dieken,

Congratulations on submitting your first issue for the Spring Framework! 👍

Before I noticed that you had submitted PR #34769, I put together some tests in an attempt to reproduce the behavior you described, and I pushed those in commit 018d3c9.

Those tests pass.

For example, the executeSelectWithReusedNamedParameterList() test method results in the following being logged at TRACE level when running H2DatabaseClientIntegrationTests.

Expanded SQL [SELECT * FROM legoset WHERE version IN ($1, $2, $3, $4) OR manual IN ($1, $2, $3, $4)]

That is expanded from the following SQL.

SELECT * FROM legoset WHERE version IN (:numbers) OR manual IN (:numbers)

Thus, I was not able to reproduce your claim.

If you would like us to investigate this further (or to consider your PR), please provide tests that fail before the proposed change and then pass after the proposed change.

Thanks,

Sam

[Dieken]

No idea why your tests can pass, I can reproduce the issue with test code below:

Demo project is created with
https://start.springboot.io/#!type=maven-project&language=java&platformVersion=3.4.4&packaging=jar&jvmVersion=21&groupId=com.example&artifactId=demo&name=demo&description=Demo%20project%20for%20Spring%20Boot&packageName=com.example.demo&dependencies=webflux,data-r2dbc,h2,mysql,lombok

demo/src/test/java/com/example/demo/DemoApplicationTests.java:

package com.example.demo;

import java.util.List;
import lombok.Data;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.r2dbc.core.DatabaseClient;

@SpringBootTest
class DemoApplicationTests {

    @Autowired
    private DatabaseClient db;

    @BeforeEach
    void setupDb() {
        db.sql("CREATE TABLE IF NOT EXISTS fund_info  (id int not null primary key, fund_code varchar(20) not null)")
                .then()
                .block();
    }

    @Test
    void contextLoads() {
    }

    @Test
    void testIt() {
        Req r = new Req();
        r.fundCodes = List.of("000001");

        db.sql("SELECT * FROM fund_info WHERE fund_code IN (:fundCodes) OR fund_code IN (:fundCodes)")
                //.bind("fundCodes", r.fundCodes)
                .bindProperties(r)
                .fetch()
                .all()
                .collectList()
                .block();
    }

    @Data
    static class Req {
        private List<String> fundCodes;
    }
}

[Dieken]

demo/src/main/resources/application.properties:

spring.application.name=demo

#spring.r2dbc.url=r2dbc:h2:mem://./testdb
spring.r2dbc.url=r2dbc:mysql://localhost/test
spring.r2dbc.username=root
spring.r2dbc.password=

[Dieken]

No idea why your tests can pass, I can reproduce the issue with test code below:

Demo project is created with https://start.springboot.io/#!type=maven-project&language=java&platformVersion=3.4.4&packaging=jar&jvmVersion=21&groupId=com.example&artifactId=demo&name=demo&description=Demo%20project%20for%20Spring%20Boot&packageName=com.example.demo&dependencies=webflux,data-r2dbc,h2,mysql,lombok

Seems there are multiple bugs: (UPDATED: the Lombok bug is caused by JDK 23, it works with JDK 21, so just ignore this case)

H2

1. With Lombok and bindProperties: FAIL

@Data
static class Req {
        private List<String> fundCodes;
}

Throws exception:

org.springframework.dao.InvalidDataAccessApiUsageException: No parameter specified for [fundCodes] in query [SELECT * FROM fund_info WHERE fund_code IN (:fundCodes) OR fund_code IN (:fundCodes)]
	at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.retrieveParameters(DefaultDatabaseClient.java:471)
	at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$getResultFunction$9(DefaultDatabaseClient.java:416)
	at org.springframework.r2dbc.core.ResultFunction.apply(ResultFunction.java:64)
	at org.springframework.r2dbc.core.DefaultFetchSpec.lambda$all$1(DefaultFetchSpec.java:77)
	at org.springframework.r2dbc.core.DelegateConnectionFunction.apply(DelegateConnectionFunction.java:48)
	at org.springframework.r2dbc.core.DelegateConnectionFunction.apply(DelegateConnectionFunction.java:33)
	at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$6(DefaultDatabaseClient.java:148)
	at reactor.core.publisher.FluxUsingWhen.deriveFluxFromResource(FluxUsingWhen.java:122)
	at reactor.core.publisher.FluxUsingWhen.access$000(FluxUsingWhen.java:53)
	at reactor.core.publisher.FluxUsingWhen$ResourceSubscriber.onNext(FluxUsingWhen.java:195)
	at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122)
	at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
	at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)

2. With plain JavaBean and bindProperties: PASS

static class Req {
        private List<String> fundCodes;

        public List<String> getFundCodes() {
            return fundCodes;
        }

        public void setFundCodes(List<String> fundCodes) {
            this.fundCodes = fundCodes;
        }
    }

3. bind("fundCodes", r.fundCodes): PASS

MySQL

1. With Lombok and bindProperties: FAIL

org.springframework.dao.InvalidDataAccessApiUsageException: No parameter specified for [fundCodes] in query [SELECT * FROM fund_info WHERE fund_code IN (:fundCodes) OR fund_code IN (:fundCodes)]
	at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.retrieveParameters(DefaultDatabaseClient.java:471)
	at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$getResultFunction$9(DefaultDatabaseClient.java:416)
	at org.springframework.r2dbc.core.ResultFunction.apply(ResultFunction.java:64)
	at org.springframework.r2dbc.core.DefaultFetchSpec.lambda$all$1(DefaultFetchSpec.java:77)
	at org.springframework.r2dbc.core.DelegateConnectionFunction.apply(DelegateConnectionFunction.java:48)
	at org.springframework.r2dbc.core.DelegateConnectionFunction.apply(DelegateConnectionFunction.java:33)
	at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$6(DefaultDatabaseClient.java:148)
	at reactor.core.publisher.FluxUsingWhen.deriveFluxFromResource(FluxUsingWhen.java:122)
	at reactor.core.publisher.FluxUsingWhen.access$000(FluxUsingWhen.java:53)
	at reactor.core.publisher.FluxUsingWhen$ResourceSubscriber.onNext(FluxUsingWhen.java:195)

2. With plain JavaBean and bindProperties: FAIL

java.lang.IllegalStateException: Parameter 1 has no binding
	at io.asyncer.r2dbc.mysql.ParameterizedStatementSupport$Bindings.validatedFinish(ParameterizedStatementSupport.java:198)
	at io.asyncer.r2dbc.mysql.ParameterizedStatementSupport$Bindings.access$100(ParameterizedStatementSupport.java:160)
	at io.asyncer.r2dbc.mysql.ParameterizedStatementSupport.execute(ParameterizedStatementSupport.java:112)
	at io.asyncer.r2dbc.mysql.ParameterizedStatementSupport.execute(ParameterizedStatementSupport.java:42)
	at org.springframework.r2dbc.core.StatementFilterFunction.lambda$static$0(StatementFilterFunction.java:45)
	at org.springframework.r2dbc.core.ResultFunction.apply(ResultFunction.java:65)
	at org.springframework.r2dbc.core.DefaultFetchSpec.lambda$all$1(DefaultFetchSpec.java:77)
	at org.springframework.r2dbc.core.DelegateConnectionFunction.apply(DelegateConnectionFunction.java:48)
	at org.springframework.r2dbc.core.DelegateConnectionFunction.apply(DelegateConnectionFunction.java:33)
	at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$6(DefaultDatabaseClient.java:148)
	at reactor.core.publisher.FluxUsingWhen.deriveFluxFromResource(FluxUsingWhen.java:122)
	at reactor.core.publisher.FluxUsingWhen.access$000(FluxUsingWhen.java:53)
	at reactor.core.publisher.FluxUsingWhen$ResourceSubscriber.onNext(FluxUsingWhen.java:195)

3. bind("fundCodes", r.fundCodes): FAIL

java.lang.IllegalStateException: Parameter 1 has no binding
	at io.asyncer.r2dbc.mysql.ParameterizedStatementSupport$Bindings.validatedFinish(ParameterizedStatementSupport.java:198)
	at io.asyncer.r2dbc.mysql.ParameterizedStatementSupport$Bindings.access$100(ParameterizedStatementSupport.java:160)
	at io.asyncer.r2dbc.mysql.ParameterizedStatementSupport.execute(ParameterizedStatementSupport.java:112)
	at io.asyncer.r2dbc.mysql.ParameterizedStatementSupport.execute(ParameterizedStatementSupport.java:42)
	at org.springframework.r2dbc.core.StatementFilterFunction.lambda$static$0(StatementFilterFunction.java:45)
	at org.springframework.r2dbc.core.ResultFunction.apply(ResultFunction.java:65)
	at org.springframework.r2dbc.core.DefaultFetchSpec.lambda$all$1(DefaultFetchSpec.java:77)
	at org.springframework.r2dbc.core.DelegateConnectionFunction.apply(DelegateConnectionFunction.java:48)
	at org.springframework.r2dbc.core.DelegateConnectionFunction.apply(DelegateConnectionFunction.java:33)
	at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$6(DefaultDatabaseClient.java:148)
	at reactor.core.publisher.FluxUsingWhen.deriveFluxFromResource(FluxUsingWhen.java:122)
	at reactor.core.publisher.FluxUsingWhen.access$000(FluxUsingWhen.java:53)
	at reactor.core.publisher.FluxUsingWhen$ResourceSubscriber.onNext(FluxUsingWhen.java:195)