Bug #102252 INSERT,SELECT from same table with aggregate and NOW() columns produces error
Submitted: 15 Jan 5:57 Modified: 15 Jan 8:28
Reporter: Duke Lee Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.22 OS:Ubuntu (20.04.1)
Assigned to: CPU Architecture:x86

[15 Jan 5:57] Duke Lee
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?
[15 Jan 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 8:28] MySQL Verification Team
Hello Duke Lee,

Thank you for the report and test case.

regards,
Umesh