Bug #80293 INSERT ... SELECT * FROM does not work for tables with generated columns
Submitted: 5 Feb 2016 19:48 Modified: 7 Feb 2016 23:01
Reporter: Martin Cetkovsk Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[5 Feb 2016 19:48] Martin Cetkovsk
Description:
INSERT INTO ... SELECT * FROM ... does not work with generated columns.

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

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;

Results in 

Error Code: 3105. The value specified for generated column 'generated' in table 'adestination' is not allowed.
[5 Feb 2016 19:57] Martin Cetkovsk
Fixed title.
[7 Feb 2016 0:46] MySQL Verification Team
Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12 Source distribution PULL: 2016-FEB-06

Copyright (c) 2000, 2016, 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 5.7 > USE TEST
Database changed
mysql 5.7 > CREATE TABLE `asource` (
    ->   `id` int(10) unsigned NOT NULL DEFAULT '0',
    ->   `generated` tinyint(1) GENERATED ALWAYS AS (id = 2) STORED NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE `adestination` (
    ->   `id` int(10) unsigned NOT NULL DEFAULT '0',
    ->   `generated` tinyint(1) GENERATED ALWAYS AS (id = 2) STORED NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO adestination
    -> SELECT asource.* FROM asource;
ERROR 3105 (HY000): The value specified for generated column 'generated' in table 'adestination' is not allowed.
[7 Feb 2016 23:01] Roy Lyseng
This is not a bug.

According to the manual http://dev.mysql.com/doc/refman/5.7/en/create-table.html, only DEFAULT may be specified as a source for a generated column:

"For INSERT, REPLACE, and UPDATE, if a generated column is inserted into, replaced, or updated explicitly, the only permitted value is DEFAULT."

This is in compliance with the SQL standard for generated columns.

A workaround for your problem is to specify an INSERT column list naming the base columns only:

INSERT INTO adestination(id)
SELECT id FROM asource;

(DEFAULT is only supported in a SELECT ... VALUES statement).
[16 Feb 2016 11:22] MySQL Verification Team
Bug #80384 marked as duplicate of this
[11 Jul 2016 12:15] MySQL Verification Team
Bug #80271 marked as duplicate of this