Description:
MySQL 5.7 doesn't support division by 0 in write operations. However in virtual columns they are allowed, but if you try to update a row that previously had division by zero to another value so not to cause the division by zero you get an error and the row gets immutable.
How to repeat:
MySQL 5.7 doesn't support division by 0 in write operations. However in virtual columns they are allowed, but if you try to update a row that previously had division by zero to another value so not to cause the division by zero you get an error and the row gets immutable.
Create a table `trips` as the following:
CREATE TABLE trips (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
distance INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
And insert this data:
INSERT INTO trips (start_time, end_time, distance) VALUES ('2017-12-09 10:16:02', '2017-12-09 10:16:02', 2);
INSERT INTO trips (start_time, end_time, distance) VALUES ('2017-12-09 10:17:52', '2017-12-09 10:56:53', 56);
INSERT INTO trips (start_time, end_time, distance) VALUES ('2017-12-09 10:18:47', '2017-12-09 10:37:59', 24);
Once you look into the content you will see this:
+----+---------------------+---------------------+----------+
| id | start_time | end_time | distance |
+----+---------------------+---------------------+----------+
| 1 | 2017-12-09 10:16:02 | 2017-12-09 10:16:02 | 2 |
| 2 | 2017-12-09 10:17:52 | 2017-12-09 10:56:53 | 56 |
| 3 | 2017-12-09 10:18:47 | 2017-12-09 10:37:59 | 24 |
+----+---------------------+---------------------+----------+
3 rows in set (0.00 sec)
Add a *virtual* field to get the `time_travelled` and one to get the speed in meters/second (`speed_m_s`)
ALTER TABLE trips
ADD COLUMN time_travelled INT(10) GENERATED ALWAYS AS (TIMESTAMPDIFF(SECOND, start_time, end_time))
AFTER distance;
ALTER TABLE trips
ADD COLUMN speed_m_s FLOAT GENERATED ALWAYS AS ((distance * 1000) / trips.time_travelled)
AFTER time_travelled;
After running:
SELECT * FROM trips;
+----+---------------------+---------------------+----------+----------------+-----------+
| id | start_time | end_time | distance | time_travelled | speed_m_s |
+----+---------------------+---------------------+----------+----------------+-----------+
| 1 | 2017-12-09 10:16:02 | 2017-12-09 10:16:02 | 2 | 0 | NULL |
| 2 | 2017-12-09 10:17:52 | 2017-12-09 10:56:53 | 56 | 2341 | 23.9214 |
| 3 | 2017-12-09 10:18:47 | 2017-12-09 10:37:59 | 24 | 1152 | 20.8333 |
+----+---------------------+---------------------+----------+----------------+-----------+
3 rows in set, 1 warning (0.00 sec)
First row shows NULL, because it's caused by a division by zero, which is an expected behavior in this case.
The problem is when you try to update the `end_time` or `distance` for instance to any other value:
UPDATE trips SET end_time = '2017-12-09 10:17:02' WHERE id = 1;
Instead of doing the change on `end_time`, recalculating `time_travelled` to be 60 and then calculate the `speed_m_s`, the server makes this row immutable and unchangeable unless you drop the columns, do the change, and add again. But right now, the default behavior is to respond with:
mysql> UPDATE trips
-> SET end_time = '2017-12-09 10:17:02'
-> WHERE id = 1;
ERROR 1365 (22012): Division by 0
Which I would argue that is not division by zero anymore since `time_travelled` should be responding for the new value, not the old one.
It happens on 5.7 and 8.0.3
I will keep this gist updated with more information about the bug: https://gist.github.com/gabidavila/d9344b81755b58f72e3b0c3f8d087c97
Suggested fix:
Maybe do not allow generated columns to display even if it has a division by zero?
Because right now the only thing that is possible is if I drop both generated columns, fix the data, and create again.
Granted that for a small table like mine it doesn't make much difference, but imagine if this was a much bigger table, and having `time_travelled` as STORED makes it a copy operation to ALTER TABLE on the ADD COLUMN or the DROP COLUMN.
My Solution is to make the behavior consistent and have someway of during the update know that won't be causing a division by zero with the new value.
If this is in somehow duplicated or the expected behavior, just let me know.
Description: MySQL 5.7 doesn't support division by 0 in write operations. However in virtual columns they are allowed, but if you try to update a row that previously had division by zero to another value so not to cause the division by zero you get an error and the row gets immutable. How to repeat: MySQL 5.7 doesn't support division by 0 in write operations. However in virtual columns they are allowed, but if you try to update a row that previously had division by zero to another value so not to cause the division by zero you get an error and the row gets immutable. Create a table `trips` as the following: CREATE TABLE trips ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, start_time DATETIME NOT NULL, end_time DATETIME NOT NULL, distance INT(10) NOT NULL DEFAULT 0, PRIMARY KEY (id) ); And insert this data: INSERT INTO trips (start_time, end_time, distance) VALUES ('2017-12-09 10:16:02', '2017-12-09 10:16:02', 2); INSERT INTO trips (start_time, end_time, distance) VALUES ('2017-12-09 10:17:52', '2017-12-09 10:56:53', 56); INSERT INTO trips (start_time, end_time, distance) VALUES ('2017-12-09 10:18:47', '2017-12-09 10:37:59', 24); Once you look into the content you will see this: +----+---------------------+---------------------+----------+ | id | start_time | end_time | distance | +----+---------------------+---------------------+----------+ | 1 | 2017-12-09 10:16:02 | 2017-12-09 10:16:02 | 2 | | 2 | 2017-12-09 10:17:52 | 2017-12-09 10:56:53 | 56 | | 3 | 2017-12-09 10:18:47 | 2017-12-09 10:37:59 | 24 | +----+---------------------+---------------------+----------+ 3 rows in set (0.00 sec) Add a *virtual* field to get the `time_travelled` and one to get the speed in meters/second (`speed_m_s`) ALTER TABLE trips ADD COLUMN time_travelled INT(10) GENERATED ALWAYS AS (TIMESTAMPDIFF(SECOND, start_time, end_time)) AFTER distance; ALTER TABLE trips ADD COLUMN speed_m_s FLOAT GENERATED ALWAYS AS ((distance * 1000) / trips.time_travelled) AFTER time_travelled; After running: SELECT * FROM trips; +----+---------------------+---------------------+----------+----------------+-----------+ | id | start_time | end_time | distance | time_travelled | speed_m_s | +----+---------------------+---------------------+----------+----------------+-----------+ | 1 | 2017-12-09 10:16:02 | 2017-12-09 10:16:02 | 2 | 0 | NULL | | 2 | 2017-12-09 10:17:52 | 2017-12-09 10:56:53 | 56 | 2341 | 23.9214 | | 3 | 2017-12-09 10:18:47 | 2017-12-09 10:37:59 | 24 | 1152 | 20.8333 | +----+---------------------+---------------------+----------+----------------+-----------+ 3 rows in set, 1 warning (0.00 sec) First row shows NULL, because it's caused by a division by zero, which is an expected behavior in this case. The problem is when you try to update the `end_time` or `distance` for instance to any other value: UPDATE trips SET end_time = '2017-12-09 10:17:02' WHERE id = 1; Instead of doing the change on `end_time`, recalculating `time_travelled` to be 60 and then calculate the `speed_m_s`, the server makes this row immutable and unchangeable unless you drop the columns, do the change, and add again. But right now, the default behavior is to respond with: mysql> UPDATE trips -> SET end_time = '2017-12-09 10:17:02' -> WHERE id = 1; ERROR 1365 (22012): Division by 0 Which I would argue that is not division by zero anymore since `time_travelled` should be responding for the new value, not the old one. It happens on 5.7 and 8.0.3 I will keep this gist updated with more information about the bug: https://gist.github.com/gabidavila/d9344b81755b58f72e3b0c3f8d087c97 Suggested fix: Maybe do not allow generated columns to display even if it has a division by zero? Because right now the only thing that is possible is if I drop both generated columns, fix the data, and create again. Granted that for a small table like mine it doesn't make much difference, but imagine if this was a much bigger table, and having `time_travelled` as STORED makes it a copy operation to ALTER TABLE on the ADD COLUMN or the DROP COLUMN. My Solution is to make the behavior consistent and have someway of during the update know that won't be causing a division by zero with the new value. If this is in somehow duplicated or the expected behavior, just let me know.