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