MyBatis useGeneratedKeys and keyProperty with batch upsert results partial key retrieving

MyBatis version

3.5.13 (w/ mybatis-spring 3.0.2)

Database vendor and version

8.0.mysql_aurora.3.06.0 (w/ innodb_version == 8.0.34)

Steps to reproduce

When you run a batch upsert statement like below, the feature "keyProperty" is only valid for the first element of given parameters and the rest of the elements does not have keyProperty filled. It occurs whether the given elements are all new or not, so if the all inputs create new rows in the table, it still gets the very first element's key only.

<insert id="upsertAll" useGeneratedKeys="true" keyProperty="id" keyColumn ="id">
    INSERT INTO test_table (
        test_name,
        created_by,
        created_at,
        updated_by,
        updated_at
    )
    VALUES
    <foreach collection="testEntities" item="entity" separator=", ">
        ( #{entity.testName}, #{entity.createdBy}, now(), #{entity.updatedBy}, now() )
    </foreach>
    ON DUPLICATE KEY UPDATE
      test_name = VALUES(test_name),
      update_by = VALUES(update_by),
      update_at = VALUES(update_at)
</insert>

testSetMapper.upsertAll(testEntities); // testEntities contains more than 2 elements
System.out.println(testEntities.get(0).getId()); // generatedKey
System.out.println(testEntities.get(1).getId()); // null

However, if you remove ON DUPLICATE KEY UPDATE statement from the original query statement, it works fine as expected

<insert id="upsertAll" useGeneratedKeys="true" keyProperty="id" keyColumn ="id">
    INSERT INTO test_table (
        test_name,
        created_by,
        created_at,
        updated_by,
        updated_at
    )
    VALUES
    <foreach collection="testEntities" item="entity" separator=", ">
        ( #{entity.testName}, #{entity.createdBy}, now(), #{entity.updatedBy}, now() )
    </foreach>
</insert>

testSetMapper.upsertAll(testEntities); // testEntities contains more than 2 elements
System.out.println(testEntities.get(0).getId()); // generatedKey
System.out.println(testEntities.get(1).getId()); // generatedKey

Comment From: harawata

Hello @kiwonseo ,

That is not a bug. Please see this thread. https://github.com/mybatis/mybatis-3/discussions/3206

Comment From: kiwonseo

understood, thank you for your answer