Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
This query:
EXPLAIN SELECT *
FROM t1
WHERE t1.a = (
SELECT SUM( c )
FROM t2
WHERE (
SELECT DISTINCT b
FROM t3
) > 0
);
crashes as follows:
#4 <signal handler called>
#5 0x08347fd1 in select_describe (join=0x9b130cc8, need_tmp_table=true, need_order=false, distinct=false, message=0x0) at sql_select.cc:20567
#6 0x0831d997 in JOIN::exec (this=0x9b130cc8) at sql_select.cc:2181
#7 0x0831fd0a in mysql_select (thd=0xaecd1e8, rref_pointer_array=0xa3314b04, tables=0xa3314f58, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0,
group=0x0, having=0x0, proc_param=0x0, select_options=2147764741, result=0xa3315660, unit=0xa3314ba0, select_lex=0xa33149c8) at sql_select.cc:2891
#8 0x08349e9f in mysql_explain_union (thd=0xaecd1e8, unit=0xa3314ba0, result=0xa3315660) at sql_select.cc:21040
#9 0x08349b7a in select_describe (join=0xa332daa0, need_tmp_table=false, need_order=false, distinct=false,
message=0x88e931c "Impossible WHERE noticed after reading const tables") at sql_select.cc:20997
#10 0x08330ddf in return_zero_rows (join=0xa332daa0, result=0xa3315660, tables=..., fields=..., send_row=true, select_options=2147764740,
info=0x88e931c "Impossible WHERE noticed after reading const tables", having=0x0) at sql_select.cc:10170
#11 0x0831d583 in JOIN::exec (this=0xa332daa0) at sql_select.cc:2122
#12 0x0831fd0a in mysql_select (thd=0xaecd1e8, rref_pointer_array=0xa33141dc, tables=0xa3314778, wild_num=0, fields=..., conds=0xa3315308, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xa3315660, unit=0xa3314278, select_lex=0xa33140a0)
at sql_select.cc:2891
#13 0x08349e9f in mysql_explain_union (thd=0xaecd1e8, unit=0xa3314278, result=0xa3315660) at sql_select.cc:21040
#14 0x08349b7a in select_describe (join=0xa3326830, need_tmp_table=false, need_order=false, distinct=false,
message=0x88e931c "Impossible WHERE noticed after reading const tables") at sql_select.cc:20997
#15 0x08330ddf in return_zero_rows (join=0xa3326830, result=0xa3315660, tables=..., fields=..., send_row=false, select_options=2147764740,
info=0x88e931c "Impossible WHERE noticed after reading const tables", having=0x0) at sql_select.cc:10170
#16 0x0831d583 in JOIN::exec (this=0xa3326830) at sql_select.cc:2122
#17 0x0831fd0a in mysql_select (thd=0xaecd1e8, rref_pointer_array=0xaeced64, tables=0xa3313da0, wild_num=1, fields=..., conds=0xa33154f0, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xa3315660, unit=0xaece94c, select_lex=0xaecec28)
at sql_select.cc:2891
#18 0x08349e9f in mysql_explain_union (thd=0xaecd1e8, unit=0xaece94c, result=0xa3315660) at sql_select.cc:21040
#19 0x082b2e07 in execute_sqlcom_select (thd=0xaecd1e8, all_tables=0xa3313da0) at sql_parse.cc:5069
#20 0x082a9d89 in mysql_execute_command (thd=0xaecd1e8) at sql_parse.cc:2234
#21 0x082b55c1 in mysql_parse (thd=0xaecd1e8,
rawbuf=0xa3313bb0 "EXPLAIN SELECT *\nFROM t1\nWHERE t1.a = (\nSELECT SUM( c )\nFROM t2\nWHERE (\nSELECT DISTINCT b\nFROM t3\n) > 0\n)", length=105,
found_semicolon=0x9f6f9228) at sql_parse.cc:6091
#22 0x082a7a06 in dispatch_command (command=COM_QUERY, thd=0xaecd1e8, packet=0xaf258b1 "", packet_length=105) at sql_parse.cc:1211
#23 0x082a6e61 in do_command (thd=0xaecd1e8) at sql_parse.cc:906
#24 0x082a3ec9 in handle_one_connection (arg=0xaecd1e8) at sql_connect.cc:1186
#25 0x00821919 in start_thread () from /lib/libpthread.so.0
#26 0x00453cce in clone () from /lib/libc.so.6
explain in other versions:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary
optimizer switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
bzr version-info
revision-id: <email address hidden>
date: 2011-08-16 15:51:40 +0300
build-date: 2011-08-16 17:45:06 +0300
revno: 3157
branch-nick: maria-5.3
DISTINCT appears to be required which makes the query partially unrealistic. Maybe it is best to ignore/filter out DISTINCT inside subqueries early in the optimization.
Repeatable on maria-5.3. Not repeatable in maria-5.2, mysql-5.5. Not influenced by any particular switches.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries
Analysis:
The crash is a result of yet another instance when the optimizer evaluates
a subquery during the constant table optimization phase during EXPLAIN.
When the subuqery requires a temporary table, the evaluation of the
subquery substitutes a query table with this temporary table.
Once EXPLAIN needs to print the name of the table, it needs the
corresponding TABLE_LIST object of each table. Such object doesn't
exist for temporary tables, so we get a null-pointer exception.
Launchpad bug id: 827416
Re: Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries
Test case:
--source include/have_innodb.inc
DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( b int) ENGINE=InnoDB;
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( c int) ENGINE=InnoDB;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int NOT NULL , PRIMARY KEY (a)) ENGINE=InnoDB;
EXPLAIN SELECT *
FROM t1
WHERE t1.a = (
SELECT SUM( c )
FROM t2
WHERE (
SELECT DISTINCT b
FROM t3
) > 0
);