MySQL Bugs: #113849: Incorrect results when case ... when used in update statement

Bug #113849	Incorrect results when case ... when used in update statement
Submitted:	1 Feb 2024 10:50	Modified:	9 Feb 2024 7:29
Reporter:	yi qian	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	case ... when, UPDATE

Description:
When using a subquery with a case when in the update statement, the updated data does not match the expected results returned by the subquery.

How to repeat:
1、prepare data:
drop database if exists select_test;
create database if not exists select_test;

create table if not exists select_test.t1_hash(
id int primary key,
a_code int,
b_code int,
infom varchar(20) NOT NULL,
date date );
use select_test;
insert into select_test.t1_hash values(1,10,15,'abc',20211025);
insert into select_test.t1_hash values(2,11,20,'bcd',20211025);
insert into select_test.t1_hash values(3,150,15,'abc',20211025);

create table if not exists select_test.t1_range(
id int primary key,
c_code int NOT NULL,
d_code int NOT NULL,
infom varchar(20) NOT NULL,
date date NOT NULL);

insert into select_test.t1_range values(1,10,15,'abc',20211026);
insert into select_test.t1_range values(2,11,20,'abc',20211026);

2、query results：
mysql> select case when count(1)>1 then 1 else 0 end from t1_hash a, t1_range b where a.infom=b.infom group by b.infom;
+----------------------------------------+
| case when count(1)>1 then 1 else 0 end |
+----------------------------------------+
|                                      1 |
+----------------------------------------+

3、Update with the above query：
mysql> update t1_hash a set a.a_code=(select case when count(1)>1 then 1 else 0 end from t1_range b where a.infom=b.infom group by b.infom);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select a_code from t1_hash;
+--------+
| a_code |
+--------+
|      1 |
|   NULL |
|      1 |
+--------+

Expected the value of a_code should be all 1, but one of the result is NULL

Hi Mr. gian,

Thank you for your bug report.

We were able to reproduce the results that you get with 8.0.36.

This is what we get:

+----------------------------------------+
| case when count(1)>1 then 1 else 0 end |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
+--------+
| a_code |
+--------+
|      1 |
|   NULL |
|      1 |
+--------+

We agree with you that this is not correct.

Verified as reported.

This is not a bug.
When the scalar subquery is evaluated for the row from t1_hash where id is 2,
the result is NULL because there are no rows in t1_range that matches
a.infom='bcd';
If the GROUP BY is omitted, thus making the subquery implicitly grouped,
I think the desired result will be returned.