Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.6, 10.3, 10.4, 10.5
-
None
-
Linux Centos 7.9
Description
When a virtual column expression returns an error based on the value, this seems to be checked at different times. When binlog_format is ROW and the binlog is on it looks like it is checked at INSERT time, but when the binlog is off or binlog_format is not ROW, then it isn't checked until SELECT, and then you get a warning instead of an error.
This is best illustrated with an example:
First:
MariaDB> show variables like 'log_bin'; |
+---------------+-------+ |
| Variable_name | Value |
|
+---------------+-------+ |
| log_bin | OFF | |
+---------------+-------+ |
1 row in set (0.002 sec) |
|
MariaDB> show variables like 'binlog_format'; |
+---------------+-------+ |
| Variable_name | Value |
|
+---------------+-------+ |
| binlog_format | MIXED |
|
+---------------+-------+ |
1 row in set (0.002 sec) |
|
MariaDB> CREATE OR REPLACE TABLE bug(c1 INT, c2 int as (c1 / 0) virtual); |
Query OK, 0 rows affected (0.036 sec) |
|
MariaDB> insert into bug(c1) values(1); |
Query OK, 1 row affected (0.005 sec)
|
|
MariaDB> select * from bug; |
+------+------+ |
| c1 | c2 |
|
+------+------+ |
| 1 | NULL | |
+------+------+ |
1 row in set, 1 warning (0.001 sec) |
|
MariaDB> show warnings;
|
+---------+------+---------------+ |
| Level | Code | Message | |
+---------+------+---------------+ |
| Warning | 1365 | Division by 0 | |
+---------+------+---------------+ |
1 row in set (0.000 sec) |
Then we try with binlog on. Same result:
MariaDB> show variables like 'log_bin'; |
+---------------+-------+
|
| Variable_name | Value |
|
+---------------+-------+
|
| log_bin | ON |
|
+---------------+-------+
|
1 row in set (0.002 sec) |
|
MariaDB> show variables like 'binlog_format'; |
+---------------+-------+
|
| Variable_name | Value |
|
+---------------+-------+
|
| binlog_format | MIXED |
|
+---------------+-------+
|
1 row in set (0.002 sec) |
|
MariaDB> CREATE OR REPLACE TABLE bug(c1 INT, c2 int as (c1 / 0) virtual); |
Query OK, 0 rows affected (0.041 sec) |
|
MariaDB> insert into bug(c1) values(1); |
Query OK, 1 row affected (0.005 sec) |
|
MariaDB> select * from bug;
|
+------+------+
|
| c1 | c2 |
|
+------+------+
|
| 1 | NULL | |
+------+------+
|
1 row in set, 1 warning (0.001 sec) |
|
MariaDB> show warnings;
|
+---------+------+---------------+
|
| Level | Code | Message |
|
+---------+------+---------------+
|
| Warning | 1365 | Division by 0 | |
+---------+------+---------------+
|
1 row in set (0.000 sec) |
Then we set binlog_format to ROW:
MariaDB> show variables like 'log_bin'; |
+---------------+-------+
|
| Variable_name | Value |
|
+---------------+-------+
|
| log_bin | ON |
|
+---------------+-------+
|
1 row in set (0.002 sec) |
|
MariaDB> show variables like 'binlog_format'; |
+---------------+-------+
|
| Variable_name | Value |
|
+---------------+-------+
|
| binlog_format | ROW |
|
+---------------+-------+
|
1 row in set (0.002 sec) |
|
MariaDB> CREATE OR REPLACE TABLE bug(c1 INT, c2 int as (c1 / 0) virtual); |
Query OK, 0 rows affected (0.037 sec) |
|
MariaDB> insert into bug(c1) values(1); |
ERROR 1365 (22012): Division by 0 |