Type:
Bug
Priority:
Major
Resolution:
Fixed
Affects Version/s:
None
Component/s:
None
The following query
SELECT * FROM t1 AS alias1, t2 AS alias2
WHERE NOT EXISTS (
SELECT * FROM t1 LEFT OUTER JOIN t3
ON (d = a)
WHERE b <= alias1.b OR e != alias2.c
)
LIMIT_ROWS_EXAMINED 20
on the test data returns two rows, while the same query without LIMIT_ROWS_EXAMINED clause returns an empty set.
bzr version-info
revision-id: timour@askmonty.org-20120213145248-tlvsd794o83odf2r
date: 2012-02-13 16:52:48 +0200
build-date: 2012-02-21 07:04:39 +0400
revno: 3417
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alias1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY alias2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1276 Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.alias2.c' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias2`.`c` AS `c` from `test`.`t1` `alias1` join `test`.`t2` `alias2` where (not(<expr_cache><`test`.`alias1`.`b`,`test`.`alias2`.`c`>(exists(select 1 from `test`.`t1` left join `test`.`t3` on((`test`.`t3`.`d` = `test`.`t1`.`a`)) where ((`test`.`t1`.`b` <= `test`.`alias1`.`b`) or (`test`.`t3`.`e` <> `test`.`alias2`.`c`))))))
optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=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
Test case:
CREATE TABLE t1 ( a INT, b INT );
INSERT INTO t1 VALUES
(3911,17),(3847,33),(3857,26);
CREATE TABLE t2 ( c VARCHAR(16) );
INSERT INTO t2 VALUES
('English'),('French'),('German');
CREATE TABLE t3 ( d INT, e VARCHAR(32) );
INSERT INTO t3 VALUES
(3813,'United States'),(3814,'United States');
SELECT * FROM t1 AS alias1, t2 AS alias2
WHERE NOT EXISTS (
SELECT * FROM t1 LEFT OUTER JOIN t3
ON (d = a)
WHERE b <= alias1.b OR e != alias2.c
);
SELECT * FROM t1 AS alias1, t2 AS alias2
WHERE NOT EXISTS (
SELECT * FROM t1 LEFT OUTER JOIN t3
ON (d = a)
WHERE b <= alias1.b OR e != alias2.c
)
LIMIT_ROWS_EXAMINED 20;
Result:
SELECT * FROM t1 AS alias1, t2 AS alias2
WHERE NOT EXISTS (
SELECT * FROM t1 LEFT OUTER JOIN t3
ON (d = a)
WHERE b <= alias1.b OR e != alias2.c
);
a b c
SELECT * FROM t1 AS alias1, t2 AS alias2
WHERE NOT EXISTS (
SELECT * FROM t1 LEFT OUTER JOIN t3
ON (d = a)
WHERE b <= alias1.b OR e != alias2.c
)
LIMIT_ROWS_EXAMINED 20;
a b c
3847 33 English
3857 26 English
Warnings:
Warning 1930 Query execution was interrupted. The query examined at least 25 rows, which exceeds LIMIT_ROWS_EXAMINED. The query result may be incomplete.
relates to
MDEV-28
LIMIT ROWS EXAMINED clause to limit the number of rows examined during SELECT processing
Closed
There are no comments yet on this issue.
{"report":{"fcp":1523.699999988079,"ttfb":524.8999999761581,"pageVisibility":"visible","entityId":10804,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"2f92daca-d07e-45e4-88b4-715e78c396cd","navigationType":0,"readyForUser":1608.3999999761581,"redirectCount":0,"resourceLoadedEnd":2019,"resourceLoadedStart":532.6999999880791,"resourceTiming":[{"duration":442.80000001192093,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":532.6999999880791,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":532.6999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":975.5,"responseStart":0,"secureConnectionStart":0},{"duration":442.7999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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":533.1000000238419,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":533.1000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":975.8999999761581,"responseStart":0,"secureConnectionStart":0},{"duration":517,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":533.1999999880791,"connectEnd":533.1999999880791,"connectStart":533.1999999880791,"domainLookupEnd":533.1999999880791,"domainLookupStart":533.1999999880791,"fetchStart":533.1999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":533.1999999880791,"responseEnd":1050.199999988079,"responseStart":1050.1000000238419,"secureConnectionStart":533.1999999880791},{"duration":623.3000000119209,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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":533.3999999761581,"connectEnd":533.3999999761581,"connectStart":533.3999999761581,"domainLookupEnd":533.3999999761581,"domainLookupStart":533.3999999761581,"fetchStart":533.3999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":533.3999999761581,"responseEnd":1156.699999988079,"responseStart":1156.699999988079,"secureConnectionStart":533.3999999761581},{"duration":518.3000000119209,"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":534,"connectEnd":534,"connectStart":534,"domainLookupEnd":534,"domainLookupStart":534,"fetchStart":534,"redirectEnd":0,"redirectStart":0,"requestStart":534,"responseEnd":1052.300000011921,"responseStart":1052.300000011921,"secureConnectionStart":534},{"duration":518.5,"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":534.3000000119209,"connectEnd":534.3000000119209,"connectStart":534.3000000119209,"domainLookupEnd":534.3000000119209,"domainLookupStart":534.3000000119209,"fetchStart":534.3000000119209,"redirectEnd":0,"redirectStart":0,"requestStart":534.3000000119209,"responseEnd":1052.800000011921,"responseStart":1052.800000011921,"secureConnectionStart":534.3000000119209},{"duration":626.8999999761581,"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":534.5,"connectEnd":534.5,"connectStart":534.5,"domainLookupEnd":534.5,"domainLookupStart":534.5,"fetchStart":534.5,"redirectEnd":0,"redirectStart":0,"requestStart":534.5,"responseEnd":1161.3999999761581,"responseStart":1161.3999999761581,"secureConnectionStart":534.5},{"duration":664.1999999880791,"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":534.6000000238419,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":534.6000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1198.800000011921,"responseStart":0,"secureConnectionStart":0},{"duration":627.4000000357628,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":534.6999999880791,"connectEnd":534.6999999880791,"connectStart":534.6999999880791,"domainLookupEnd":534.6999999880791,"domainLookupStart":534.6999999880791,"fetchStart":534.6999999880791,"redirectEnd":0,"redirectStart":0,"requestStart":534.6999999880791,"responseEnd":1162.1000000238419,"responseStart":1162.1000000238419,"secureConnectionStart":534.6999999880791},{"duration":663.8999999761581,"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":535,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":535,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1198.8999999761581,"responseStart":0,"secureConnectionStart":0},{"duration":627.5999999642372,"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":535.1000000238419,"connectEnd":535.1000000238419,"connectStart":535.1000000238419,"domainLookupEnd":535.1000000238419,"domainLookupStart":535.1000000238419,"fetchStart":535.1000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":535.1000000238419,"responseEnd":1162.699999988079,"responseStart":1162.699999988079,"secureConnectionStart":535.1000000238419},{"duration":1470.8999999761581,"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":545.8000000119209,"connectEnd":545.8000000119209,"connectStart":545.8000000119209,"domainLookupEnd":545.8000000119209,"domainLookupStart":545.8000000119209,"fetchStart":545.8000000119209,"redirectEnd":0,"redirectStart":0,"requestStart":545.8000000119209,"responseEnd":2016.699999988079,"responseStart":2016.699999988079,"secureConnectionStart":545.8000000119209},{"duration":1473.1000000238419,"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":545.8999999761581,"connectEnd":545.8999999761581,"connectStart":545.8999999761581,"domainLookupEnd":545.8999999761581,"domainLookupStart":545.8999999761581,"fetchStart":545.8999999761581,"redirectEnd":0,"redirectStart":0,"requestStart":545.8999999761581,"responseEnd":2019,"responseStart":2019,"secureConnectionStart":545.8999999761581},{"duration":596.3999999761581,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1211.5,"connectEnd":1211.5,"connectStart":1211.5,"domainLookupEnd":1211.5,"domainLookupStart":1211.5,"fetchStart":1211.5,"redirectEnd":0,"redirectStart":0,"requestStart":1211.5,"responseEnd":1807.8999999761581,"responseStart":1807.8999999761581,"secureConnectionStart":1211.5},{"duration":514.6000000238419,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1517,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1517,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":2031.6000000238419,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":357,"responseStart":525,"responseEnd":545,"domLoading":529,"domInteractive":2033,"domContentLoadedEventStart":2033,"domContentLoadedEventEnd":2074,"domComplete":2754,"loadEventStart":2754,"loadEventEnd":2755,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2022.1000000238419},{"name":"bigPipe.sidebar-id.end","time":2023},{"name":"bigPipe.activity-panel-pipe-id.start","time":2023.1000000238419},{"name":"bigPipe.activity-panel-pipe-id.end","time":2023.6000000238419},{"name":"activityTabFullyLoaded","time":2080.699999988079}],"measures":[],"correlationId":"20e28325744b7d","effectiveType":"4g","downlink":9.9,"rtt":0,"serverDuration":96,"dbReadsTimeInMs":12,"dbConnsTimeInMs":20,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}