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.

score 8 · Answer 1 · answered Sep 26 '17 at 17:41

8

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.

answered Sep 26 '17 at 17:41

Brian Leishman

8,155
11
57
93

1

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. – alik Sep 27 '17 at 10:50
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 – Brian Leishman Sep 27 '17 at 13:13
@alik 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 https://bugs.mysql.com/bug.php?id=80293. – kaorukobo Jan 13 '20 at 03:12

score 1 · Answer 2 · answered Feb 05 '16 at 18:30

1

You must declare the columns

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

answered Feb 05 '16 at 18:30

ScaisEdge

131,976
10
91
107

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. – alik Feb 05 '16 at 18:57

score 1 · Answer 3 · answered Feb 05 '16 at 18:30

1

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;

answered Feb 05 '16 at 18:30

Dr. Aaron Dishno

1,859
1
29
24

2

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. – Ryan Vincent Feb 05 '16 at 18:36
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. – alik Feb 05 '16 at 18:57

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

3 Answers3