Bug #80271 Generated columns are required in INSERT INTO ... SELECT * FROM ... commands
Submitted: 4 Feb 2016 20:18 Modified: 11 Jul 2016 12:15
Reporter: Martin Cetkovsk Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.7.11 OS:Windows (10, 2012 R2)
Assigned to: CPU Architecture:Any

[4 Feb 2016 20:18] Martin Cetkovsk 
Description:
MySQL requires supplying a (dummy) value for generated columns in INSERT INTO ... SELECT ... FROM command.

How to repeat:
CREATE TABLE `asource` (
  `id` int(10) unsigned NOT NULL DEFAULT '0'
);

CREATE TABLE `adestination` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `generated` tinyint(1) GENERATED ALWAYS AS (id = 2) STORED NOT NULL
);

INSERT INTO adestination 
SELECT asource.* FROM asource;

Suggested fix:
As generated columns are generated and could not be manually assigned a value, they should not be required to mention in INSERT INTO ... SELECT ... FROM command (but might be as if the asource has the generated column, it will be included and its value should be silently skipped).
[5 Feb 2016 19:51] Martin Cetkovsk 
Raised Severity as #79204 in 5.7.11 made the thing worse (#80293).

A workaround to provide some value to that generated column no longer works. The DEFAULT is not syntactically supported for INSERT INTO ... SELECT * FROM ... statement.
[5 Feb 2016 20:14] Martin Cetkovsk 
Fixed title.
[11 Jul 2016 12:15] MySQL Verification Team 
Thank you for the report.
Marking this as duplicate of Bug #80293.