Bug #94857 execute statement doesn't return correct result
Submitted: 1 Apr 2019 20:37 Modified: 4 Apr 2019 13:03
Reporter: Weidong Yu Email Updates:
Status: Verified 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

[1 Apr 2019 20:37] Weidong Yu
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);
[4 Apr 2019 13:02] MySQL Verification Team
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) >