Jump to content
phdphd

Computed Column Does Not Update And Unable To Insert Row

Recommended Posts

Posted (edited)

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 by phdphd

Share this post


Link to post
Share on other sites

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

  • Thanks 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

image.png.c53668cd64a62fc3aaf89cfe06018a0f.png

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.