Type:
Bug
Priority:
Major
Resolution:
Fixed
Affects Version/s:
None
Component/s:
None
mysqld: sql_select.cc:21160: int JOIN::print_explain(select_result_sink*, bool, bool, bool, bool, const char*): Assertion `this->optimized == 2' failed.
#6 0x00007f9ae9559235 in __assert_fail () from /lib64/libc.so.6
#7 0x00000000007a7fa7 in JOIN::print_explain (this=0x7f9acd09f168, result=0x24ba358,
on_the_fly=true, need_tmp_table=false, need_order=false, distinct=false,
message=0xcfc4cd "Not yet optimized") at sql_select.cc:21160
#8 0x00000000005d60a5 in st_select_lex::print_explain (this=0x7f9acd08ede8,
output=0x24ba358) at sql_lex.cc:3784
#9 0x00000000005d613e in st_select_lex_unit::print_explain (this=0x7f9acd08e8e0,
output=0x24ba358) at sql_lex.cc:3807
#10 0x00000000006cca6c in Show_explain_request::get_explain_data (arg=0x7f9abe1beba0)
at sql_class.cc:3042
#11 0x000000000095b035 in Apc_target::process_apc_requests (this=0x7f9acd08e650)
at my_apc.cc:255
#12 0x000000000095a9c6 in Apc_target::disable (this=0x7f9acd08e650) at my_apc.cc:83
#13 0x0000000000777cec in JOIN::exec (this=0x7f9acd09f758) at sql_select.cc:2126
#14 0x000000000077b125 in mysql_select (thd=0x7f9acd08c338,
rref_pointer_array=0x23d6878, tables=0x7f9acd09ed70, wild_num=0, fields=..., conds=
0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2416200192, result=0x7f9acd09f660, unit=0x23d6118,
select_lex=0x23d6620) at sql_select.cc:3023
#15 0x00000000008fcd0a in mysql_derived_fill (thd=0x7f9acd08c338, lex=0x7f9acd08e840,
derived=0x23d56a8) at sql_derived.cc:889
#16 0x00000000008fc952 in mysql_derived_optimize (thd=0x7f9acd08c338,
lex=0x7f9acd08e840, derived=0x23d56a8) at sql_derived.cc:778
#17 0x00000000008fb90b in mysql_handle_single_derived (lex=0x7f9acd08e840,
derived=0x23d56a8, phases=4) at sql_derived.cc:176
#18 0x000000000077022d in TABLE_LIST::handle_derived (this=0x23d56a8,
lex=0x7f9acd08e840, phases=4) at table.cc:5846
#19 0x00000000005d4b46 in st_select_lex::handle_derived (this=0x7f9acd08ede8,
lex=0x7f9acd08e840, phases=4) at sql_lex.cc:3171
#20 0x00000000007738cc in JOIN::optimize_inner (this=0x7f9acd09f168) at sql_select.cc:971
#21 0x0000000000773796 in JOIN::optimize (this=0x7f9acd09f168) at sql_select.cc:938
#22 0x000000000077b08f in mysql_select (thd=0x7f9acd08c338,
rref_pointer_array=0x7f9acd08f040, tables=0x23d56a8, wild_num=1, fields=...,
conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2147764736, result=0x23d7428, unit=0x7f9acd08e8e0,
select_lex=0x7f9acd08ede8) at sql_select.cc:3009
#23 0x0000000000771c0a in handle_select (thd=0x7f9acd08c338, lex=0x7f9acd08e840,
result=0x23d7428, setup_tables_done_option=0) at sql_select.cc:332
#24 0x0000000000700181 in execute_sqlcom_select (thd=0x7f9acd08c338,
all_tables=0x23d56a8) at sql_parse.cc:5178
---Type <return> to continue, or q <return> to quit---
#25 0x00000000006f70c7 in mysql_execute_command (thd=0x7f9acd08c338) at sql_parse.cc:2285
#26 0x0000000000702b5c in mysql_parse (thd=0x7f9acd08c338,
rawbuf=0x23d54c0 "SELECT * FROM view_A, B", length=23,
found_semicolon=0x7f9abe207c98) at sql_parse.cc:6179
#27 0x00000000006f4876 in dispatch_command (command=COM_QUERY, thd=0x7f9acd08c338,
packet=0x7f9acd0903c9 "SELECT * FROM view_A, B", packet_length=23)
at sql_parse.cc:1229
#28 0x00000000006f3baa in do_command (thd=0x7f9acd08c338) at sql_parse.cc:924
#29 0x00000000006f09ee in handle_one_connection (arg=0x7f9acd08c338)
at sql_connect.cc:1218
#30 0x00007f9aea214a4f in start_thread () from /lib64/libpthread.so.0
bzr version-info
revision-id: psergey@askmonty.org-20120426031831-0iujnre01v3tlwkf
date: 2012-04-26 08:48:31 +0530
build-date: 2012-05-01 22:20:22 +0300
revno: 3504
optimizer_switch (default):
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=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
Grammar to reproduce (3.yy):
thread1_init:
CREATE TABLE A (a INT) ENGINE=MyISAM; CREATE ALGORITHM=TEMPTABLE VIEW view_A AS SELECT * FROM A; INSERT INTO A VALUES (8); CREATE TABLE B (b INT) ENGINE=MyISAM; INSERT INTO B VALUES (4),(5),(6),(7),(8),(9);
thread2_init:
SET @con_id = CONNECTION_ID()-1;
query:
SELECT * FROM view_A, B;
thread2:
SHOW EXPLAIN FOR @con_id;
Run as:
perl ./runall.pl \
--queries=100M \
--threads=2 \
--duration=600 \
--skip-gendata \
--grammar=3.yy \
--basedir=<basedir> \
--vardir=<vardir>
relates to
MDEV-165
MWL#182: Explain running statements: finish coding, submit for review
Closed
{"report":{"fcp":764.8999999761581,"ttfb":196.5,"pageVisibility":"visible","entityId":11533,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"13d1d178-7e07-41f2-8986-6bd6e4e6c72f","navigationType":0,"readyForUser":889.5999999046326,"redirectCount":0,"resourceLoadedEnd":512.7999999523163,"resourceLoadedStart":203.5,"resourceTiming":[{"duration":7.5,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":203.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":203.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":211,"responseStart":0,"secureConnectionStart":0},{"duration":7.600000023841858,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":203.79999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":203.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":211.39999997615814,"responseStart":0,"secureConnectionStart":0},{"duration":166.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":203.79999995231628,"connectEnd":203.79999995231628,"connectStart":203.79999995231628,"domainLookupEnd":203.79999995231628,"domainLookupStart":203.79999995231628,"fetchStart":203.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":216.5,"responseEnd":370.5,"responseStart":232.29999995231628,"secureConnectionStart":203.79999995231628},{"duration":276.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":203.89999997615814,"connectEnd":203.89999997615814,"connectStart":203.89999997615814,"domainLookupEnd":203.89999997615814,"domainLookupStart":203.89999997615814,"fetchStart":203.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":217.5,"responseEnd":480.1999999284744,"responseStart":234.89999997615814,"secureConnectionStart":203.89999997615814},{"duration":40.700000047683716,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":204.09999990463257,"connectEnd":204.09999990463257,"connectStart":204.09999990463257,"domainLookupEnd":204.09999990463257,"domainLookupStart":204.09999990463257,"fetchStart":204.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":217.69999992847443,"responseEnd":244.79999995231628,"responseStart":241.89999997615814,"secureConnectionStart":204.09999990463257},{"duration":40.89999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":204.19999992847443,"connectEnd":204.19999992847443,"connectStart":204.19999992847443,"domainLookupEnd":204.19999992847443,"domainLookupStart":204.19999992847443,"fetchStart":204.19999992847443,"redirectEnd":0,"redirectStart":0,"requestStart":217.89999997615814,"responseEnd":245.09999990463257,"responseStart":242.69999992847443,"secureConnectionStart":204.19999992847443},{"duration":44.800000071525574,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":204.19999992847443,"connectEnd":204.19999992847443,"connectStart":204.19999992847443,"domainLookupEnd":204.19999992847443,"domainLookupStart":204.19999992847443,"fetchStart":204.19999992847443,"redirectEnd":0,"redirectStart":0,"requestStart":222.79999995231628,"responseEnd":249,"responseStart":246.09999990463257,"secureConnectionStart":204.19999992847443},{"duration":12.299999952316284,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":204.39999997615814,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":204.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":216.69999992847443,"responseStart":0,"secureConnectionStart":0},{"duration":14.799999952316284,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":204.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":204.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":219.29999995231628,"responseStart":0,"secureConnectionStart":0},{"duration":76.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":204.5,"connectEnd":204.5,"connectStart":204.5,"domainLookupEnd":204.5,"domainLookupStart":204.5,"fetchStart":204.5,"redirectEnd":0,"redirectStart":0,"requestStart":223.39999997615814,"responseEnd":281,"responseStart":277.2999999523163,"secureConnectionStart":204.5},{"duration":44.60000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":204.59999990463257,"connectEnd":204.59999990463257,"connectStart":204.59999990463257,"domainLookupEnd":204.59999990463257,"domainLookupStart":204.59999990463257,"fetchStart":204.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":223.59999990463257,"responseEnd":249.19999992847443,"responseStart":247,"secureConnectionStart":204.59999990463257},{"duration":306.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":205.29999995231628,"connectEnd":205.29999995231628,"connectStart":205.29999995231628,"domainLookupEnd":205.29999995231628,"domainLookupStart":205.29999995231628,"fetchStart":205.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":231.09999990463257,"responseEnd":511.59999990463257,"responseStart":500.39999997615814,"secureConnectionStart":205.29999995231628},{"duration":302.89999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":209.89999997615814,"connectEnd":209.89999997615814,"connectStart":209.89999997615814,"domainLookupEnd":209.89999997615814,"domainLookupStart":209.89999997615814,"fetchStart":209.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":232,"responseEnd":512.7999999523163,"responseStart":505.6999999284744,"secureConnectionStart":209.89999997615814},{"duration":77.70000004768372,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":520.6999999284744,"connectEnd":520.6999999284744,"connectStart":520.6999999284744,"domainLookupEnd":520.6999999284744,"domainLookupStart":520.6999999284744,"fetchStart":520.6999999284744,"redirectEnd":0,"redirectStart":0,"requestStart":567.3999999761581,"responseEnd":598.3999999761581,"responseStart":597.3999999761581,"secureConnectionStart":520.6999999284744},{"duration":121.39999997615814,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":756.8999999761581,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":756.8999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":878.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":146.39999997615814,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":763.6999999284744,"connectEnd":763.6999999284744,"connectStart":763.6999999284744,"domainLookupEnd":763.6999999284744,"domainLookupStart":763.6999999284744,"fetchStart":763.6999999284744,"redirectEnd":0,"redirectStart":0,"requestStart":879.5999999046326,"responseEnd":910.0999999046326,"responseStart":907.8999999761581,"secureConnectionStart":763.6999999284744}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":6,"responseStart":197,"responseEnd":203,"domLoading":200,"domInteractive":956,"domContentLoadedEventStart":956,"domContentLoadedEventEnd":1000,"domComplete":1139,"loadEventStart":1139,"loadEventEnd":1140,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":939.2999999523163},{"name":"bigPipe.sidebar-id.end","time":940.0999999046326},{"name":"bigPipe.activity-panel-pipe-id.start","time":940.1999999284744},{"name":"bigPipe.activity-panel-pipe-id.end","time":941.0999999046326},{"name":"activityTabFullyLoaded","time":1014.1999999284744}],"measures":[],"correlationId":"65c7bdd2b77b8b","effectiveType":"4g","downlink":9.7,"rtt":0,"serverDuration":92,"dbReadsTimeInMs":10,"dbConnsTimeInMs":18,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Fix pushed to 5.3-show-explain2