Bug #106193 insert into select,coalesce(sum(),0) not work correct

• Submitted: 18 Jan 2022 6:43
• Modified: 19 Jan 2022 8:10
• Reporter: flyingsand leung
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.27
• OS: Windows
• CPU Architecture: x86

[18 Jan 2022 6:43] flyingsand leung

Description:
t1:
+------+-------+
| Y    | V     |
+------+-------+
| 2021 | 2.000 |
| 2021 | 3.000 |
+------+-------+

INSERT INTO t1(Y,V)
SELECT Y,COALESCE(SUM(V),0) V
FROM t1
WHERE Y=2021
GROUP BY Y;

the new row V is 0 instead of 5.

How to repeat:
CREATE TABLE t1 (
  Y INT,
  V decimal(32,3)
);

insert into t1(Y,V) VALUES(2021,2);
insert into t1(Y,V) VALUES(2021,3);

INSERT INTO t1(Y,V)
SELECT Y,COALESCE(SUM(V),0) V
FROM t1
WHERE Y=2021
GROUP BY Y;

SELECT * FROM t1;

Expected results:
+------+-------+
| Y    | V     |
+------+-------+
| 2021 | 2.000 |
| 2021 | 3.000 |
| 2021 | 5.000 |
+------+-------+
3 rows in set (0.01 sec)

Actual results:
+------+-------+
| Y    | V     |
+------+-------+
| 2021 | 2.000 |
| 2021 | 3.000 |
| 2021 | 0.000 |
+------+-------+
3 rows in set (0.01 sec)

[18 Jan 2022 10:59] MySQL Verification Team

Hello flyingsand leung,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[19 Jan 2022 8:10] Erlend Dahl

Duplicate of

Bug#105351 Data insertion exception in version 8.0.27

(fixed in 8.0.28).