P0-[4.0 bug hunting]-[SQL Plan Management]-INSERT IGNORE allows NULL value in a NOT NULL generated column

[mrigger]

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.

[shuke987]

/bug P1