Having lower and upper case column names looks really bad but it seems to be the correct thing.

Looks good, please proceed. Thanks!

Will this change the collation used for all MS SQL testing in Keycloak? If yes, I am not sure this is a good idea. Basically all default collations in MS SQL are case-insensitive (see https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support) and I would assume most people go with the default. By changing to case-sensitive, we would test a setup most people are not using.

Will this change the collation used for all MS SQL testing in Keycloak? If yes, I am not sure this is a good idea. Basically all default collations in MS SQL are case-insensitive (see https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support) and I would assume most people go with the default. By changing to case-sensitive, we would test a setup most people are not using.

@sschu I would love all the input I can have.

How would we detect errors like this one without running the test with case-sensitive collation?

I'm not a DB expert (please read, never used DB in my life), but if the test passes with case-sensitive implies the case-insensitive will also work?

I don't wanna double our testsuite size by running it twice 🤞

@pruivo You are absolutely right, you could not detect this bug without it. I am just concerned that if we change collation here, we don't detect other bugs with case-insensitive collations somewhere else. Would it be possible to make this configurable and specify collation per test?

I duplicated the "Migration Tests" to run MSSQL with case-sensitive collation.
I tried on my fork, without this fix, and it detects the problem: https://github.com/pruivo/keycloak/actions/runs/25452832988/job/74676496961

MigrationTest ++ Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'user_session_id'.
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:278)
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1788)
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:688)
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:607)
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7825)
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4828)
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:321)
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:253)
MigrationTest ++ 	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:584)
MigrationTest ++ 	at io.agroal.pool.wrapper.PreparedStatementWrapper.execute(PreparedStatementWrapper.java:301)
MigrationTest ++ 	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:168)
MigrationTest ++ 	... 56 more

Do you think it is good enough?
cc @ahus1 @sschu