Description:
The query:
root@localhost [test] > EXPLAIN select 1 as f1 from information_schema.tables where "CHARACTER_SETS"= (select cast(table_name as char) from information_schema.tables where table_schema != 'performance_schema' order by table_name limit 1) limit 1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
Assertion fails at sql_show.cc:7764:
7757│ /*
7758│ We pass a condition, which can be used to do less file manipulations (for
7759│ example, WHERE TABLE_SCHEMA='test' allows to open only directory 'test',
7760│ not other database directories). Filling schema tables is done before
7761│ QEP_TAB::sort_table() (=filesort, for ORDER BY), so we can trust
7762│ that condition() is complete, has not been zeroed by filesort:
7763│ */
7764├> DBUG_ASSERT(qep_tab->condition() == qep_tab->condition_optim());
Stack (5.7):
...
#7 0x00007f705c31cc32 in __GI___assert_fail (assertion=0x1f74d50 "qep_tab->condition() == qep_tab->condition_optim()", file=0x1f73590 "sql/sql_show.cc", line=7764, function=0x1f76f60 <do_fill_table(THD*, TABLE_LIST*, QEP_TAB*)::__PRETTY_FUNCTION__> "bool do_fill_table(THD*, TABLE_LIST*, QEP_TAB*)") at assert.c:101
#8 0x0000000001602393 in do_fill_table (thd=0x7f7000012060, table_list=0x7f700000d0a8, qep_tab=0x7f70000d5338) at sql/sql_show.cc:7764
#9 0x00000000016027d5 in get_schema_tables_result (join=0x7f70000d4b20, executed_place=PROCESSED_BY_JOIN_EXEC) at sql/sql_show.cc:7869
#10 0x00000000015d9d83 in JOIN::prepare_result (this=0x7f70000d4b20) at sql/sql_select.cc:903
#11 0x000000000172f841 in explain_query_specification (ethd=0x7f7000012060, select_lex=0x7f700000c608, ctx=CTX_JOIN) at sql/opt_explain.cc:2083
#12 0x00000000017300af in mysql_explain_unit (ethd=0x7f7000012060, unit=0x7f700000c8e8) at sql/opt_explain.cc:2243
#13 0x0000000001729a19 in Explain::explain_subqueries (this=0x7f705525d260) at sql/opt_explain.cc:566
#14 0x0000000001729fb5 in Explain::send (this=0x7f705525d260) at sql/opt_explain.cc:660
#15 0x000000000172ee4b in explain_no_table (thd=0x7f7000012060, select_lex=0x7f70000e7678, message=0x1f5db90 "Impossible WHERE", ctx=CTX_JOIN) at sql/opt_explain.cc:1881
#16 0x000000000172f649 in explain_query_specification (ethd=0x7f7000012060, select_lex=0x7f70000e7678, ctx=CTX_JOIN) at sql/opt_explain.cc:2049
#17 0x00000000017300af in mysql_explain_unit (ethd=0x7f7000012060, unit=0x7f70000e7958) at sql/opt_explain.cc:2243
#18 0x000000000172fe2f in explain_query (ethd=0x7f7000012060, unit=0x7f70000e7958) at sql/opt_explain.cc:2188
#19 0x00000000015d87bf in handle_query (thd=0x7f7000012060, lex=0x7f7000014140, result=0x7f7000005d68, added_options=0, removed_options=0) at sql/sql_select.cc:176
#20 0x000000000158cc04 in execute_sqlcom_select (thd=0x7f7000012060, all_tables=0x7f700000c040) at sql/sql_parse.cc:4722
#21 0x000000000158636e in mysql_execute_command (thd=0x7f7000012060) at sql/sql_parse.cc:2450
#22 0x000000000158dbe8 in mysql_parse (thd=0x7f7000012060, parser_state=0x7f705525f210) at sql/sql_parse.cc:5154
#23 0x0000000001583435 in dispatch_command (command=COM_QUERY, thd=0x7f7000012060, packet=0x7f7000007801 "", packet_length=232) at sql/sql_parse.cc:1249
#24 0x000000000158222c in do_command (thd=0x7f7000012060) at sql/sql_parse.cc:835
...
Most likely this is I_S issue, since EXPLAIN on regular tables is not affected:
root@localhost [test] > SET @@max_heap_table_size = 2 * 1024 * 1024;
root@localhost [test] > CREATE TABLE t1 LIKE information_schema.tables;
root@localhost [test] > INSERT INTO t1 SELECT * FROM information_schema.tables;
Query OK, 181 rows affected (0,04 sec)
Records: 181 Duplicates: 0 Warnings: 0
root@localhost [test] > EXPLAIN select 1 as f1 from t1 where "CHARACTER_SETS"= (select cast(table_name as char) from t1 where table_schema != 'performance_schema' order by table_name limit 1) limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 181 | 100.00 | NULL |
| 2 | SUBQUERY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 181 | 90.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
2 rows in set, 1 warning (0,00 sec)
root@localhost [test] > SHOW WARNINGS;
+-------+------+------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select 1 AS `f1` from `test`.`t1` where 1 limit 1 |
+-------+------+------------------------------------------------------------------+
1 row in set (0,00 sec)
How to repeat:
EXPLAIN SELECT 1 AS F1 FROM information_schema.tables WHERE "CHARACTER_SETS"= (SELECT CAST(TABLE_NAME AS CHAR) FROM information_schema.tables WHERE table_schema != 'PERFORMANCE_SCHEMA' ORDER BY table_name LIMIT 1) LIMIT 1;
Simplified statement:
EXPLAIN SELECT 1 FROM DUAL WHERE (SELECT 1 FROM information_schema.tables WHERE table_schema ORDER BY table_name LIMIT 1);
