Type:
Bug
Priority:
Major
Resolution:
Fixed
Affects Version/s:
5.5.67 , 10.1.44 , 10.2.31 , 10.3.20 , 10.4.10 , 10.5.1
The problem can be reproduced with the following test case:
create table t0 (a int );
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
insert into t0 select a+10 from t0;
insert into t0 select a+20 from t0;
insert into t0 select a+40 from t0;
insert into t0 select a+80 from t0;
insert into t0 select a+160 from t0;
delete from t0 where a > 300;
create table t1 (
f1 int , f2 int , f3 int , f4 int ,
primary key (f1), key (f3), key (f4)
) engine=myisam;
insert into t1 select a+100, a+100, a+100, a+100 from t0;
insert into t1 VALUES (9,0,2,6), (9930,0,0, NULL );
analyze table t1;
set optimizer_switch= 'index_merge_sort_union=off' ;
set optimizer_switch= 'index_merge_union=on' ;
explain
select * from t1
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
insert into t1 values (52,0,1,0),(53,0,1,0);
insert into t1 values (50,0,1,0),(51,0,1,0);
insert into t1 values (48,0,1,0),(49,0,1,0);
insert into t1 values (46,0,1,0),(47,0,1,0);
insert into t1 values (44,0,1,0),(45,0,1,0);
analyze table t1;
explain
select * from t1
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
The first explain shows that a ROR union index merge has been chosen that would select 3 rows.
MariaDB [test]> explain
-> select * from t1
-> where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
-> (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
| 1 | SIMPLE | t1 | index_merge | PRIMARY,f3,f4 | f3,PRIMARY,f3 | 5,4,5 | NULL | 3 | Using union(f3,PRIMARY,f3); Using where |
+------+-------------+-------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
The second explain shows that a table scan was used to access all 309 rows of table t1 though a plan with a ROR union index merge that would access less than 15 rows still could be used.
MariaDB [test]> explain
-> select * from t1
-> where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
-> (f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | PRIMARY,f3,f4 | NULL | NULL | NULL | 313 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{"report":{"fcp":942.7999999523163,"ttfb":457.2999999523163,"pageVisibility":"visible","entityId":84067,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"f8219ee6-b683-4240-97b1-111f726a5d99","navigationType":0,"readyForUser":1072.1000001430511,"redirectCount":0,"resourceLoadedEnd":986,"resourceLoadedStart":470.7999999523163,"resourceTiming":[{"duration":30.700000047683716,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":470.7999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":470.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":501.5,"responseStart":0,"secureConnectionStart":0},{"duration":31,"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":471.10000014305115,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":471.10000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":502.10000014305115,"responseStart":0,"secureConnectionStart":0},{"duration":52.60000014305115,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":471.2999999523163,"connectEnd":471.2999999523163,"connectStart":471.2999999523163,"domainLookupEnd":471.2999999523163,"domainLookupStart":471.2999999523163,"fetchStart":471.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":471.2999999523163,"responseEnd":523.9000000953674,"responseStart":523.9000000953674,"secureConnectionStart":471.2999999523163},{"duration":195.30000019073486,"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":472.2999999523163,"connectEnd":472.2999999523163,"connectStart":472.2999999523163,"domainLookupEnd":472.2999999523163,"domainLookupStart":472.2999999523163,"fetchStart":472.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":528.6000001430511,"responseEnd":667.6000001430511,"responseStart":541,"secureConnectionStart":472.2999999523163},{"duration":53.39999985694885,"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":472.40000009536743,"connectEnd":472.40000009536743,"connectStart":472.40000009536743,"domainLookupEnd":472.40000009536743,"domainLookupStart":472.40000009536743,"fetchStart":472.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":472.40000009536743,"responseEnd":525.7999999523163,"responseStart":525.7999999523163,"secureConnectionStart":472.40000009536743},{"duration":83.79999995231628,"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":472.40000009536743,"connectEnd":472.40000009536743,"connectStart":472.40000009536743,"domainLookupEnd":472.40000009536743,"domainLookupStart":472.40000009536743,"fetchStart":472.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":530.4000000953674,"responseEnd":556.2000000476837,"responseStart":554.9000000953674,"secureConnectionStart":472.40000009536743},{"duration":55.09999990463257,"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":472.60000014305115,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":472.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":527.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":85.69999980926514,"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":472.60000014305115,"connectEnd":472.60000014305115,"connectStart":472.60000014305115,"domainLookupEnd":472.60000014305115,"domainLookupStart":472.60000014305115,"fetchStart":472.60000014305115,"redirectEnd":0,"redirectStart":0,"requestStart":529.9000000953674,"responseEnd":558.2999999523163,"responseStart":556.5,"secureConnectionStart":472.60000014305115},{"duration":122.09999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":472.7000000476837,"connectEnd":472.7000000476837,"connectStart":472.7000000476837,"domainLookupEnd":472.7000000476837,"domainLookupStart":472.7000000476837,"fetchStart":472.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":531,"responseEnd":594.7999999523163,"responseStart":593,"secureConnectionStart":472.7000000476837},{"duration":55.39999985694885,"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":472.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":472.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":528.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":85.5,"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":473,"connectEnd":473,"connectStart":473,"domainLookupEnd":473,"domainLookupStart":473,"fetchStart":473,"redirectEnd":0,"redirectStart":0,"requestStart":531.1000001430511,"responseEnd":558.5,"responseStart":557.1000001430511,"secureConnectionStart":473},{"duration":94.5,"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":474.2999999523163,"connectEnd":474.2999999523163,"connectStart":474.2999999523163,"domainLookupEnd":474.2999999523163,"domainLookupStart":474.2999999523163,"fetchStart":474.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":474.2999999523163,"responseEnd":568.7999999523163,"responseStart":568.7999999523163,"secureConnectionStart":474.2999999523163},{"duration":214.29999995231628,"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":474.7000000476837,"connectEnd":474.7000000476837,"connectStart":474.7000000476837,"domainLookupEnd":474.7000000476837,"domainLookupStart":474.7000000476837,"fetchStart":474.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":474.7000000476837,"responseEnd":689,"responseStart":689,"secureConnectionStart":474.7000000476837},{"duration":65.80000019073486,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":696.2999999523163,"connectEnd":696.2999999523163,"connectStart":696.2999999523163,"domainLookupEnd":696.2999999523163,"domainLookupStart":696.2999999523163,"fetchStart":696.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":696.2999999523163,"responseEnd":762.1000001430511,"responseStart":762.1000001430511,"secureConnectionStart":696.2999999523163},{"duration":35.80000019073486,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":933.7999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":933.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":969.6000001430511,"responseStart":0,"secureConnectionStart":0},{"duration":44.59999990463257,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/e65b778d185daf5aee24936755b43da6/_/download/contextbatch/js/browser-metrics-plugin.contrib,-_super,-atl.general/batch.js?agile_global_admin_condition=true&jag=true&slack-enabled=true","startTime":941.4000000953674,"connectEnd":941.4000000953674,"connectStart":941.4000000953674,"domainLookupEnd":941.4000000953674,"domainLookupStart":941.4000000953674,"fetchStart":941.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":941.4000000953674,"responseEnd":986,"responseStart":986,"secureConnectionStart":941.4000000953674}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":51,"responseStart":457,"responseEnd":474,"domLoading":460,"domInteractive":1145,"domContentLoadedEventStart":1145,"domContentLoadedEventEnd":1195,"domComplete":1284,"loadEventStart":1284,"loadEventEnd":1284,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1121.2000000476837},{"name":"bigPipe.sidebar-id.end","time":1122},{"name":"bigPipe.activity-panel-pipe-id.start","time":1122.2000000476837},{"name":"bigPipe.activity-panel-pipe-id.end","time":1123.2000000476837},{"name":"activityTabFullyLoaded","time":1209.5}],"measures":[],"correlationId":"a26b1a6a269c79","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":340,"dbReadsTimeInMs":12,"dbConnsTimeInMs":20,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Monty approved the submitted fix