Bug #105351 | Data insertion exception in version 8.0.27 | ||
---|---|---|---|
Submitted: | 28 Oct 2021 2:10 | Modified: | 19 Nov 2021 19:32 |
Reporter: | tuantuan Xue | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S2 (Serious) |
Version: | 8.0.27 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | regression |
[28 Oct 2021 2:10]
tuantuan Xue
[28 Oct 2021 6:22]
MySQL Verification Team
Hello tuantuan Xue, Thank you for the report and test case. Observed that 8.0.27 build is affected. regards, Umesh
[28 Oct 2021 6:26]
MySQL Verification Team
- 8.0.27 - affected bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> CREATE TABLE test(ID INT,FIELDNAME VARCHAR(100)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO test VALUES(1,'36245'); Query OK, 1 row affected (0.01 sec) mysql> SELECT -> MAX(id)+1 , -> '27141' id2 -> FROM -> test a -> GROUP BY id2 ; +-----------+-------+ | MAX(id)+1 | id2 | +-----------+-------+ | 2 | 27141 | +-----------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test -> SELECT -> MAX(id)+1 , -> '27141' id2 -> FROM -> test a -> GROUP BY id2 ; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test; +------+-----------+ | ID | FIELDNAME | +------+-----------+ | 1 | 36245 | | NULL | 27141 | +------+-----------+ 2 rows in set (0.00 sec) mysql> shutdown; Query OK, 0 rows affected (0.00 sec)
[28 Oct 2021 6:28]
MySQL Verification Team
This seems to be regression, lowest checked version 8.0.11 not affected bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.11 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.05 sec) mysql> use test Database changed mysql> CREATE TABLE test(ID INT,FIELDNAME VARCHAR(100)); Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO test VALUES(1,'36245'); Query OK, 1 row affected (0.09 sec) mysql> SELECT -> MAX(id)+1 , -> '27141' id2 -> FROM -> test a -> GROUP BY id2 ; +-----------+-------+ | MAX(id)+1 | id2 | +-----------+-------+ | 2 | 27141 | +-----------+-------+ 1 row in set (0.00 sec) mysql> INSERT INTO test -> SELECT -> MAX(id)+1 , -> '27141' id2 -> FROM -> test a -> GROUP BY id2 ; Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test; +------+-----------+ | ID | FIELDNAME | +------+-----------+ | 1 | 36245 | | 2 | 27141 | +------+-----------+ 2 rows in set (0.00 sec) mysql>
[28 Oct 2021 6:31]
MySQL Verification Team
- 5.7.36 not affected
[19 Nov 2021 19:32]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL 8.0.28 release, and here's the proposed changelog entry from the documentation team: If a query with the sql_buffer_result system variable enabled returned just one row, and an attempt was made to insert the result into a table, then an error in setting the output from the temporary table could produce a data exception. Thank you for the bug report.
[19 Jan 2022 8:10]
Erlend Dahl
Bug#106193 insert into select,coalesce(sum(),0) not work correct was marked as a duplicate.