MySQL Bug Report for division by zero

division_by_zero.md

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)) STORED
  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

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.