MySQL : Adding a row with a virtual column fails. #2646

Open
mattbryson opened this Issue Jan 4, 2017 · 1 comment

Projects

None yet

2 participants

@mattbryson

I have a MySQL db, and one of the tables has a JSON blob in it, with a virtual column generated from that JSON content.

CREATE TABLE `my_table` (
  `name` varchar(50) NOT NULL,
  `json` json DEFAULT NULL,
  `virtual_column` bigint(20) GENERATED ALWAYS AS (json_unquote(json_extract(`json`,'$.properties.some_value'))) VIRTUAL,
  PRIMARY KEY (`name`),
  UNIQUE KEY `virtual_column` (`virtual_column`)
) ;

When trying to add a new row, the insert fails as it tries to add a value to the generated column, which is not allowed.

The generated SQL statement that i pulled from the console was...

INSERT INTO `my_table` (`name`, `json`, `virtual_column`) 
VALUES ('test', '{\"properties\": {\"some_value\": \"1234\"}}', NULL);

The above has tried to INSERT a value of NULL into virtual_column but it should not insert anything as this column value is generated by mySQL.

The generated SQL should be...

INSERT INTO `my_table` (`name`, `json` ) 
VALUES ('test', '{\"properties\": {\"some_value\": \"1234\"}}');

It should exclude generated columns.

The Table is created like this..

(Great app btw, thanks for all the hard work! )

@dmoagx
Collaborator
dmoagx commented Jan 4, 2017 edited

Yeah, generated columns are not supported by Sequel Pro currently.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment