The following test case produces different result sets for the first and the second SELECT. SELECT is the same, only the value of record_cond_statistics differs.
The same query on 10.0-base or on MariaDB 5.3 or on MySQL 5.6 produces 2 rows, which I assume to be a correct result.
CREATETABLE t1 (i1 INT) ENGINE=MyISAM;
INSERTINTO t1 VALUES (9),(8);
CREATETABLE t2 (id2 INT, i2 INT) ENGINE=MyISAM;
INSERTINTO t2 VALUES (1,5),(2,6);
CREATETABLE t3 (pk3 INTPRIMARYKEY, i3 INT) ENGINE=MyISAM;
INSERTINTO t3 VALUES (1,0),(2,6);
SELECT * FROM t1 AS outer_t1, t2, t3
WHERE pk3 = id2 AND i2 < SOME ( SELECT i1 FROM t1 WHERE i1 < outer_t1.i1 );
SET optimizer_switch='record_cond_statistics=on';
SELECT * FROM t1 AS outer_t1, t2, t3
WHERE pk3 = id2 AND i2 < SOME ( SELECT i1 FROM t1 WHERE i1 < outer_t1.i1 );
Results:
SELECT * FROM t1 AS outer_t1, t2, t3
WHERE pk3 = id2 AND i2 < SOME ( SELECT i1 FROM t1 WHERE i1 < outer_t1.i1 );
i1 id2 i2 pk3 i3
9 1 5 1 0
9 2 6 2 6
SET optimizer_switch='record_cond_statistics=on';
SELECT * FROM t1 AS outer_t1, t2, t3
WHERE pk3 = id2 AND i2 < SOME ( SELECT i1 FROM t1 WHERE i1 < outer_t1.i1 );
i1 id2 i2 pk3 i3
9 1 5 1 0
8 1 5 1 0
9 2 6 2 6
8 2 6 2 6
EXPLAIN with record_cond_statistics=off:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Subqueries: 2; Using join buffer (flat, BNL join)
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2.id2 1 100.00
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.outer_t1.i1' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`outer_t1`.`i1` AS `i1`,`test`.`t2`.`id2` AS `id2`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`pk3` AS `pk3`,`test`.`t3`.`i3` AS `i3` from `test`.`t1` `outer_t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`pk3` = `test`.`t2`.`id2`) and <nop>(<expr_cache><`test`.`t2`.`i2`,`test`.`outer_t1`.`i1`>(<in_optimizer>(`test`.`t2`.`i2`,<exists>(select `test`.`t1`.`i1` from `test`.`t1` where ((`test`.`t1`.`i1` < `test`.`outer_t1`.`i1`) and trigcond(((<cache>(`test`.`t2`.`i2`) < `test`.`t1`.`i1`) or isnull(`test`.`t1`.`i1`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`i1`)))))))
EXPLAIN with record_cond_statistics=on:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY outer_t1 ALL NULL NULL NULL NULL 2 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Subqueries: 2; Using join buffer (flat, BNL join)
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2.id2 1 100.00 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.outer_t1.i1' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`outer_t1`.`i1` AS `i1`,`test`.`t2`.`id2` AS `id2`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`pk3` AS `pk3`,`test`.`t3`.`i3` AS `i3` from `test`.`t1` `outer_t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`pk3` = `test`.`t2`.`id2`) and <nop>(<expr_cache><`test`.`t2`.`i2`,`test`.`outer_t1`.`i1`>(<in_optimizer>(`test`.`t2`.`i2`,<exists>(select `test`.`t1`.`i1` from `test`.`t1` where ((`test`.`t1`.`i1` < `test`.`outer_t1`.`i1`) and trigcond(((<cache>(`test`.`t2`.`i2`) < `test`.`t1`.`i1`) or isnull(`test`.`t1`.`i1`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`i1`)))))))
Attachments
Issue Links
relates to
MDEV-83Cost-based choice for the pushdown of subqueries to joined tables
moved the calculation of the extra cost of subquery predicates during join optimization
into a separate method JOIN::static_pushdown_cost()
fixed small errors in static_pushdown_cost()
added a call to this method into optimize_straight_join, so that SELECT STRAIGHT_JOIN
is optimized in the same way as regular joins
added test cases for SELECT STRAIGHT_JOIN
Fixed bug MDEV-5178 Valgrind warnings (Conditional jump or move depends on uninitialised value) with static_cond_pushdown=on, FROM subquery
Timour Katchaounov (Inactive)
added a comment - Already fixed by the following commit:
revno: 3752
fixes bug: https://mariadb.atlassian.net/browse/MDEV-5178
committer: timour@askmonty.org <timour@askmonty.org>
branch nick: 10.0-md83-4170-static-dyn-push
timestamp: Thu 2013-10-31 16:19:45 +0200
message:
Addressed review comments:
moved the calculation of the extra cost of subquery predicates during join optimization
into a separate method JOIN::static_pushdown_cost()
fixed small errors in static_pushdown_cost()
added a call to this method into optimize_straight_join, so that SELECT STRAIGHT_JOIN
is optimized in the same way as regular joins
added test cases for SELECT STRAIGHT_JOIN
Fixed bug MDEV-5178 Valgrind warnings (Conditional jump or move depends on uninitialised value) with static_cond_pushdown=on, FROM subquery
People
Timour Katchaounov (Inactive)
Elena Stepanova
Votes:
0Vote for this issue
Watchers:
2Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":691.2999999523163,"ttfb":147.19999980926514,"pageVisibility":"visible","entityId":26206,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"8444f88b-8637-4f16-81bc-67abcd391080","navigationType":0,"readyForUser":766.5,"redirectCount":0,"resourceLoadedEnd":811.2999999523163,"resourceLoadedStart":154.59999990463257,"resourceTiming":[{"duration":42,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":154.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":154.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":196.59999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":41.69999980926514,"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":155,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":155,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":196.69999980926514,"responseStart":0,"secureConnectionStart":0},{"duration":60.69999980926514,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":155,"connectEnd":155,"connectStart":155,"domainLookupEnd":155,"domainLookupStart":155,"fetchStart":155,"redirectEnd":0,"redirectStart":0,"requestStart":155,"responseEnd":215.69999980926514,"responseStart":215.69999980926514,"secureConnectionStart":155},{"duration":117,"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":155.19999980926514,"connectEnd":155.19999980926514,"connectStart":155.19999980926514,"domainLookupEnd":155.19999980926514,"domainLookupStart":155.19999980926514,"fetchStart":155.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":155.19999980926514,"responseEnd":272.19999980926514,"responseStart":272.19999980926514,"secureConnectionStart":155.19999980926514},{"duration":113.10000014305115,"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":155.19999980926514,"connectEnd":155.19999980926514,"connectStart":155.19999980926514,"domainLookupEnd":155.19999980926514,"domainLookupStart":155.19999980926514,"fetchStart":155.19999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":155.19999980926514,"responseEnd":268.2999999523163,"responseStart":268.2999999523163,"secureConnectionStart":155.19999980926514},{"duration":117.5,"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":155.29999995231628,"connectEnd":155.29999995231628,"connectStart":155.29999995231628,"domainLookupEnd":155.29999995231628,"domainLookupStart":155.29999995231628,"fetchStart":155.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":155.29999995231628,"responseEnd":272.7999999523163,"responseStart":272.7999999523163,"secureConnectionStart":155.29999995231628},{"duration":117.79999995231628,"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":155.39999985694885,"connectEnd":155.39999985694885,"connectStart":155.39999985694885,"domainLookupEnd":155.39999985694885,"domainLookupStart":155.39999985694885,"fetchStart":155.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":155.39999985694885,"responseEnd":273.19999980926514,"responseStart":273.19999980926514,"secureConnectionStart":155.39999985694885},{"duration":118.20000004768372,"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":155.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":155.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":273.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":118.29999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":155.59999990463257,"connectEnd":155.59999990463257,"connectStart":155.59999990463257,"domainLookupEnd":155.59999990463257,"domainLookupStart":155.59999990463257,"fetchStart":155.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":155.59999990463257,"responseEnd":273.89999985694885,"responseStart":273.89999985694885,"secureConnectionStart":155.59999990463257},{"duration":118.59999990463257,"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":155.79999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":155.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":274.39999985694885,"responseStart":0,"secureConnectionStart":0},{"duration":118.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":156,"connectEnd":156,"connectStart":156,"domainLookupEnd":156,"domainLookupStart":156,"fetchStart":156,"redirectEnd":0,"redirectStart":0,"requestStart":156,"responseEnd":274.5,"responseStart":274.5,"secureConnectionStart":156},{"duration":590.2000000476837,"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":156.79999995231628,"connectEnd":156.79999995231628,"connectStart":156.79999995231628,"domainLookupEnd":156.79999995231628,"domainLookupStart":156.79999995231628,"fetchStart":156.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":156.79999995231628,"responseEnd":747,"responseStart":747,"secureConnectionStart":156.79999995231628},{"duration":586.1999998092651,"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":161.5,"connectEnd":161.5,"connectStart":161.5,"domainLookupEnd":161.5,"domainLookupStart":161.5,"fetchStart":161.5,"redirectEnd":0,"redirectStart":0,"requestStart":161.5,"responseEnd":747.6999998092651,"responseStart":747.6999998092651,"secureConnectionStart":161.5},{"duration":131.90000009536743,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":436.59999990463257,"connectEnd":436.59999990463257,"connectStart":436.59999990463257,"domainLookupEnd":436.59999990463257,"domainLookupStart":436.59999990463257,"fetchStart":436.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":436.59999990463257,"responseEnd":568.5,"responseStart":568.5,"secureConnectionStart":436.59999990463257},{"duration":163.59999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/d5715adaadd168a9002b108b2b039b50-CDN/lu2bu7/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/css/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":642,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":642,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":805.5999999046326,"responseStart":0,"secureConnectionStart":0},{"duration":163.20000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/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","startTime":642.8999998569489,"connectEnd":642.8999998569489,"connectStart":642.8999998569489,"domainLookupEnd":642.8999998569489,"domainLookupStart":642.8999998569489,"fetchStart":642.8999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":642.8999998569489,"responseEnd":806.0999999046326,"responseStart":806.0999999046326,"secureConnectionStart":642.8999998569489},{"duration":168,"initiatorType":"script","name":"https://jira.mariadb.org/s/f51ef5507eea4c158f257c66c93b2a3f-CDN/lu2bu7/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/js/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":643.2999999523163,"connectEnd":643.2999999523163,"connectStart":643.2999999523163,"domainLookupEnd":643.2999999523163,"domainLookupStart":643.2999999523163,"fetchStart":643.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":643.2999999523163,"responseEnd":811.2999999523163,"responseStart":811.2999999523163,"secureConnectionStart":643.2999999523163},{"duration":169.79999995231628,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":675.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":675.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":845.2999999523163,"responseStart":0,"secureConnectionStart":0}],"fetchStart":1,"domainLookupStart":1,"domainLookupEnd":1,"connectStart":1,"connectEnd":1,"requestStart":18,"responseStart":148,"responseEnd":154,"domLoading":153,"domInteractive":840,"domContentLoadedEventStart":840,"domContentLoadedEventEnd":884,"domComplete":1456,"loadEventStart":1456,"loadEventEnd":1457,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":808.5999999046326},{"name":"bigPipe.sidebar-id.end","time":809.3999998569489},{"name":"bigPipe.activity-panel-pipe-id.start","time":809.5},{"name":"bigPipe.activity-panel-pipe-id.end","time":810.5},{"name":"activityTabFullyLoaded","time":898.2999999523163}],"measures":[],"correlationId":"f4e3bf54d29820","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":73,"dbReadsTimeInMs":12,"dbConnsTimeInMs":18,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Major
MDEV-83 Cost-based choice for the pushdown of subqueries to joined tables
Already fixed by the following commit:
revno: 3752
fixes bug: https://mariadb.atlassian.net/browse/MDEV-5178
committer: timour@askmonty.org <timour@askmonty.org>
branch nick: 10.0-md83-4170-static-dyn-push
timestamp: Thu 2013-10-31 16:19:45 +0200
message:
into a separate method JOIN::static_pushdown_cost()
is optimized in the same way as regular joins
MDEV-5178Valgrind warnings (Conditional jump or move depends on uninitialised value) with static_cond_pushdown=on, FROM subquery