P1-[4.0 bug hunting]-INSERT IGNORE allows NULL value in a NOT NULL generated column

[sre-bot]

Bug Hunter issue tidb-challenge-program/bug-hunting-issue#56

---

Consider the following statements:

CREATE TABLE t0(c0 INTEGER AS (NULL) NOT NULL, c1 INT);
CREATE INDEX i0 ON t0(c0, c1);
INSERT IGNORE INTO t0(c1) VALUES (0);
SELECT * FROM t0 WHERE t0.c0 IS NULL; -- expected: {NULL|0}, actual: {}

The INSERT IGNORE inserts a NULL value into t0, even though the column is declared as NOT NULL. This causes an unexpected result in the SELECT since t0.c0 IS NULL evaluates to FALSE, even though t0.c0 is NULL:

Server version: 5.7.25-TiDB- TiDB Server (Apache License 2.0), MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t0(c0 INTEGER AS (NULL) NOT NULL, c1 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE INDEX i0 ON t0(c0, c1);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO t0(c1) VALUES (0);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM t0 WHERE t0.c0 IS NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM t0;
+----+------+
| c0 | c1   |
+----+------+
| NULL |    0 |
+----+------+
1 row in set (0.00 sec)

MySQL 8.0.19 seems to address this by converting NULL to 0, and is thus not affected by this bug:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Database changed
mysql> CREATE TABLE t0(c0 INTEGER AS (NULL) NOT NULL, c1 INT);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE INDEX i0 ON t0(c0, c1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT IGNORE INTO t0(c1) VALUES (0);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM t0;
+----+------+
| c0 | c1   |
+----+------+
|  0 |    0 |
+----+------+
1 row in set (0.00 sec)

I found this based on the latest master commit 187f225002b67daa47992816f6ef5ddb76b0f68a, and also checked that this reproduces on the 4.0 RC.

[sre-bot]

Integrity check:
RCA symptom trigger_condition affect_version fix_version fields are empty

Please comment /info to get template

[ti-srebot]

Please edit this comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added

1. Root Cause Analysis (RCA)

The generated column doesn't handle bad null value.

2. Symptom

Wrong result.

3. All Trigger Conditions

generated column, INSERT IGNORE

4. Workaround (optional)

5. Affected versions

[v4.0.0:v4.0.7]

6. Fixed versions

v4.0.8