SQLServer many to many relationship update breaks when updating from exposed 0.26.2 to 0.27.1

[Froidoh]

Hello!

Running an sqlserver with exposed "0.26.2" I wanted to update to a newer version but ALL newer versions break on this code, at runtime:

Three tables, modeling a Many-to-Many relationship between A and B

return transaction {
	val toUpdate = A.findById(input.id) ?: return@transaction null

	val newBs: List<B> = ...// Calculate some updates based on business logic
	toUpdate.bs = SizedCollection(newBs)

	toUpdate
}

in 0.26.2 this works fine. From 0.27.1 onwards I get a runtime exception "Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set."

org.jetbrains.exposed.exceptions.ExposedSQLException: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:63)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:129)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:115)
	at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:29)
	at org.jetbrains.exposed.sql.QueriesKt.batchInsert(Queries.kt:137)
	at org.jetbrains.exposed.sql.QueriesKt.batchInsert$default(Queries.kt:92)
	at org.jetbrains.exposed.dao.InnerTableLink.setValue(InnerTableLink.kt:64)
	at projectX.A.setBs(A.kt:52)

I read the release notes of 0.27.1:

[SQLServer] Don't use OUTPUT clause in inserts when shouldReturnGeneratedValues=false
[SQLServer] Unnecessary limit for OUTPUT command when using batch insert (#440)

As the code breaks with this version, I assume it is related, but I am not sure and I am also not sure how I can set shouldReturnGeneratedValues=true when I just use the assignment syntax like above (toUpdate.bs = SizedCollection(newBs)) I couldn't find an issue and the documentation https://github.com/JetBrains/Exposed/wiki/DAO#many-to-many-reference is still the same.

Any input how to fix this in a later version would be highly appreciated. I don't want to be stuck with 0.26.2 forever but as it stands, I can't upgrade without doing a git bisect and diving deep into the codebase of a dependency I'd rather not touch myself.

Thanks for any insights and potential solutions you share beforehand!

[Tapac]

I wasn't able to reproduce it with the latest master. Can you please check the latest version and if your code will fail please share a sample project to debug. Thank you.

[Froidoh]

Hello @Tapac

Thanks a lot for your comment.

At the moment I cannot strip down the application to create a minimal reproducible build, but I can tell you the following:

For one of the many to many relations, namely role<->user I get this with version 0.27.1 to 0.33.1:

2021-08-30 14:36:03.450 [eventLoopGroupProxy-4-1 @call-handler#3] DEBUG Exposed - SELECT dbo."role".id, dbo."role"."name" FROM dbo."role" WHERE dbo."role".id IN (643, 719, 869, 959, 960)
2021-08-30 14:36:03.480 [eventLoopGroupProxy-4-1 @call-handler#3] DEBUG Exposed - DELETE FROM dbo.user_role WHERE (dbo.user_role.user_id = 2308) AND (dbo.user_role.role_id NOT IN (643, 719, 960, 869, 959))
2021-08-30 14:36:03.509 [eventLoopGroupProxy-4-1 @call-handler#3] DEBUG Exposed - INSERT INTO dbo.user_role (role_id, user_id)  VALUES(960, 2308)
2021-08-30 14:36:03.509 [eventLoopGroupProxy-4-1 @call-handler#3] WARN  Exposed - Transaction attempt #2 failed: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.. Statement(s): INSERT INTO dbo.user_role (role_id, user_id)  VALUES(?, ?)

And this with version 0.26.1

2021-08-30 14:39:46.118 [eventLoopGroupProxy-4-1 @call-handler#3] DEBUG Exposed - SELECT dbo."role".id,  dbo."role"."name" FROM dbo."role" WHERE dbo."role".id IN (643, 719, 869, 959, 960)
2021-08-30 14:39:46.151 [eventLoopGroupProxy-4-1 @call-handler#3] DEBUG Exposed - DELETE FROM dbo.user_role WHERE (dbo.user_role.user_id = 2308) AND (dbo.user_role.role_id NOT IN (643, 719, 869, 959, 960))
2021-08-30 14:39:46.188 [eventLoopGroupProxy-4-1 @call-handler#3] DEBUG Exposed - INSERT INTO dbo.user_role (role_id, user_id)  OUTPUT inserted.id AS GENERATED_KEYS VALUES(960, 2308)

The important difference is previously: INSERT INTO dbo.user_role (role_id, user_id) OUTPUT inserted.id AS GENERATED_KEYS VALUES(960, 2308)

while with the newer versions there is no OUTPUT

Both versions use "com.microsoft.sqlserver:mssql-jdbc:9.4.0.jre8" as a dependency.

The relevant code:

a simple UserTable, RoleTable, UserRoleTable:

return transaction {
 val user = User.findById(request.id) ?: return@transaction null
 user.roles = Role.forIds(request.roles)
}

I checked but Role.forIds(request.roles) does return a SizedIterable, so I am still not sure what's wrong... I mean, clearly the insert is wrong as it doesn't return anything, but it fails in that line.

My question is: What do I need to change to that the insert generates an output/result set! like in the previous versions!

Before I dive into producing a minimal example: Does that already help?

[Froidoh]

From my point of view the error resides in:

https://github.com/JetBrains/Exposed/blob/master/exposed-dao/src/main/kotlin/org/jetbrains/exposed/dao/InnerTableLink.kt#L69

namely shouldReturnGeneratedValues = false

no idea how I can override this with the simple assignment syntax.

Also note: Just removing roles is no problem at all. I can remove all the roles I want and everything works fine. But as soon as I want to assign a new role:

2021-08-30 21:40:30.587 [eventLoopGroupProxy-4-1 @call-handler#3] DEBUG Exposed - INSERT INTO dbo.user_role (role_id, user_id)  VALUES(719, 2308)
2021-08-30 21:40:30.588 [eventLoopGroupProxy-4-1 @call-handler#3] WARN  Exposed - Transaction attempt #2 failed: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.. Statement(s): INSERT INTO dbo.user_role (role_id, user_id)  VALUES(?, ?)
org.jetbrains.exposed.exceptions.ExposedSQLException: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
	at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:62)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:135)
	at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:121)
	at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:28)
	at org.jetbrains.exposed.sql.QueriesKt.batchInsert(Queries.kt:151)
	at org.jetbrains.exposed.sql.QueriesKt.batchInsert(Queries.kt:93)
	at org.jetbrains.exposed.sql.QueriesKt.batchInsert$default(Queries.kt:88)
	at org.jetbrains.exposed.dao.InnerTableLink$setValue$1.invoke(InnerTableLink.kt:69)
	at org.jetbrains.exposed.dao.InnerTableLink$setValue$1.invoke(InnerTableLink.kt:67)
	at org.jetbrains.exposed.dao.EntityLifecycleInterceptorKt.executeAsPartOfEntityLifecycle(EntityLifecycleInterceptor.kt:17)
	at org.jetbrains.exposed.dao.InnerTableLink.setValue(InnerTableLink.kt:67)
	at database.models.auth.User.setRoles(AuthModels.kt:31)

[Tapac]

I found a problem, the fix will be released soon.