Note: The test case is similar to MDEV-13709, and the problem appeared around the same time, so it might well be a duplicate. But the crash and the introducing revision are different, so I'm filing it separately just in case.
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (9),(5),(1);
|
|
CREATE TABLE t2 (b INT) ENGINE=MyISAM;
|
|
CREATE TABLE t3 (c VARCHAR(8), d INT) ENGINE=MyISAM;
|
INSERT INTO t3 VALUES ('foo',2),('bar',6);
|
|
CREATE OR REPLACE VIEW v AS SELECT a FROM t1, t2 GROUP BY a;
|
|
SELECT * FROM t3 WHERE d IN ( SELECT * FROM v ) AND c LIKE 'z%' OR c IS NULL ;
|
10.3 016c35a7f2f4a288542f5fcb0e182c05238398e6
|
mysqld: /data/src/10.3/sql/sql_select.cc:7429: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570815e+308L)' failed.
|
170902 1:27:18 [ERROR] mysqld got signal 6 ;
|
|
#7 0x00007f337e4d7ee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
|
#8 0x000055ea73ed736a in greedy_search (join=0x7f336803bce8, remaining_tables=2, search_depth=62, prune_level=1, use_cond_selectivity=1) at /data/src/10.3/sql/sql_select.cc:7429
|
#9 0x000055ea73ed67d4 in choose_plan (join=0x7f336803bce8, join_tables=2) at /data/src/10.3/sql/sql_select.cc:6999
|
#10 0x000055ea73f0702d in JOIN::reoptimize (this=0x7f336803bce8, added_where=0x7f3368043330, join_tables=2, save_to=0x0) at /data/src/10.3/sql/sql_select.cc:25994
|
#11 0x000055ea73edb324 in JOIN::push_splitting_cond_into_derived (this=0x7f336803bce8, thd=0x7f3368000b00, cond=0x7f3368043330) at /data/src/10.3/sql/sql_select.cc:9078
|
#12 0x000055ea73edb4b1 in JOIN::improve_chosen_plan (this=0x7f336803b790, thd=0x7f3368000b00) at /data/src/10.3/sql/sql_select.cc:9105
|
#13 0x000055ea73ecf899 in make_join_statistics (join=0x7f336803b790, tables_list=..., keyuse_array=0x7f336803ba80) at /data/src/10.3/sql/sql_select.cc:4516
|
#14 0x000055ea73ec4d3c in JOIN::optimize_inner (this=0x7f336803b790) at /data/src/10.3/sql/sql_select.cc:1528
|
#15 0x000055ea73ec36f4 in JOIN::optimize (this=0x7f336803b790) at /data/src/10.3/sql/sql_select.cc:1123
|
#16 0x000055ea73e67039 in st_select_lex::optimize_unflattened_subqueries (this=0x7f3368004f00, const_only=false) at /data/src/10.3/sql/sql_lex.cc:3922
|
#17 0x000055ea74031714 in JOIN::optimize_unflattened_subqueries (this=0x7f336803b238) at /data/src/10.3/sql/opt_subselect.cc:5130
|
#18 0x000055ea73ec6b40 in JOIN::optimize_stage2 (this=0x7f336803b238) at /data/src/10.3/sql/sql_select.cc:2052
|
#19 0x000055ea73ec4e08 in JOIN::optimize_inner (this=0x7f336803b238) at /data/src/10.3/sql/sql_select.cc:1541
|
#20 0x000055ea73ec36f4 in JOIN::optimize (this=0x7f336803b238) at /data/src/10.3/sql/sql_select.cc:1123
|
#21 0x000055ea73ecc79e in mysql_select (thd=0x7f3368000b00, tables=0x7f3368014c68, wild_num=1, fields=..., conds=0x7f3368016ef8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f336801a498, unit=0x7f33680047c0, select_lex=0x7f3368004f00) at /data/src/10.3/sql/sql_select.cc:3723
|
#22 0x000055ea73ec0dbc in handle_select (thd=0x7f3368000b00, lex=0x7f33680046f8, result=0x7f336801a498, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:378
|
#23 0x000055ea73e8c26e in execute_sqlcom_select (thd=0x7f3368000b00, all_tables=0x7f3368014c68) at /data/src/10.3/sql/sql_parse.cc:6466
|
#24 0x000055ea73e827ef in mysql_execute_command (thd=0x7f3368000b00) at /data/src/10.3/sql/sql_parse.cc:3731
|
#25 0x000055ea73e8fbd4 in mysql_parse (thd=0x7f3368000b00, rawbuf=0x7f3368014a08 "SELECT * FROM t3 WHERE d IN ( SELECT * FROM v ) AND c LIKE 'z%' OR c IS NULL", length=76, parser_state=0x7f337876e610, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7921
|
#26 0x000055ea73e7d32e in dispatch_command (command=COM_QUERY, thd=0x7f3368000b00, packet=0x7f336800af11 "SELECT * FROM t3 WHERE d IN ( SELECT * FROM v ) AND c LIKE 'z%' OR c IS NULL ", packet_length=77, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1819
|
#27 0x000055ea73e7bd90 in do_command (thd=0x7f3368000b00) at /data/src/10.3/sql/sql_parse.cc:1370
|
#28 0x000055ea73fd2646 in do_handle_one_connection (connect=0x55ea77ca04f0) at /data/src/10.3/sql/sql_connect.cc:1418
|
#29 0x000055ea73fd23d3 in handle_one_connection (arg=0x55ea77ca04f0) at /data/src/10.3/sql/sql_connect.cc:1324
|
#30 0x000055ea7444a1dc in pfs_spawn_thread (arg=0x55ea77d40ef0) at /data/src/10.3/storage/perfschema/pfs.cc:1862
|
#31 0x00007f338041c494 in start_thread (arg=0x7f337876f700) at pthread_create.c:333
|
#32 0x00007f337e59493f in clone () from /lib/x86_64-linux-gnu/libc.so.6
|
No visible problem on a non-debug build.
The failure appeared in bb-10.2-ext / 10.3 trees with this revision:
commit b14e2b044b6483f22d9c33e6697479fbd859f747
|
Author: Igor Babaev <igor@askmonty.org>
|
Date: Thu Aug 3 21:19:19 2017 -0700
|
|
This first patch prepared for the task MDEV-13369:
|
"Optimization for equi-joins of derived tables with GROUP BY"
|
should be considered rather as a 'proof of concept'.
|
- relates to
-
MDEV-13709
Server crashes in update_ref_and_keys / JOIN::reoptimize
-
-
Closed
{"report":{"fcp":1781.0999999046326,"ttfb":235.5,"pageVisibility":"visible","entityId":62958,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"858a8978-4592-4084-ac3d-e926d6a787e3","navigationType":0,"readyForUser":1417.5,"redirectCount":0,"resourceLoadedEnd":1435.5,"resourceLoadedStart":266.39999985694885,"resourceTiming":[{"duration":222.29999995231628,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":266.39999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":266.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":488.69999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":222.40000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/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&whisper-enabled=true","startTime":266.89999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":266.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":489.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":306.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":267.09999990463257,"connectEnd":267.09999990463257,"connectStart":267.09999990463257,"domainLookupEnd":267.09999990463257,"domainLookupStart":267.09999990463257,"fetchStart":267.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":267.09999990463257,"responseEnd":573.5999999046326,"responseStart":573.5999999046326,"secureConnectionStart":267.09999990463257},{"duration":469.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/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&whisper-enabled=true","startTime":267.2999999523163,"connectEnd":267.2999999523163,"connectStart":267.2999999523163,"domainLookupEnd":267.2999999523163,"domainLookupStart":267.2999999523163,"fetchStart":267.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":267.2999999523163,"responseEnd":736.3999998569489,"responseStart":736.3999998569489,"secureConnectionStart":267.2999999523163},{"duration":235.29999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":267.39999985694885,"connectEnd":267.39999985694885,"connectStart":267.39999985694885,"domainLookupEnd":267.39999985694885,"domainLookupStart":267.39999985694885,"fetchStart":267.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":267.39999985694885,"responseEnd":502.69999980926514,"responseStart":502.69999980926514,"secureConnectionStart":267.39999985694885},{"duration":316,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":267.59999990463257,"connectEnd":267.59999990463257,"connectStart":267.59999990463257,"domainLookupEnd":267.59999990463257,"domainLookupStart":267.59999990463257,"fetchStart":267.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":267.59999990463257,"responseEnd":583.5999999046326,"responseStart":583.5999999046326,"secureConnectionStart":267.59999990463257},{"duration":316.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":267.7999999523163,"connectEnd":267.7999999523163,"connectStart":267.7999999523163,"domainLookupEnd":267.7999999523163,"domainLookupStart":267.7999999523163,"fetchStart":267.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":267.7999999523163,"responseEnd":584.5,"responseStart":584.5,"secureConnectionStart":267.7999999523163},{"duration":493.09999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":268,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":268,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":761.0999999046326,"responseStart":0,"secureConnectionStart":0},{"duration":472.60000014305115,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":268.19999980926514,"connectEnd":268.19999980926514,"connectStart":268.19999980926514,"domainLookupEnd":268.19999980926514,"domainLookupStart":268.19999980926514,"fetchStart":268.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":268.19999980926514,"responseEnd":740.7999999523163,"responseStart":740.7999999523163,"secureConnectionStart":268.19999980926514},{"duration":492.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/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":268.39999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":268.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":761.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":473.39999985694885,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/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":268.5,"connectEnd":268.5,"connectStart":268.5,"domainLookupEnd":268.5,"domainLookupStart":268.5,"fetchStart":268.5,"redirectEnd":0,"redirectStart":0,"requestStart":268.5,"responseEnd":741.8999998569489,"responseStart":741.8999998569489,"secureConnectionStart":268.5},{"duration":758.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":269.39999985694885,"connectEnd":269.39999985694885,"connectStart":269.39999985694885,"domainLookupEnd":269.39999985694885,"domainLookupStart":269.39999985694885,"fetchStart":269.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":269.39999985694885,"responseEnd":1027.8999998569489,"responseStart":1027.8999998569489,"secureConnectionStart":269.39999985694885},{"duration":953.7999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":272.7999999523163,"connectEnd":272.7999999523163,"connectStart":272.7999999523163,"domainLookupEnd":272.7999999523163,"domainLookupStart":272.7999999523163,"fetchStart":272.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":272.7999999523163,"responseEnd":1226.5999999046326,"responseStart":1226.5999999046326,"secureConnectionStart":272.7999999523163},{"duration":260.60000014305115,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":773.8999998569489,"connectEnd":773.8999998569489,"connectStart":773.8999998569489,"domainLookupEnd":773.8999998569489,"domainLookupStart":773.8999998569489,"fetchStart":773.8999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":773.8999998569489,"responseEnd":1034.5,"responseStart":1034.5,"secureConnectionStart":773.8999998569489},{"duration":277.2000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/e65b778d185daf5aee24936755b43da6/_/download/contextbatch/js/browser-metrics-plugin.contrib,-_super,-project.issue.navigator,-jira.view.issue,-atl.general/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":1158.2999999523163,"connectEnd":1158.2999999523163,"connectStart":1158.2999999523163,"domainLookupEnd":1158.2999999523163,"domainLookupStart":1158.2999999523163,"fetchStart":1158.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":1158.2999999523163,"responseEnd":1435.5,"responseStart":1435.5,"secureConnectionStart":1158.2999999523163},{"duration":213.60000014305115,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1313.3999998569489,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1313.3999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1527,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":48,"domainLookupEnd":56,"connectStart":56,"connectEnd":74,"secureConnectionStart":64,"requestStart":74,"responseStart":235,"responseEnd":272,"domLoading":245,"domInteractive":1476,"domContentLoadedEventStart":1476,"domContentLoadedEventEnd":1526,"domComplete":2807,"loadEventStart":2807,"loadEventEnd":2809,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1460.8999998569489},{"name":"bigPipe.sidebar-id.end","time":1461.6999998092651},{"name":"bigPipe.activity-panel-pipe-id.start","time":1461.8999998569489},{"name":"bigPipe.activity-panel-pipe-id.end","time":1464.5},{"name":"activityTabFullyLoaded","time":1531.1999998092651}],"measures":[],"correlationId":"48bf6a1f4fcedd","effectiveType":"4g","downlink":9.7,"rtt":0,"serverDuration":93,"dbReadsTimeInMs":11,"dbConnsTimeInMs":21,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}