MySQL Bugs: #80293: INSERT ... SELECT * FROM does not work for tables with generated columns

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

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.

Fixed title.

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.

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).

Bug #80384 marked as duplicate of this

Bug #80271 marked as duplicate of this