As it says in the title. I believe that there is some kind of a race condition that is leading to an H2 query returning wrong results.
The reason why I think that it is a race condition is because if I debug and try to step into the code being executed everything works expected. However, as soon as I run our test in normal mode the test fails, i.e. the data is returned incorrectly.

Everything works fine in 2.0.206 and it starts behaving incorrectly from 2.1.210. This lead me to believe that there must be some commit that caused this. Therefore, I checked out the H2 codebase and started testing against different commits.

I managed to isolate the problem to the merge commit 0ab388f. To be more precise commit ced08fc.

The query that we are executing is:

select RES.*, UA_MAPPING.USER_ACCOUNT_ID_ as USER_ACCOUNT_ID_
from (select RES.*
      from CONVERSATION RES
      where RES.ID_ in (select UA_MAPPING.CONVERSATION_ID_
                        from CONV_USR_ACCOUNT UA_MAPPING
                        where UA_MAPPING.USER_ACCOUNT_ID_ = ?)
        and RES.STATE_ = ?
      order by RES.LAST_MESSAGE_TIME_ desc) RES
     left join CONV_USR_ACCOUNT UA_MAPPING on RES.ID_ = UA_MAPPING.CONVERSATION_ID_
order by RES.LAST_MESSAGE_TIME_ desc;

The parameters are:

{1: '75e93efa-e721-11ec-94ba-62e065102c67', 2: 'ACTIVE'}

The result that we are getting is:

Columns: ID_, REV_, TENANT_ID_, PARENT_ID_, REFERENCE_ID_, REFERENCE_TYPE_, REFERENCE_DEFINITION_ID_, CONVERSATION_DEFINITION_ID_, STATE_, TYPE_, SUB_TYPE_, PERMISSION_TYPE_, JOINING_TYPE_, PRIORITY_, NAME_, FALLBACK_NAME_, DESCRIPTION_, CREATION_TIME_, CREATOR_ID_, UPDATE_TIME_, UPDATER_ID_, RESUBMISSION_TIME_, LAST_MESSAGE_TIME_, METADATA_, TAGS_, EXTERNAL_ID_, AVATAR_ID_, USER_ACCOUNT_ID_
Row: 75f796db-e721-11ec-94ba-62e065102c67, 1, , null, null, null, null, null, ACTIVE, dm, default, inheritedAndImmutable, fixed, null, Conversation 1, null, null, 2022-06-08 13:52:24.074, null, null, null, null, 2022-06-08 13:52:24.396, <<BLOB>>, <<BLOB>>, null, null, 75e8a2b9-e721-11ec-94ba-62e065102c67

This result is incorrect. See the value of the USER_ACCOUNT_ID_ table. It is coming from a different mapping and not the one that we need.

Unfortunately, I have not been able to extract the problem in a smaller more reproducible example. Do you think that there is some information that I can provide to you in order to be able to help you find a reproducible scenario. Are there certain parameters that you would like me to enable or some logging information that you can use.

In case more context is needed, this is being used in a Spring Boot projected, using a Spring transaction manager, and using MyBatis to execute the query.