Bug #112124 Sometimes , ROW_COUNT() return wrong answer, which cause by index.

• Submitted: 21 Aug 2023 15:10
• Modified: 22 Aug 2023 12:26
• Reporter: linkang zhang (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: DML, replace into, row count

[21 Aug 2023 15:10] linkang zhang

Description:
When we use unique index with DML of replace into , we will get wrong row_count().

How to repeat:
I can contribute one test case for you.

CREATE TABLE `t1` (`c1` int, `c2` int, UNIQUE KEY `c1` (`c1`)) ENGINE=InnoDB;

CREATE TABLE `t2` (`c1` int, `c2` int, UNIQUE KEY `c1` (`c1`),UNIQUE KEY `c2` 
 (`c2`)) ENGINE=InnoDB;

REPLACE INTO t1 VALUES (1,1); 
REPLACE INTO t1 VALUES (1,1);
SELECT ROW_COUNT();

REPLACE INTO t2 VALUES (1,1);
REPLACE INTO t2 VALUES (1,1);
SELECT ROW_COUNT();

# In table t1, the ROW_COUNT is 1, whith is a wrong.
# In table t2, the ROW_COUNT is 2, and it is right according to the document of MySQL.

[22 Aug 2023 12:26] MySQL Verification Team

Hi Mr. zhang,

Thank you for your bug report.

We managed to repeat your test case:

ROW_COUNT()
1
ROW_COUNT()
1
ROW_COUNT()
1
ROW_COUNT()
2

We printed row count after each DML.

There is , however , an explanation why the row count is 2 and not 1. Namely, the ROW_COUNT() value is similar to the value from the mysql_affected_rows().

Hence, this is a bug , but a very low priority one.