The problem appeared on 5.5 with the following merge:
revno: 3671 [merge]
|
revision-id: sergii@pisem.net-20130228214729-t0xhegqa9uzsr1k9
|
parent: sergii@pisem.net-20130228191953-70sigyi7o2viwe53
|
parent: sergii@pisem.net-20130228204847-vlit40812mz8af3u
|
committer: Sergei Golubchik <sergii@pisem.net>
|
branch nick: 5.5
|
timestamp: Thu 2013-02-28 22:47:29 +0100
|
message:
|
5.3->5.5 merge
|
It's possible that the underlying issue also exists on 5.3, but there is no visible indication of it.
On 5.5:
mysqld: /data/bzr/5.5/sql/sql_select.cc:1371: int JOIN::optimize(): Assertion `item->type() == Item::FIELD_ITEM' failed.
|
130423 1:52:20 [ERROR] mysqld got signal 6 ;
|
#7 0x00007faaa6625192 in __GI___assert_fail (assertion=0xd2ed00 "item->type() == Item::FIELD_ITEM", file=0xd2e9e0 "/data/bzr/5.5/sql/sql_select.cc", line=1371, function=0xd31d10 "int JOIN::optimize()") at assert.c:103
|
#8 0x0000000000640b02 in JOIN::optimize (this=0x7faa900202f8) at /data/bzr/5.5/sql/sql_select.cc:1371
|
#9 0x00000000006469e1 in mysql_select (thd=0x3117820, rref_pointer_array=0x311a828, tables=0x7faa900077f0, wild_num=1, fields=..., conds=0x7faa9001afd0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7faa9001b1b8, unit=0x3119ef8, select_lex=0x311a5d0) at /data/bzr/5.5/sql/sql_select.cc:3048
|
#10 0x000000000063d5ea in handle_select (thd=0x3117820, lex=0x3119e48, result=0x7faa9001b1b8, setup_tables_done_option=0) at /data/bzr/5.5/sql/sql_select.cc:318
|
#11 0x000000000061643d in execute_sqlcom_select (thd=0x3117820, all_tables=0x7faa900077f0) at /data/bzr/5.5/sql/sql_parse.cc:4641
|
#12 0x000000000060ee8c in mysql_execute_command (thd=0x3117820) at /data/bzr/5.5/sql/sql_parse.cc:2195
|
#13 0x0000000000618cdc in mysql_parse (thd=0x3117820, rawbuf=0x7faa900074c8 "SELECT * FROM country AS t1, country AS t2 LEFT JOIN country AS t3 ON ( t2.code = t3.code )\nWHERE t1.code = t2.code AND ( t1.code = ( SELECT MIN(code) FROM country ) OR 0 )", length=172, parser_state=0x7faa9bd08500) at /data/bzr/5.5/sql/sql_parse.cc:5759
|
#14 0x000000000060c3dc in dispatch_command (command=COM_QUERY, thd=0x3117820, packet=0x320cc41 "SELECT * FROM country AS t1, country AS t2 LEFT JOIN country AS t3 ON ( t2.code = t3.code )\nWHERE t1.code = t2.code AND ( t1.code = ( SELECT MIN(code) FROM country ) OR 0 )", packet_length=172) at /data/bzr/5.5/sql/sql_parse.cc:1068
|
#15 0x000000000060b61d in do_command (thd=0x3117820) at /data/bzr/5.5/sql/sql_parse.cc:794
|
#16 0x000000000071092d in do_handle_one_connection (thd_arg=0x3117820) at /data/bzr/5.5/sql/sql_connect.cc:1266
|
#17 0x0000000000710314 in handle_one_connection (arg=0x3117820) at /data/bzr/5.5/sql/sql_connect.cc:1181
|
#18 0x000000000096c0b8 in pfs_spawn_thread (arg=0x31b5a70) at /data/bzr/5.5/storage/perfschema/pfs.cc:1015
|
#19 0x00007faaa73f5e9a in start_thread (arg=0x7faa9bd09700) at pthread_create.c:308
|
#20 0x00007faaa66e9cbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
|
revision-id: knielsen@knielsen-hq.org-20130422142239-e1b8ly4fsw30i2c5
|
revno: 3734
|
branch-nick: 5.5
|
BUILD/compile-pentium-debug-max-no-ndb
|
Test case:
SET join_cache_level=3;
|
SET optimizer_switch = 'in_to_exists=on,outer_join_with_cache=on,join_cache_hashed=on';
|
CREATE TABLE country (code varchar(3));
|
INSERT INTO country VALUES ('USA'),('FRA');
|
|
SELECT * FROM country AS t1, country AS t2 LEFT JOIN country AS t3 ON ( t2.code = t3.code )
|
WHERE t1.code = t2.code AND ( t1.code = ( SELECT MIN(code) FROM country ) OR 0 );
|
Minimal optimizer_switch: in_to_exists=on,outer_join_with_cache=on,join_cache_hashed=on
Full optimizer_switch (default):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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,extended_keys=off
|
EXPLAIN also fails.
{"report":{"fcp":1026.8000000715256,"ttfb":340.7000000476837,"pageVisibility":"visible","entityId":23911,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"48078107-a6d8-4b9c-8491-a782d5f57578","navigationType":0,"readyForUser":1092.7000000476837,"redirectCount":0,"resourceLoadedEnd":1204.3000000715256,"resourceLoadedStart":349.8000000715256,"resourceTiming":[{"duration":235,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":349.8000000715256,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":349.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":584.8000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":234.60000002384186,"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":350.2000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":350.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":584.8000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":242.79999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":350.3000000715256,"connectEnd":350.3000000715256,"connectStart":350.3000000715256,"domainLookupEnd":350.3000000715256,"domainLookupStart":350.3000000715256,"fetchStart":350.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":350.3000000715256,"responseEnd":593.1000000238419,"responseStart":593.1000000238419,"secureConnectionStart":350.3000000715256},{"duration":300.39999997615814,"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":350.8000000715256,"connectEnd":350.8000000715256,"connectStart":350.8000000715256,"domainLookupEnd":350.8000000715256,"domainLookupStart":350.8000000715256,"fetchStart":350.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":350.8000000715256,"responseEnd":651.2000000476837,"responseStart":651.2000000476837,"secureConnectionStart":350.8000000715256},{"duration":304.09999990463257,"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":350.90000009536743,"connectEnd":350.90000009536743,"connectStart":350.90000009536743,"domainLookupEnd":350.90000009536743,"domainLookupStart":350.90000009536743,"fetchStart":350.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":350.90000009536743,"responseEnd":655,"responseStart":655,"secureConnectionStart":350.90000009536743},{"duration":304.60000002384186,"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":351,"connectEnd":351,"connectStart":351,"domainLookupEnd":351,"domainLookupStart":351,"fetchStart":351,"redirectEnd":0,"redirectStart":0,"requestStart":351,"responseEnd":655.6000000238419,"responseStart":655.6000000238419,"secureConnectionStart":351},{"duration":305,"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":351,"connectEnd":351,"connectStart":351,"domainLookupEnd":351,"domainLookupStart":351,"fetchStart":351,"redirectEnd":0,"redirectStart":0,"requestStart":351,"responseEnd":656,"responseStart":655.9000000953674,"secureConnectionStart":351},{"duration":305.39999997615814,"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":351.10000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":351.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":656.5,"responseStart":0,"secureConnectionStart":0},{"duration":305.5,"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":351.3000000715256,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":351.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":656.8000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":305,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":351.3000000715256,"connectEnd":351.3000000715256,"connectStart":351.3000000715256,"domainLookupEnd":351.3000000715256,"domainLookupStart":351.3000000715256,"fetchStart":351.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":351.3000000715256,"responseEnd":656.3000000715256,"responseStart":656.3000000715256,"secureConnectionStart":351.3000000715256},{"duration":305.40000009536743,"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":351.5,"connectEnd":351.5,"connectStart":351.5,"domainLookupEnd":351.5,"domainLookupStart":351.5,"fetchStart":351.5,"redirectEnd":0,"redirectStart":0,"requestStart":351.5,"responseEnd":656.9000000953674,"responseStart":656.9000000953674,"secureConnectionStart":351.5},{"duration":504.3000000715256,"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":360.60000002384186,"connectEnd":360.60000002384186,"connectStart":360.60000002384186,"domainLookupEnd":360.60000002384186,"domainLookupStart":360.60000002384186,"fetchStart":360.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":360.60000002384186,"responseEnd":864.9000000953674,"responseStart":864.9000000953674,"secureConnectionStart":360.60000002384186},{"duration":842.2000000476837,"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":362.10000002384186,"connectEnd":362.10000002384186,"connectStart":362.10000002384186,"domainLookupEnd":362.10000002384186,"domainLookupStart":362.10000002384186,"fetchStart":362.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":362.10000002384186,"responseEnd":1204.3000000715256,"responseStart":1204.3000000715256,"secureConnectionStart":362.10000002384186},{"duration":340.89999997615814,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":810.3000000715256,"connectEnd":810.3000000715256,"connectStart":810.3000000715256,"domainLookupEnd":810.3000000715256,"domainLookupStart":810.3000000715256,"fetchStart":810.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":810.3000000715256,"responseEnd":1151.2000000476837,"responseStart":1151.2000000476837,"secureConnectionStart":810.3000000715256},{"duration":260.8000000715256,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1003.6000000238419,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1003.6000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1264.4000000953674,"responseStart":0,"secureConnectionStart":0}],"fetchStart":1,"domainLookupStart":1,"domainLookupEnd":1,"connectStart":1,"connectEnd":1,"requestStart":176,"responseStart":341,"responseEnd":362,"domLoading":345,"domInteractive":1220,"domContentLoadedEventStart":1220,"domContentLoadedEventEnd":1257,"domComplete":2177,"loadEventStart":2177,"loadEventEnd":2179,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1206.6000000238419},{"name":"bigPipe.sidebar-id.end","time":1207.5},{"name":"bigPipe.activity-panel-pipe-id.start","time":1207.6000000238419},{"name":"bigPipe.activity-panel-pipe-id.end","time":1209.9000000953674},{"name":"activityTabFullyLoaded","time":1265.2000000476837}],"measures":[],"correlationId":"ed2b34b73433a3","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":92,"dbReadsTimeInMs":11,"dbConnsTimeInMs":18,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}