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.

Linzy

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.

Renick

Thanks for (sad) confirmation. Regarding your disabling the generated columns. I am not a fun of adding a schema change permission to the "live" user, not counting it might become slow to do that. – Linzy

That's an interesting point, I didn't think of that, but yes it is quite annoying. Every time I try to use other tools like HeidiSQL, or the Percona Online Schema Alter tool, they don't work when it comes to inserts, since practically nothing I see goes out of its way to handle virtual columns – Renick

@Linzy As the answer to how can I copy question, if you don't hestiate enumerating all columns in the copying SQL sentence, see Roy Lyseng's workaround at bugs.mysql.com/bug.php?id=80293. – Edie

Answer 2

You must declare the columns

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

Powwow

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. – Linzy

Answer 3

It is best practice to list out the columns, and use null as field1 for the auto incremented id field.

INSERT INTO adestination
(id,
 field1,
 field2) 
SELECT 
     null AS generated, 
     asource.field1, 
     asource.field2
FROM asource;

Stavros

imo, Never mention the autoincrement column when inserting. You can only mess them up sometimes if they are in the query. If you never mention them in the insert query then they are certain to be maintained correctly by the database engine. – Agglomerate

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. – Linzy