| Bug #102252 | INSERT,SELECT from same table with aggregate and NOW() columns produces error | ||
|---|---|---|---|
| Submitted: | 15 Jan 2021 5:57 | Modified: | 5 Nov 2021 14:18 |
| Reporter: | Duke Lee | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.22 | OS: | Ubuntu (20.04.1) |
| Assigned to: | CPU Architecture: | x86 | |
[15 Jan 2021 6:05]
Duke Lee
Oh, I forgot to mention that I found another condition that allows the INSERT statement to succeed:
###
CREATE TABLE note_test (
external_id INT NOT NULL PRIMARY KEY,
note_order INT NOT NULL,
last_update DATETIME NOT NULL
);
INSERT INTO note_test (external_id, note_order, last_update)
SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW()
FROM note_test
WHERE external_id = 123
;
-- The above succeeds. If the referenced column in the WHERE clause (external_id) is either a PRIMARY KEY or has a UNIQUE constraint, the insertion will succeed when the table is empty.
[15 Jan 2021 8:28]
MySQL Verification Team
Hello Duke Lee, Thank you for the report and test case. regards, Umesh
[15 Jan 2021 8:29]
MySQL Verification Team
- 8.0.22
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.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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.02 sec)
mysql> use test
Database changed
mysql> CREATE TABLE note_test (
-> external_id INT NOT NULL, -- Hypothetical FK to some parent table
-> note_order INT NOT NULL,
-> last_update DATETIME NOT NULL
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> -- Table is initially empty.
mysql>
mysql> -- Running the following SELECT...
mysql> SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW()
-> FROM note_test
-> WHERE external_id = 123
-> ;
+-----+----------------------------------+---------------------+
| 123 | COALESCE(MAX(note_order), 0) + 1 | NOW() |
+-----+----------------------------------+---------------------+
| 123 | 1 | 2021-01-15 08:36:10 |
+-----+----------------------------------+---------------------+
1 row in set (0.01 sec)
mysql> -- However, once you include the SELECT into an INSERT ... SELECT ... that references the same table...
mysql> INSERT INTO note_test (external_id, note_order, last_update)
-> SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW()
-> FROM note_test
-> WHERE external_id = 123
-> ;
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_update' at row 1
mysql>
mysql> show errors;
+-------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------+
| Error | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_update' at row 1 |
+-------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------+
| Error | 1292 | Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_update' at row 1 |
+-------+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[15 Jan 2021 8:30]
MySQL Verification Team
- 5.7.32 - no issues
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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.02 sec)
mysql> use test
Database changed
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE note_test (
-> external_id INT NOT NULL, -- Hypothetical FK to some parent table
-> note_order INT NOT NULL,
-> last_update DATETIME NOT NULL
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> -- Table is initially empty.
mysql>
mysql> -- Running the following SELECT...
mysql> SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW()
-> FROM note_test
-> WHERE external_id = 123
-> ;
+-----+----------------------------------+---------------------+
| 123 | COALESCE(MAX(note_order), 0) + 1 | NOW() |
+-----+----------------------------------+---------------------+
| 123 | 1 | 2021-01-15 08:39:41 |
+-----+----------------------------------+---------------------+
1 row in set (0.01 sec)
mysql> -- However, once you include the SELECT into an INSERT ... SELECT ... that references the same table...
mysql> INSERT INTO note_test (external_id, note_order, last_update)
-> SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW()
-> FROM note_test
-> WHERE external_id = 123
-> ;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from note_test;
+-------------+------------+---------------------+
| external_id | note_order | last_update |
+-------------+------------+---------------------+
| 123 | 1 | 2021-01-15 08:39:50 |
+-------------+------------+---------------------+
1 row in set (0.00 sec)
mysql>
[5 Nov 2021 13:47]
Jon Stephens
Regression of WL#9384.
Description: The conditions to produce the error are a bit odd. An error is produced when selecting from and inserting into the same table via INSERT ... SELECT ... where the following conditions are met for the SELECT statement: 1. Including a coalesce on an aggregated column 2. Including a NOW() column 3. The SELECT statement's result set is initially empty (without the coalesce) The following error is thrown: Data truncation: Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_update' at row 1 I can confirm that the error does NOT occur in MySQL 5.7.32 but DOES occur in MySQL 8.0.22 How to repeat: CREATE TABLE note_test ( external_id INT NOT NULL, -- Hypothetical FK to some parent table note_order INT NOT NULL, last_update DATETIME NOT NULL ); -- Table is initially empty. -- Running the following SELECT... SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW() FROM note_test WHERE external_id = 123 ; -- ...produces the result: +-----+----------------------------------+---------------------+ | 123 | COALESCE(MAX(note_order), 0) + 1 | NOW() | +-----+----------------------------------+---------------------+ | 123 | 1 | 2021-01-14 20:48:12 | +-----+----------------------------------+---------------------+ 1 row in set (0.00 sec) -- However, once you include the SELECT into an INSERT ... SELECT ... that references the same table... INSERT INTO note_test (external_id, note_order, last_update) SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW() FROM note_test WHERE external_id = 123 ; -- ...mysql produces an error: ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_update' at row 1 -- If you wrap the SELECT into subquery... INSERT INTO note_test (external_id, note_order, last_update) SELECT * FROM ( SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW() FROM note_test WHERE external_id = 123 ) bugfix ; -- ...this will succeed, for some reason..? -- The following will also succeed: TRUNCATE note_test; INSERT INTO note_test (external_id, note_order, last_update) SELECT 123, COALESCE(MAX(note_order), 0) + 1, NOW() FROM note_test WHERE external_id IS NULL ; -- Note the change of the WHERE clause from "external_id = 123" to "external_id IS NULL". Both queries return the same result set when the table is initially empty but behaves different when being inserted. Suggested fix: I couldn't seem to find any existing notes on this. When run by itself, the SELECT statement produces a single row that one would think should be inserted appropriately. The MySQL doc regarding INSERT SELECT states the following: "When selecting from and inserting into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT and then inserts those rows into the target table." (from https://dev.mysql.com/doc/refman/8.0/en/insert-select.html) It's almost as if the insertion is occurring prematurely, before the result set is stuffed into the temporary table to then be inserted. Perhaps using the subquery forces mysql to create the temporary table?