Bug #76205 sql_show.cc:7764: Assertion `qep_tab->condition() == qep_tab->condition_optim()\
Submitted: 7 Mar 2015 7:01 Modified: 22 May 2015 15:27
Reporter: Gleb Shchepa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.7+ OS:Any
Assigned to: CPU Architecture:Any

[7 Mar 2015 7:01] Gleb Shchepa
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);
[22 May 2015 15:27] Paul Dubois
Noted in 5.7.8, 5.8.0 changelogs.

EXPLAIN for a query containing an uncorrelated subquery could attempt
to materialize the subquery twice, raising an assertion.