[#MDEV-24461] Virtual column expression error with binlog

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

[MDEV-24461] Virtual column expression error with binlog_format=ROW Created: 2020-12-21 Updated: 2023-04-27
Status:	Open
Project:	MariaDB Server
Component/s:	Virtual Columns
Affects Version/s:	10.5.6, 10.3, 10.4, 10.5
Fix Version/s:	10.4, 10.5

[MDEV-24461] Virtual column expression error with binlog_format=ROW Created: 2020-12-21 Updated: 2023-04-27