Bug #112044 Incorrect ROW_COUNT() result with query_cache_type=1

• Submitted: 11 Aug 2023 12:57
• Modified: 11 Aug 2023 13:24
• Reporter: Wen He (OCA)
• Status: Unsupported
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any
• Tags: query cache, SELECT, statistics

[11 Aug 2023 12:57] Wen He

Description:
The result of ROW_COUNT() might be wrong for a select statement when query cache is enabled.
In my following given example, the ROW_COUNT() is expected to return 0 but returns 1 actually.

How to repeat:
-- Start a mysqld with query_cache_type=1
drop table if exists t1, t2;
create table t1 (c1 int, c2 int);
create table t2 (c1 int, c2 int);
insert into t2 values (1, 1), (2, 2);

select * from t2 where c1 > 1;
insert into t1 values(1, 1);
select * from t2 where c1 > 1;
select ROW_COUNT();

[11 Aug 2023 13:24] MySQL Verification Team

Hi Mr. He,

Thank you for your bug report.

However, query cache is deprecated since 5.7.20 and is not maintained any more.

Version 5.7 will be maintained, for non-deprecated and high severity bugs until October 1st, this year.

Hence, this is no more a supported feature.

Unsupported.