Skip to content
New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Query method with bind parameter List<byte[]> does no longer work #1900

Closed
kunjiyan opened this issue Sep 28, 2024 · 2 comments
Closed

Query method with bind parameter List<byte[]> does no longer work #1900

kunjiyan opened this issue Sep 28, 2024 · 2 comments
Assignees
Labels
type: regression A regression from a previous release

Comments

@kunjiyan
Copy link

Hello, the following example doesn't work any more after upgrading spring-boot version from 3.3.2 to 3.3.3 / 3.3.4.
uuid is a BINARY(16) column in a table in MariaDB 10.6.18.

@Query(
      "(SELECT "
          + ALL_COLUMNS
          + " FROM user WHERE uuid IN (:uuids)) "
  List<User> findAllByUuids(List<byte[]> uuids);

It looks like the problem is caused by this fix in the class org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.

I debugged a bit and it looks like in the version 3.3.3 in the function StringBasedJdbcQuery.bindParameters(RelationalParameterAccessor accessor) a new function writeValue is called which iterates through each byte[] using writeJdbcValue and gets a jdbcValue for each byte in the array - jdbcType VARBINARY. The end result of the bindParameters' value is ArrayList<Object[16]> with Byte items.

If I understand correctly in the 3.3.2 version the bindParameters function uses convertAndAddParameter which calls writeJdbcValue for a byte[] and the result jdbcType is BINARY. The result value of bindParameters is ArrayList<byte[]>.

The error for List.of([-8, 7, -57, -71, -23, -77, 70, 24, -70, -19, -113, 98, -9, 0, 121, 119])

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar 
Caused by: java.sql.SQLSyntaxErrorException: (conn=714) Could not convert [-8] to -3

Maybe a check for treating List<byte[]> should be added to differentiate from the tuple processing ?
Thanks in advance !

@kunjiyan kunjiyan changed the title Query methods with bind parameter List<byte[]> does no longer work Query method with bind parameter List<byte[]> does no longer work Sep 28, 2024
@spring-projects-issues spring-projects-issues added the status: waiting-for-triage An issue we've not yet triaged label Sep 28, 2024
@mp911de mp911de added type: regression A regression from a previous release and removed status: waiting-for-triage An issue we've not yet triaged labels Sep 30, 2024
@schauder
Copy link
Contributor

schauder commented Oct 1, 2024

If you are dealing with UUIDs it assume that actually using the UUID type both in the database an in Java probably does work better.

That said, I still consider this a regression and we'll look into it.

@frivoire
Copy link

frivoire commented Oct 1, 2024

If you are dealing with UUIDs it assume that actually using the UUID type both in the database an in Java probably does work better.

I totally agree with this, it will be simpler 👍
Sadly, the UUID type on MariaDB is not available until a fairly recent version (10.7 which is not LTS, so 10.11 in LTS version => released in 2023), thus not applicable in all environments yet 😿

schauder added a commit that referenced this issue Oct 2, 2024
schauder added a commit that referenced this issue Oct 2, 2024
byte[] is mapped to BINARY data and is not considered tuple data.

The explicit check for byte[] is not very nice but matches the special handling in MappingJdbcConverter.writeJdbcValue

Closes #1900
@mp911de mp911de added this to the 3.3.5 (2024.0.5) milestone Oct 2, 2024
mp911de pushed a commit that referenced this issue Oct 2, 2024
byte[] is mapped to BINARY data and is not considered tuple data.

The explicit check for byte[] is not very nice but matches the special handling in MappingJdbcConverter.writeJdbcValue

Closes #1900
Original pull request: #1903
mp911de added a commit that referenced this issue Oct 2, 2024
Refine tests. Extend comment for future-me.

See #1900
Original pull request: #1903
@mp911de mp911de closed this as completed in ae86518 Oct 2, 2024
mp911de added a commit that referenced this issue Oct 2, 2024
Refine tests. Extend comment for future-me.

See #1900
Original pull request: #1903
# for free to join this conversation on GitHub. Already have an account? # to comment
Labels
type: regression A regression from a previous release
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants