MySQL Bugs: #118630: INSERT IGNORE failed but insert a different value

Bug #118630	INSERT IGNORE failed but insert a different value
Submitted:	10 Jul 9:05	Modified:	23 Jul 1:36
Reporter:	chi zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	9.3.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

In the following test case, the INSERT statement will fail and will not insert any value, as there is a NOT NULL constraint. However, the first select returns 1 row from t0. Furthermore, the predicate of the second select statement should be false, but it also returns 1 row.

```
CREATE TABLE t0(c0 BIGINT(159) PRIMARY KEY NOT NULL) ;
INSERT IGNORE INTO t0(c0) VALUES(NULL);
SELECT * FROM t0; -- 0
SELECT 0 AS ref0 FROM t0 WHERE (NOT (((NULL) NOT IN (t0.c0)) && ((t0.c0) NOT IN (CAST(0.1 AS DOUBLE))))); -- 0
```

How to repeat:
```
CREATE TABLE t0(c0 BIGINT(159) PRIMARY KEY NOT NULL) ;
INSERT IGNORE INTO t0(c0) VALUES(NULL);
SELECT * FROM t0; -- 0
SELECT 0 AS ref0 FROM t0 WHERE (NOT (((NULL) NOT IN (t0.c0)) && ((t0.c0) NOT IN (CAST(0.1 AS DOUBLE))))); -- 0
```

Hello Chi Zhang,

Thank you for the bug report.
Imho this is not a bug, please see the doc https://dev.mysql.com/doc/refman/9.3/en/sql-mode.html#ignore-effect-on-execution

If the SQL mode is not strict, IGNORE causes the NULL to be inserted as the column implicit default (0 in this case), which enables the row to be handled without skipping it.

Regards,
Ashwini Patil

Hi,

Thanks for your work on analyzing this test case!

For the second query in this test case, the predicate should be false, as there is only `0` in `t0.c0`, so `(NULL) NOT IN (t0.c0)` should be true, `(t0.c0) NOT IN (CAST(0.1 AS DOUBLE))` should be true, and the whole predicate `NOT (((NULL) NOT IN (t0.c0)) && ((t0.c0) NOT IN (CAST(0.1 AS DOUBLE))))` should be false, however, the second query still return 1 row.