mysql in语句产生的奇怪的bug

mysql in语句产生一个奇怪的bug，在将performance_schema.events_statements_history_long和performance_schema.threads c通过thread_id列使用in连接时，出不来结果，但是用exists可以出来。

mysql> (select  c.thread_id  from information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c where w.blocking_trx_id = b.trx_id and b.trx_mysql_thread_id = c.PROCESSLIST_ID ) ;
+-----------+
| thread_id |
+-----------+
|        66 |
+-----------+
1 row in set, 1 warning (0.00 sec)

上面这个有一个66的结果

 

 

mysql>  select  THREAD_ID,EVENT_ID,EVENT_NAME,CURRENT_SCHEMA,SQL_TEXT from performance_schema.events_statements_history_long cn  where cn.thread_id in  (select  c.thread_id  from information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c where w.blocking_trx_id = b.trx_id and b.trx_mysql_thread_id = c.PROCESSLIST_ID ) order by THREAD_ID,EVENT_ID;
Empty set (0.01 sec)

 

上面这个没有内容输出

mysql> select  THREAD_ID,EVENT_ID,EVENT_NAME,CURRENT_SCHEMA,SQL_TEXT from performance_schema.events_statements_history_long cn  where exists (select  1  from information_schema.innodb_lock_waits w,information_schema.innodb_trx b,performance_schema.threads c where w.blocking_trx_id = b.trx_id and b.trx_mysql_thread_id = c.PROCESSLIST_ID and  cn.THREAD_ID = c.THREAD_ID );
+-----------+----------+------------------------------+----------------+----------------------------------+
| THREAD_ID | EVENT_ID | EVENT_NAME                   | CURRENT_SCHEMA | SQL_TEXT                         |
+-----------+----------+------------------------------+----------------+----------------------------------+
|        66 |        1 | statement/sql/select         | NULL           | select @@version_comment limit 1 |
|        66 |        2 | statement/sql/show_databases | NULL           | show databases                   |
|        66 |        3 | statement/sql/error          | NULL           | creat database wftest            |
|        66 |        4 | statement/sql/create_db      | NULL           | create database wftest           |
|        66 |        5 | statement/sql/select         | NULL           | SELECT DATABASE()                |
|        66 |        6 | statement/com/Init DB        | NULL           | NULL                             |
|        66 |        7 | statement/sql/show_databases | wftest         | show databases                   |
|        66 |        8 | statement/sql/show_tables    | wftest         | show tables                      |
|        66 |        9 | statement/sql/create_table   | wftest         | create table t(id int)           |
|        66 |       10 | statement/sql/insert         | wftest         | insert into t values(1)          |
|        66 |       11 | statement/sql/commit         | wftest         | commit                           |
|        66 |       12 | statement/sql/begin          | wftest         | start transaction                |
|        66 |       13 | statement/sql/update         | wftest         | update t set id = 2 where id=1   |
+-----------+----------+------------------------------+----------------+----------------------------------+
13 rows in set, 289 warnings (0.02 sec)

上面这个有结果。奇怪。

反馈给mysql团队了，等等看后续如何。
https://bugs.mysql.com/bug.php?id=100795