Bug #79204 INSERT INTO table from SELECT accepts to insert into generated columns
Submitted: 10 Nov 2015 12:38 Modified: 1 Dec 2015 18:22
Reporter: Olav Sandstå Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[10 Nov 2015 12:38] Olav Sandstå 
Description:
With the following table definition:

CREATE TABLE t1 (
  i1 INTEGER NOT NULL,
  i2 INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL NOT NULL
);

the following INSERT statement succeeds:

INSERT INTO t1 (i1, i2) SELECT 5, 6;

It should have failed (or at least given a warning?) that it is not allowed to insert a value into a generated column.

How to repeat:
CREATE TABLE t1 (
  i1 INTEGER NOT NULL,
  i2 INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL NOT NULL
);

INSERT INTO t1 (i1, i2) SELECT 5, 6;

SELECT * FROM t1;

DROP TABLE t1;
[10 Nov 2015 12:40] Olav Sandstå 
Posted by developer:
 
The similar INSERT statement using VALUES to specify what to insert:

  INSERT INTO t1 (i1, i2) VALUES (5,6);

returns an error:

  ERROR 3105 (HY000): The value specified for generated column 'i2' in table 't1' is not allowed.
[1 Dec 2015 18:22] Paul DuBois 
Noted in 5.7.11, 5.8.0 changelogs.

INSERT INTO ... SELECT statements could insert values other than
DEFAULT into generated columns.