IN Clause Binding Throws Exceptions Again

sshark commented 3 years ago

Bug Report

I found similar issues with IN clause errors that were resolved but I could not get my queries to work correctly,

Versions

Driver: r2dbc-mysql-0.8.2.RELEASE
Database: MySQL
Java: JDK 11.0.11
OS: Linux

Current Behavior

I used the setup, spring-boot-starter:2.4.5, spring-data-r2dbc:1.2.8, and JDK 11.0.11, to demonstrate.

@Query("select * from Node where Group in (?groups) or Group in (?groups)") // 1
Flux<Node> findByNodes(List<Integer> ids, List<Integer> groups);

@Query("select * from Node where Group in (:groups) or Group in (:groups)") // 2
Flux<Node> findByNodes(List<Integer> ids, List<Integer> groups);

@Query("select * from NodeMaster where NodeGroup in (?nodeGroups)") // 2A
Flux<NodeMaster> findByNodeGroups(List<Integer> nodeGroups);

Queries (1) & (2A), using ?, and (2), using :, threw,

Caused by: java.lang.IllegalArgumentException: Cannot encode value of type 'class java.util.ArrayList'
    at dev.miku.r2dbc.mysql.codec.DefaultCodecs.encode(DefaultCodecs.java:178)
    at dev.miku.r2dbc.mysql.ParametrizedStatementSupport.bind(ParametrizedStatementSupport.java:72)
    at dev.miku.r2dbc.mysql.ParametrizedStatementSupport.bind(ParametrizedStatementSupport.java:39)
    at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$bindByIndex$6(DefaultDatabaseClient.java:442)
    at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
    at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.bindByIndex(DefaultDatabaseClient.java:439)
    at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$execute$2(DefaultDatabaseClient.java:360)
    at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$execute$3(DefaultDatabaseClient.java:374)
    at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:46)

I presume ? does not support Java Collection in general and require a conversion

  @Query("select * from Node where Group in (?groups) or NodeUID in (?ids)") // 3
  Flux<Node> findByNodes(List<Integer> ids, List<Integer> groups);

Query (3) threw,

  Caused by: java.lang.IllegalArgumentException: Cannot encode value of type 'class java.util.ArrayList'
    at dev.miku.r2dbc.mysql.codec.DefaultCodecs.encode(DefaultCodecs.java:178)
    at dev.miku.r2dbc.mysql.ParametrizedStatementSupport.bind(ParametrizedStatementSupport.java:72)
    at dev.miku.r2dbc.mysql.ParametrizedStatementSupport.bind(ParametrizedStatementSupport.java:39)

@Query("select * from Node where Group in (:groups) or NodeUID in (:groups)") // 4
Flux<Node> findByNodes(List<Integer> groups);  

  @Query("select * from Node where Group in (?groups) or NodeUID in (?groups)") // 5
  Flux<Node> findByNodes(List<Integer> groups);

Queries (4) and (5) threw,

reactor.core.Exceptions$ErrorCallbackNotImplemented: java.lang.IllegalStateException: Parameter 3 has no binding
Caused by: 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)

However, these queries were fine,

@Query("select * from Node where Group in (:groups) or Group in (:ids)") // 6
Flux<Node> findByNodes(List<Integer> ids, List<Integer> groups);

@Query("select * from Node where Group in (:groups) or NodeUID in (:ids)") // 7
Flux<Node> findByNodes(List<Integer> ids, List<Integer> groups);

@Query("select * from NodeMaster where NodeGroup in (:nodeGroups)")
Flux<NodeMaster> findByNodeGroups(List<Integer> nodeGroups);

Table schema

CREATE TABLE `Node`
(
   `NodeUID`             INT(11) AUTO_INCREMENT PRIMARY KEY,
   `Group`               INT(11) NOT NULL
...
);

Expected behavior/code

I expect the queries with repeated parameters to behave the same as queries (6) and (7) or suggest alternative query notations

mp911de commented 3 years ago

These seem to be Spring-specific issues as Spring expands named parameters (:foo) that are collection-typed into individual placeholders. The driver generally doesn't accept Collection parameters to be bound. What should ?groups represent? Generally, SQL Server supports only named parameters prefixed with @ (such as @P0).

Since this ticket isn't actionable here, closing it. Please create an issue for bind parameter reuse in Spring Framework.

sshark commented 3 years ago

My bad. This should be reported in MySQL R2DBC instead of MSSQL. Should I report in Spring Framework or r2dbc-mysql? Thanks

mp911de commented 3 years ago

Thanks for the detail. MySQL is different from the other databases as it uses anonymous bind markers only (?). These can be bound by index only and it is not possible to bind the same value to a bind marker that is used multiple times.

By using the bind marker multiple times I mean:

... WHERE foo IN (@p0, @p1) OR bar IN (@p0, @p1)

And we call Statement.bind("@p0", ...).bind("@p1", ...) to pass on parameters to the driver.

Anonymous bind markers are bound by their ordinal position which requires something like Statement.bind(0, ...).bind(1, ...).bind(2, ...).bind(3, ...) as it is not possible to refer within the SQL statement to the same parameter.

I think it would be possible to work around this limitation to some extent but it requires a lot of changes in the binding algorithm and it would only work for scalar types, not for ByteBuffer or Blob/Clob types as those can be consumed only once.

That being said, I recommend rewriting your query into a form where you pass in parameters multiple times.

r2dbc / r2dbc-mssql