Consider the query where the derived table is used in the IN subquery.
SELECT * FROM t1
|
WHERE t1.a>1 AND
|
(t1.a,t1.b,t1.c) IN
|
(
|
SELECT d_tab.e,d_tab.f,d_tab.max_g
|
FROM (
|
SELECT t2.e, t2.f, MAX(t2.g) AS max_g
|
FROM t2
|
GROUP BY t2.e
|
HAVING max_g>25
|
) as d_tab
|
WHERE d_tab.e<5
|
)
|
;
|
There the condition (d_tab.e<5) that is used in the WHERE clause of the IN subquery
should be pushed down into the WHERE clause of the derived table but the pushdown
isn't done. Explain in json format for this query:
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "<subquery2>",
|
"access_type": "ALL",
|
"possible_keys": ["distinct_key"],
|
"rows": 12,
|
"filtered": 100,
|
"materialized": {
|
"unique": 1,
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "<derived3>",
|
"access_type": "ALL",
|
"rows": 12,
|
"filtered": 100,
|
"attached_condition": "d_tab.e > 1 and d_tab.e < 5",
|
"materialized": {
|
"query_block": {
|
"select_id": 3,
|
"having_condition": "max_g > 25",
|
"filesort": {
|
"sort_key": "t2.e",
|
"temporary_table": {
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"rows": 12,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 15,
|
"filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNL",
|
"attached_condition": "t1.a = d_tab.e and t1.b = d_tab.f and t1.c = d_tab.max_g"
|
}
|
}
|
} |
|
Pushdown also isn't down when the derived table is used in the materialized IN subquery that is converted to semijoin:
SELECT * FROM t1
|
WHERE t1.a>1 AND
|
(t1.a,t1.b,t1.c) IN
|
(
|
SELECT d_tab.e,MAX(d_tab.f),d_tab.max_g
|
FROM (
|
SELECT t2.e, t2.f, MAX(t2.g) AS max_g
|
FROM t2
|
GROUP BY t2.e
|
HAVING max_g>25
|
) as d_tab
|
WHERE d_tab.e<5
|
GROUP BY d_tab.e
|
)
|
;
|
Explain in json format for this query:
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 15,
|
"filtered": 100,
|
"attached_condition": "t1.a > 1 and t1.a is not null and t1.b is not null and t1.c is not null"
|
},
|
"table": {
|
"table_name": "<subquery2>",
|
"access_type": "eq_ref",
|
"possible_keys": ["distinct_key"],
|
"key": "distinct_key",
|
"key_length": "12",
|
"used_key_parts": ["e", "MAX(d_tab.f)", "max_g"],
|
"ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
|
"rows": 1,
|
"filtered": 100,
|
"materialized": {
|
"unique": 1,
|
"query_block": {
|
"select_id": 2,
|
"temporary_table": {
|
"table": {
|
"table_name": "<derived3>",
|
"access_type": "ALL",
|
"rows": 12,
|
"filtered": 100,
|
"attached_condition": "d_tab.e < 5",
|
"materialized": {
|
"query_block": {
|
"select_id": 3,
|
"having_condition": "max_g > 25",
|
"filesort": {
|
"sort_key": "t2.e",
|
"temporary_table": {
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"rows": 12,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
} |
|
{"report":{"fcp":1466,"ttfb":396.19999999995343,"pageVisibility":"visible","entityId":67218,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"0a0b7e07-dd0f-4b65-8903-8ab311f68423","navigationType":0,"readyForUser":1566.2999999999302,"redirectCount":0,"resourceLoadedEnd":2353.5,"resourceLoadedStart":401.0999999999767,"resourceTiming":[{"duration":610.9000000000233,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bv2/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":401.0999999999767,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":401.0999999999767,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1012,"responseStart":0,"secureConnectionStart":0},{"duration":611,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bv2/820016/12ta74/2380add21a9a1006587582385952de73/_/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":401.39999999990687,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":401.39999999990687,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1012.3999999999069,"responseStart":0,"secureConnectionStart":0},{"duration":619.4000000000233,"initiatorType":"script","name":"https://jira.mariadb.org/s/e9b27a47da5fb0f74a35acd57e9847fb-CDN/lu2bv2/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":401.5999999999767,"connectEnd":401.5999999999767,"connectStart":401.5999999999767,"domainLookupEnd":401.5999999999767,"domainLookupStart":401.5999999999767,"fetchStart":401.5999999999767,"redirectEnd":0,"redirectStart":0,"requestStart":401.5999999999767,"responseEnd":1021,"responseStart":1021,"secureConnectionStart":401.5999999999767},{"duration":660.2000000000698,"initiatorType":"script","name":"https://jira.mariadb.org/s/c32eb0da7ad9831253f8397e6cc26afd-CDN/lu2bv2/820016/12ta74/2380add21a9a1006587582385952de73/_/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":401.79999999993015,"connectEnd":401.79999999993015,"connectStart":401.79999999993015,"domainLookupEnd":401.79999999993015,"domainLookupStart":401.79999999993015,"fetchStart":401.79999999993015,"redirectEnd":0,"redirectStart":0,"requestStart":401.79999999993015,"responseEnd":1062,"responseStart":1062,"secureConnectionStart":401.79999999993015},{"duration":663.8000000000466,"initiatorType":"script","name":"https://jira.mariadb.org/s/bc0bcb146314416123c992714ee00ff7-CDN/lu2bv2/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":401.89999999990687,"connectEnd":401.89999999990687,"connectStart":401.89999999990687,"domainLookupEnd":401.89999999990687,"domainLookupStart":401.89999999990687,"fetchStart":401.89999999990687,"redirectEnd":0,"redirectStart":0,"requestStart":401.89999999990687,"responseEnd":1065.6999999999534,"responseStart":1065.6999999999534,"secureConnectionStart":401.89999999990687},{"duration":664.1999999999534,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":402.0999999999767,"connectEnd":402.0999999999767,"connectStart":402.0999999999767,"domainLookupEnd":402.0999999999767,"domainLookupStart":402.0999999999767,"fetchStart":402.0999999999767,"redirectEnd":0,"redirectStart":0,"requestStart":402.0999999999767,"responseEnd":1066.2999999999302,"responseStart":1066.2999999999302,"secureConnectionStart":402.0999999999767},{"duration":664.4000000000233,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":402.39999999990687,"connectEnd":402.39999999990687,"connectStart":402.39999999990687,"domainLookupEnd":402.39999999990687,"domainLookupStart":402.39999999990687,"fetchStart":402.39999999990687,"redirectEnd":0,"redirectStart":0,"requestStart":402.39999999990687,"responseEnd":1066.7999999999302,"responseStart":1066.7999999999302,"secureConnectionStart":402.39999999990687},{"duration":759.1999999999534,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bv2/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":402.5999999999767,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":402.5999999999767,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1161.7999999999302,"responseStart":0,"secureConnectionStart":0},{"duration":664.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":402.69999999995343,"connectEnd":402.69999999995343,"connectStart":402.69999999995343,"domainLookupEnd":402.69999999995343,"domainLookupStart":402.69999999995343,"fetchStart":402.69999999995343,"redirectEnd":0,"redirectStart":0,"requestStart":402.69999999995343,"responseEnd":1067.1999999999534,"responseStart":1067.1999999999534,"secureConnectionStart":402.69999999995343},{"duration":759.2000000000698,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bv2/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":402.79999999993015,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":402.79999999993015,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1162,"responseStart":0,"secureConnectionStart":0},{"duration":665.0999999999767,"initiatorType":"script","name":"https://jira.mariadb.org/s/719848dd97ebe0663199f49a3936487a-CDN/lu2bv2/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":403.0999999999767,"connectEnd":403.0999999999767,"connectStart":403.0999999999767,"domainLookupEnd":403.0999999999767,"domainLookupStart":403.0999999999767,"fetchStart":403.0999999999767,"redirectEnd":0,"redirectStart":0,"requestStart":403.0999999999767,"responseEnd":1068.1999999999534,"responseStart":1068.1999999999534,"secureConnectionStart":403.0999999999767},{"duration":1041.0999999999767,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":408.19999999995343,"connectEnd":408.19999999995343,"connectStart":408.19999999995343,"domainLookupEnd":408.19999999995343,"domainLookupStart":408.19999999995343,"fetchStart":408.19999999995343,"redirectEnd":0,"redirectStart":0,"requestStart":408.19999999995343,"responseEnd":1449.2999999999302,"responseStart":1449.2999999999302,"secureConnectionStart":408.19999999995343},{"duration":1945.2000000000698,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bv2/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":408.29999999993015,"connectEnd":408.29999999993015,"connectStart":408.29999999993015,"domainLookupEnd":408.29999999993015,"domainLookupStart":408.29999999993015,"fetchStart":408.29999999993015,"redirectEnd":0,"redirectStart":0,"requestStart":408.29999999993015,"responseEnd":2353.5,"responseStart":2353.5,"secureConnectionStart":408.29999999993015},{"duration":277.4000000000233,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1172.7999999999302,"connectEnd":1172.7999999999302,"connectStart":1172.7999999999302,"domainLookupEnd":1172.7999999999302,"domainLookupStart":1172.7999999999302,"fetchStart":1172.7999999999302,"redirectEnd":0,"redirectStart":0,"requestStart":1172.7999999999302,"responseEnd":1450.1999999999534,"responseStart":1450.1999999999534,"secureConnectionStart":1172.7999999999302},{"duration":957.6999999999534,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1459.6999999999534,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1459.6999999999534,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2417.399999999907,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":173,"responseStart":396,"responseEnd":400,"domLoading":399,"domInteractive":2371,"domContentLoadedEventStart":2371,"domContentLoadedEventEnd":2414,"domComplete":3796,"loadEventStart":3796,"loadEventEnd":3798,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2355.1999999999534},{"name":"bigPipe.sidebar-id.end","time":2356},{"name":"bigPipe.activity-panel-pipe-id.start","time":2356.0999999999767},{"name":"bigPipe.activity-panel-pipe-id.end","time":2358.29999999993},{"name":"activityTabFullyLoaded","time":2421.1999999999534}],"measures":[],"correlationId":"d703772e92d0ff","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":98,"dbReadsTimeInMs":11,"dbConnsTimeInMs":19,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Major