R2DBC: Generated query SQL neglects custom column names

[sunny-chung]

Not sure if this is the right place to report a R2DBC issue.

I have a spring data repository like this:

interface ChatChannelParticipantRepository : BaseRepository<ChatChannelParticipant> {

    suspend fun countByChannelIdAndUserIdAndActive(channelId: String, userId: String, active: Boolean): Long
}

@NoRepositoryBean
interface BaseRepository<T : BaseEntity> : CoroutineCrudRepository<T, String>

Entities like this:

@Table(name = "message__chat_channel_participant")
data class ChatChannelParticipant(
    val channelId: String,
    val userId: String,
    val role: Role
) : BaseEntity()

abstract class BaseEntity {
    @Id
    var id: String = UUID.randomUUID().toString()

    @Column(value = "is_active")
    var isActive: Boolean = true
}

When I execute the query defined in the repository, I got this exception:

org.springframework.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar [SELECT COUNT(message__chat_channel_participant.id) FROM message__chat_channel_participant WHERE message__chat_channel_participant.channel_id = ? AND (message__chat_channel_participant.user_id = ?) AND (message__chat_channel_participant.active = ?)]
    at org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:243) ~[spring-r2dbc-6.0.11.jar!/:6.0.11]
    Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException:
Error has been observed at the following site(s):
    *__checkpoint ⇢ Handler com.example.api.MessageApi#list(String, Instant, Instant, int, String, Continuation) [DispatcherHandler]
Original Stack Trace:
        at org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:243) ~[spring-r2dbc-6.0.11.jar!/:6.0.11]
        at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$8(DefaultDatabaseClient.java:151) ~[spring-r2dbc-6.0.11.jar!/:6.0.11]
        at reactor.core.publisher.Flux.lambda$onErrorMap$28(Flux.java:7236) ~[reactor-core-3.5.9.jar!/:3.5.9]

...

Caused by: io.r2dbc.spi.R2dbcBadGrammarException: Unknown column 'message__chat_channel_participant.active' in 'where clause'
    at io.asyncer.r2dbc.mysql.message.server.ErrorMessage.toException(ErrorMessage.java:108) ~[r2dbc-mysql-1.0.2.jar!/:1.0.2]
    at io.asyncer.r2dbc.mysql.message.server.ErrorMessage.toException(ErrorMessage.java:73) ~[r2dbc-mysql-1.0.2.jar!/:1.0.2]
    at io.asyncer.r2dbc.mysql.MySqlResult$MySqlMessage.exception(MySqlResult.java:185) ~[r2dbc-mysql-1.0.2.jar!/:1.0.2]
    at io.asyncer.r2dbc.mysql.MySqlResult.lambda$map$1(MySqlResult.java:99) ~[r2dbc-mysql-1.0.2.jar!/:1.0.2]
    at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:113) ~[reactor-core-3.5.9.jar!/:3.5.9]
    at reactor.core.publisher.FluxHandle$HandleConditionalSubscriber.onNext(FluxHandle.java:343) ~[reactor-core-3.5.9.jar!/:3.5.9]
    at reactor.core.publisher.FluxContextWriteRestoringThreadLocals$ContextWriteRestoringThreadLocalsSubscriber.onNext(FluxContextWriteRestoringThreadLocals.java:116) ~[reactor-core-3.5.9.jar!/:3.5.9]
    at io.asyncer.r2dbc.mysql.internal.util.DiscardOnCancelSubscriber.onNext(DiscardOnCancelSubscriber.java:66) ~[r2dbc-mysql-1.0.2.jar!/:1.0.2]
    at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drainRegular(FluxWindowPredicate.java:670) ~[reactor-core-3.5.9.jar!/:3.5.9]

...

The result is the same regardless of the existence of the @Column annotation.

Versions: Kotlin 1.8.22 JVM Target 17 Spring Data Commons 3.1.2 Spring Data R2dbc 3.1.2

[mp911de]

You do not use the property that you think you're using.

The property is declared with the name isActive while your query method uses a property named active. Kotlin renders a method isActive that introduces another property with the name active but without the annotation as the annotation resides at the field isActive.

This is an unfortunate arrangement as you end up with two properties without an obvious way to detect that mismatch.

[sunny-chung]

Yes, it works if I change to countByChannelIdAndUserIdAndIsActive.

JetBrains' Spring Data plugin incorrectly reports the property "isActive" cannot be resolved, and only "Active" is available for autocompletion. But this has nothing to do with Spring Data.

Could Spring Data include a boolean property as an example in the Kotlin support section of the documentation?

And, for a Kotlin boolean property named isXxx, would Spring Data consider to drop supporting Xxx as a property?

[mp911de]

Is there some Kotlin documentation that explains isFoo to Foo property name rewriting?

[sunny-chung]

This?

https://kotlinlang.org/docs/java-to-kotlin-interop.html#properties

If the name of the property starts with is, a different name mapping rule is used: the name of the getter will be the same as the property name, and the name of the setter will be obtained by replacing is with set. For example, for a property isOpen, the getter will be called isOpen() and the setter will be called setOpen(). This rule applies for properties of any type, not just Boolean.

But I guess we are focusing on Kotlin native support, not Java compatibility.