MyBatis partial repeated data leads to result data loss

MyBatis version

3.5.15

Database vendor and version

mysql8

Test case or example project

Steps to reproduce

create table and import data

create table if not exists tb_user
(
    user_name varchar(100) null,
    user_id   int          null,
    password  varchar(100) null
);
create table tb_other
(
    col1 varchar(20) null,
    col2 varchar(20) null,
    col3 int         null
);

INSERT INTO tb_user (user_name, user_id, password) VALUES ('test', 1, '1');

INSERT INTO tb_other (col1, col2, col3) VALUES ('test', 'xxx', 1);
INSERT INTO tb_other (col1, col2, col3) VALUES ('test', 'xxx', 2);

mapper.xml

    <resultMap id="testMap" type="com.cyb.admin.domain.TbUser">
        <id property="userId" column="user_id"/>
        <id property="userName" column="user_name"/>

<!--        <collection property="dictTags" ofType="java.lang.String"></collection>-->
        <collection property="others" ofType="com.cyb.admin.domain.TbOther">
            <result column="col1" property="col1"></result>
            <result column="col2" property="col2"></result>
      </collection>
    </resultMap>

    <select id="selectTest" parameterType="int" resultMap="testMap" resultOrdered="true">
        select t1.user_id, t1.user_name, t2.col1, t2.col2
        from tb_user t1
                 left join tb_other t2 on t1.user_name = t2.col1
    </select>

entity Class

@NoArgsConstructor
@Data
public class TbUser {
    private String userName;
    private Long userId;

    List<TbOther> others;
}

@NoArgsConstructor
@Data
public class TbOther {
    private String col1;
    private String col2;
}

execute sql , result like this

test code

    MybatisTestMapper mapper = sqlSession.getMapper(MybatisTestMapper.class);

    List<TbUser> list = mapper.selectTest();
    System.out.println("list size:"+list.size());
    System.out.println("list.others size:"+list.get(0).getOthers().size());

Expected result

list size:1
list.others size:2

Actual result

list size:1
list.others size:1

Comment From: harawata

Hello @pop1213 ,

That is the expected behavior because, in the <collection />, there is no <id> element and the values of col1 and col2 are the same.

If the value of col3 is unique for each row of tb_other, you can use it as ID.

<collection property="others" ofType="com.cyb.admin.domain.TbOther">
  <id column="col3" />
  <result column="col1" property="col1" />
  <result column="col2" property="col2" />
</collection>

Comment From: pop1213

I understand what you mean. However, in the above scenario, the console log printing shows that there are two pieces of data, but I get no result at all. This may be puzzling.

Comment From: harawata

@pop1213 ,

I am not sure what you mean. If you need further assistance, please create a small repro project like these and share it on your GitHub repository. We may be able to understand what you expect by taking a look at the assertions in the test case.