INSERT INTO ... SELECT if destination column has a generated column

Question

Have some tables:

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

I copy a row from asource to adestination:

INSERT INTO adestination 
SELECT asource.* 
FROM asource;

The above generates an error:

Error Code: 1136. Column count doesn't match value count at row 1

Ok, quite strange to require me to mention generated query. But ok, I add that column to the query:

INSERT INTO adestination 
SELECT asource.*, NULL AS `generated`
FROM asource;

This has worked fine in 5.7.10. However, it generates an error in 5.7.11 (due to a fix:

Error Code: 3105. The value specified for generated column 'generated' in table 'adestination' is not allowed.

Ok, next try:

INSERT INTO adestination 
SELECT asource.*, 1 AS `generated`
FROM asource;

But still the same error. I have tried 0, TRUE, FALSE but the error persists.

The DEFAULT value which is stated as the only allowed value (specs or docs). However, the following generates a syntax error (DEFAULT is not supported there):

INSERT INTO adestination 
SELECT asource.*, DEFAULT AS `generated`
FROM asource;

So, how can I copy a row from one table to another using INSERT INTO ... SELECT if the destination table adds some columns where some of them are GENERATED?

The code calling this query is generic and has no knowledge what columns that particular tables have. It just knows which extra columns the destination table has. The source table is a live table, the destination table is a historical version of the source table. It has few columns extra like user id made the change, what type of the change it is (insert, update, delete) when etc.

Answer 1

Sadly this is just how MySQL works now to "conform to SQL standards".

The only value that the generated column can accept in an update, insert, etc. is DEFAULT, or the other option is to omit the column altogether.

My poor mans work around for these are to just disable the generated column while I'm working with the data (like for importing a dump) and then go back and add the generated column expression afterwards.

Answer 2

You must declare the columns

Insert into adestination (id, generated) 
select id, 1 
from asource;