Description:
I created a simple prepare statement, which involves NULL in (select 1 from t), mysql cannot check whether (select 1 from t) is empty set.
How to repeat:
use test;
create table t (a int);
insert into t values (1);
select NULL in (select 1 from t);
prepare st1 from "select NULL in (select 1 from t)";
execute st1;
delete from t;
select NULL in (select 1 from t);
execute st1;
First time, the select statement and execute st1 run same result. After delete all record from table t, select statement returns different value, and execute st1 reult doesn't change.
mysql> use test;
Database changed
mysql> create table t (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select NULL in (select 1 from t);
+---------------------------+
| NULL in (select 1 from t) |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0.01 sec)
mysql> prepare st1 from "select NULL in (select 1 from t)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute st1;
+---------------------------+
| NULL in (select 1 from t) |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0.00 sec)
mysql> delete from t;
Query OK, 1 row affected (0.00 sec)
mysql> select NULL in (select 1 from t);
+---------------------------+
| NULL in (select 1 from t) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
mysql> execute st1;
+---------------------------+
| NULL in (select 1 from t) |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0.00 sec)
