Computed Column Does Not Update And Unable To Insert Row

[phdphd]

Hi All,

I have a table to which I added 2 computed columns like this :

ALTER TABLE foo add number2 DECIMAL GENERATED ALWAYS AS (number*2) STORED, add nombre3 DECIMAL GENERATED ALWAYS AS (number*3) STORED

Two issues :

1. Updating a value in "number" column does not updates "number2" and "number3" columns

2. Inserting a new row generates the error "the value specified for generated column is not allowed".

Thanks!

 

 

Edited June 5, 2019 by phdphd

[Barand]

I have just run this sequence of SQL and no issues

CREATE TABLE `foo` (
  `foo_id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`foo_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO foo (number) VALUES (5),(10);

SELECT * FROM test.foo;
+--------+--------+
| foo_id | number |
+--------+--------+
|      1 |      5 |
|      2 |     10 |
+--------+--------+

--
--  add generated columns
--

ALTER TABLE foo add number2 DECIMAL GENERATED ALWAYS AS (number*2) STORED, add nombre3 DECIMAL GENERATED ALWAYS AS (number*3) STORED;

--
-- check new table
--
SELECT * FROM test.foo;
+--------+--------+---------+---------+
| foo_id | number | number2 | nombre3 |
+--------+--------+---------+---------+
|      1 |      5 |      10 |      15 |
|      2 |     10 |      20 |      30 |
+--------+--------+---------+---------+

--
--  insert a couple more records
--

INSERT INTO foo (number) VALUES (15),(20);

--
--  check again
--

SELECT * FROM test.foo;
+--------+--------+---------+---------+
| foo_id | number | number2 | nombre3 |
+--------+--------+---------+---------+
|      1 |      5 |      10 |      15 |
|      2 |     10 |      20 |      30 |
|      3 |     15 |      30 |      45 |
|      4 |     20 |      40 |      60 |
+--------+--------+---------+---------+

Perhaps you need to give us more details on exactly what you are doing

[phdphd]

You are right. My mistake. I am using phpMyAdmin. I should have made the tests through SQL statements instead of inserting a row via the Insert tab or editing a value via the View tab.

[Barand]

I have added a couple more records by manually editing the table (MySql Workbech) and, again, no issues. (Note - generated values only appear when the output is refreshed)