MyBatis <association> with Nested <collection> Returns Unexpected Results

MyBatis version

3.4.6

Database vendor and version

mysql-connector-java:8.0.19

Test case or example project

https://github.com/UserProgrammer/mybatis-discriminator-example/tree/resultmap-bug-hunt

The relevant result-map xml configuration can be found in src/main/resources/com/example/dao/CarDao.xml

The unit test is located at src/test/java/com/example/dao/ICarDaoTest.java

database sql scripts are located at sql-scripts/

Steps to reproduce

1. Clone repo (resultmap-bug-hunt branch)
2. In the terminal, type script/build in the root project directory. (It'll take a few moments for the database docker container to be ready, you'll see a repeated message from mysqladmin. This is expected)

Expected result

[
   {
      "car":{
         "id":1,
         "vin":"13icf043",
         "year":"2019",
         "make":"Subaru",
         "model":"Legacy",
         "color":"black",
         "vehicleType":"car",
         "serviceRecordIds":[
            1, 2, 3
         ],
         "doorCount":4
      },
      "user":{
         "id":1,
         "firstName":"Michael",
         "lastName":"da Costa"
      }
   }, {
      "car":{
         "id":2,
         "vin":"4tgu4985",
         "year":"2014",
         "make":"Ford",
         "model":"Escape",
         "color":"red",
         "vehicleType":"car",
         "serviceRecordIds":[

         ],
         "doorCount":5
      },
      "user":{
         "id":2,
         "firstName":"John",
         "lastName":"Doe"
      }
   }
]

Actual result

Note that there are separate results for each serviceRecordId (for car with id = 1), instead of having a single result where all the serviceRecordIds are collected in an array, as is expected:

[
   {
      "car":{
         "id":1,
         "vin":"13icf043",
         "year":"2019",
         "make":"Subaru",
         "model":"Legacy",
         "color":"black",
         "vehicleType":"car",
         "serviceRecordIds":[
            1
         ],
         "doorCount":4
      },
      "user":{
         "id":1,
         "firstName":"Michael",
         "lastName":"da Costa"
      }
   },
   {
      "car":{
         "id":1,
         "vin":"13icf043",
         "year":"2019",
         "make":"Subaru",
         "model":"Legacy",
         "color":"black",
         "vehicleType":"car",
         "serviceRecordIds":[
            2
         ],
         "doorCount":4
      },
      "user":{
         "id":1,
         "firstName":"Michael",
         "lastName":"da Costa"
      }
   },
   {
      "car":{
         "id":1,
         "vin":"13icf043",
         "year":"2019",
         "make":"Subaru",
         "model":"Legacy",
         "color":"black",
         "vehicleType":"car",
         "serviceRecordIds":[
            3
         ],
         "doorCount":4
      },
      "user":{
         "id":1,
         "firstName":"Michael",
         "lastName":"da Costa"
      }
   },
   {
      "car":{
         "id":2,
         "vin":"4tgu4985",
         "year":"2014",
         "make":"Ford",
         "model":"Escape",
         "color":"red",
         "vehicleType":"car",
         "serviceRecordIds":[

         ],
         "doorCount":5
      },
      "user":{
         "id":2,
         "firstName":"John",
         "lastName":"Doe"
      }
   }
]

Comment From: harawata

Hello @UserProgrammer , The linked repo is not found. Could you check please?

Comment From: UserProgrammer

@harawata My bad. I had set it up as a private repo. It should be available now.

Comment From: harawata

Thank you for the update, @UserProgrammer , I could clone the repo!

It seems like a simple misconfiguration issue. The query returns 4 rows:

vehicle_id	vehicle_vin	vehicle_year	vehicle_make	vehicle_model	vehicle_color	vehicle_type	car_door_count	id	first_name	last_name	service_record_id
1	13icf043	2019	Subaru	Legacy	black	car	4	1	Michael	da Costa	1
1	13icf043	2019	Subaru	Legacy	black	car	4	1	Michael	da Costa	2
1	13icf043	2019	Subaru	Legacy	black	car	4	1	Michael	da Costa	3
2	4tgu4985	2014	Ford	Escape	red	car	5	2	John	Doe	null

MyBatis needs to know which column(s) to use to group these rows, so you need to declare <id /> in the result map.

<resultMap id="carOwnershipResultMap" type="com.example.entity.CarOwnership">
  <id column="vehicle_id" /><!-- added -->
  <association property="car" resultMap="carResultMap" />
  <association property="user" resultMap="userResultMap" />
</resultMap>

Note that property attribute is omitted because there is no corresponding property in CarOwnership.

With the above result map, the test passed on my environment. Please try it and let us know the result.

Comment From: UserProgrammer

That worked! I thought the <resultMap="carOwnershipResultMap"> picked up on the <id .../> tag in <resultMap id="vehicleResultMap">. Thank you for taking the time to look into it!