MySQL Bugs: #94857: execute statement doesn't return correct result

Bug #94857	execute statement doesn't return correct result
Submitted:	1 Apr 2019 20:37	Modified:	13 Jun 2024 17:40
Reporter:	Weidong Yu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.7.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	execute statement

Description:
create an empty table t; 
run "select NULL in (select 1 from t)", it return 0
insert one row into t, re-run above statement, it returns NULL.

If after create empty table t;
prepare a statement from "select NULL in (select 1 from t)", and execute it, it returns 0;

insert into one row into t, re-run prepared statement, it still returns 0

How to repeat:
create table t (a int);
prepare st1 from "select NULL in (select 1 from t)";
execute st1;
insert into t values (1);
execute st1;

Compare with

create table t (a int);
select NULL in (select 1 from t);
insert into t values (1);
select NULL in (select 1 from t);

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in

> To comply with the SQL standard, IN returns NULL not only if the 
> expression on the left hand side is NULL, but also if no match is
> found in the list and one of the expressions in the list is NULL. 

I think we can safely say this bug is verified as return is not consistent with manual nor with itself :(. Thanks for the report

Bogdan

---

mysql [localhost:5725] {msandbox} (test) > create table t (a int);
Query OK, 0 rows affected (0.09 sec)

mysql [localhost:5725] {msandbox} (test) > prepare st1 from "select NULL in (select 1 from t)";
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql [localhost:5725] {msandbox} (test) > execute st1;
+---------------------------+
| NULL in (select 1 from t) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (test) > insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5725] {msandbox} (test) > execute st1;
+---------------------------+
| NULL in (select 1 from t) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (test) > drop table t;
Query OK, 0 rows affected (0.09 sec)

mysql [localhost:5725] {msandbox} (test) > create table t (a int);
Query OK, 0 rows affected (0.06 sec)

mysql [localhost:5725] {msandbox} (test) > select NULL in (select 1 from t);
+---------------------------+
| NULL in (select 1 from t) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (test) > insert into t values (1);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:5725] {msandbox} (test) > select NULL in (select 1 from t);
+---------------------------+
| NULL in (select 1 from t) |
+---------------------------+
|                      NULL |
+---------------------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (test) > select * from t;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (test) > select 1 in (select 1 from t);
+------------------------+
| 1 in (select 1 from t) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (test) > select 2 in (select 1 from t);
+------------------------+
| 2 in (select 1 from t) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (test) > select null in (select 1 from t);
+---------------------------+
| null in (select 1 from t) |
+---------------------------+
|                      NULL |
+---------------------------+
1 row in set (0.00 sec)

mysql [localhost:5725] {msandbox} (test) >

Posted by developer:
 
Problem is not reproducible on latest 8.0 release (8.0.37).
Some earlier bug fix has probably fixed this too.

Documented fix as follows in the MySQL 8.0.37 changelog:

    IN should return NULL not only if the expression on the left
    hand side is NULL, but when no match is found in the list and
    one of the expressions in the list is NULL as well. In some
    cases, it was found that adding rows to a table caused a
    subsequent prepared SELECT NULL IN (SELECT ...) query to return
    a different result when the result should have remained the
    same.

Closed.