Type:
Bug
Priority:
Minor
Resolution:
Duplicate
Affects Version/s:
None
wrong order while using Window functions (RANK(), ROW_NUMBER) inside an IF, ordered also by joined table and st_distance function.
testcase:
{{
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cat` int(11) NOT NULL DEFAULT 0,
`cat_order` int(11) NOT NULL DEFAULT 0,
`distance` int(11) NOT NULL DEFAULT 0,
`title` varchar(50) NOT NULL DEFAULT '',
`address` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `dist` (`distance`),
KEY `cat` (`cat`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `addresses` (
`a_id` int(11) NOT NULL,
`a_type` tinyint(3) unsigned NOT NULL,
`a_address` varchar(90) NOT NULL,
`a_x` double NOT NULL,
`a_y` double NOT NULL,
`a_visible` tinyint(1) NOT NULL DEFAULT 1,
`a_status` tinyint(3) unsigned NOT NULL DEFAULT 0,
`a_city` int(11) NOT NULL DEFAULT 0,
/* `a_point` point NOT NULL,*/
PRIMARY KEY (`a_id`),
UNIQUE KEY `type_address2` (`a_address`,`a_city`,`a_type`),
KEY `xy` (`a_x`,`a_y`),
KEY `id_x_y` (`a_id`,`a_x`,`a_y`),
KEY `city_w_address` (`a_city`,`a_address`,`a_x`,`a_y`),
/SPATIAL KEY `sa_point` (`a_point`), /
KEY `point` (`a_point`(25))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into test
values ('1', '1', '2', '191', 'cat', '57316'),(
'2', '1', '1', '90', 'dog', '57317'),(
'3', '2', '1', '5', 'chair', '57318'),(
'4', '2', '2', '2', 'desk', '57319');
INSERT INTO addresses (`a_id`,`a_type`,`a_address`,`a_x`,`a_y`,`a_visible`,`a_status`,`a_city`) VALUES (57316,1,'רח\' הציונות 78 ,אריאל',32.105,35.19709,1,1,110);
INSERT INTO addresses (`a_id`,`a_type`,`a_address`,`a_x`,`a_y`,`a_visible`,`a_status`,`a_city`) VALUES (57317,1,'דרך בר יהודה 53, נשר, ישראל',32.77686,35.04083,1,1,1651);
INSERT INTO addresses (`a_id`,`a_type`,`a_address`,`a_x`,`a_y`,`a_visible`,`a_status`,`a_city`) VALUES (57318,1,'העמל 37 פתח תקווה',32.096686,34.848931,1,1,1792);
INSERT INTO addresses (`a_id`,`a_type`,`a_address`,`a_x`,`a_y`,`a_visible`,`a_status`,`a_city`) VALUES (57319,1,'תרצה 19 רמת גן',32.071911,34.829069,1,1,1893);
SET @hereami = POINT(35.22245,31.94236); /*בית אל */
/works /
select row_number() over (partition by cat) c, round( ST_Distance(@hereami, point( a_y,a_x) ) * 111195) distance, test.*
from test, addresses
where a_id=address
order by c desc, distance asc
/*wrong order */
select if( row_number() over (partition by cat) =1,1,0) c, round( ST_Distance(@hereami, point( a_y,a_x) ) * 111195) distance, test.*
from test, addresses
where a_id=address
order by c desc, distance asc
;}}
duplicates
MDEV-15212
server crashed, query using ORDER BY (expression with window function)
Closed
{"report":{"fcp":1076.3999999985099,"ttfb":248.60000000149012,"pageVisibility":"visible","entityId":73323,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"7a94d9a7-71b8-4caa-aa0d-ccdfcb946045","navigationType":0,"readyForUser":1170.6000000014901,"redirectCount":0,"resourceLoadedEnd":932.2999999970198,"resourceLoadedStart":254.10000000149012,"resourceTiming":[{"duration":325.29999999701977,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bv2/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":254.10000000149012,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":254.10000000149012,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":579.3999999985099,"responseStart":0,"secureConnectionStart":0},{"duration":325.40000000596046,"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":254.29999999701977,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":254.29999999701977,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":579.7000000029802,"responseStart":0,"secureConnectionStart":0},{"duration":335.29999999701977,"initiatorType":"script","name":"https://jira.mariadb.org/s/e9b27a47da5fb0f74a35acd57e9847fb-CDN/lu2bv2/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":254.5,"connectEnd":254.5,"connectStart":254.5,"domainLookupEnd":254.5,"domainLookupStart":254.5,"fetchStart":254.5,"redirectEnd":0,"redirectStart":0,"requestStart":254.5,"responseEnd":589.7999999970198,"responseStart":589.7999999970198,"secureConnectionStart":254.5},{"duration":437,"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":254.70000000298023,"connectEnd":254.70000000298023,"connectStart":254.70000000298023,"domainLookupEnd":254.70000000298023,"domainLookupStart":254.70000000298023,"fetchStart":254.70000000298023,"redirectEnd":0,"redirectStart":0,"requestStart":254.70000000298023,"responseEnd":691.7000000029802,"responseStart":691.7000000029802,"secureConnectionStart":254.70000000298023},{"duration":440.8999999985099,"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":254.89999999850988,"connectEnd":254.89999999850988,"connectStart":254.89999999850988,"domainLookupEnd":254.89999999850988,"domainLookupStart":254.89999999850988,"fetchStart":254.89999999850988,"redirectEnd":0,"redirectStart":0,"requestStart":254.89999999850988,"responseEnd":695.7999999970198,"responseStart":695.7999999970198,"secureConnectionStart":254.89999999850988},{"duration":441.19999999552965,"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":255.10000000149012,"connectEnd":255.10000000149012,"connectStart":255.10000000149012,"domainLookupEnd":255.10000000149012,"domainLookupStart":255.10000000149012,"fetchStart":255.10000000149012,"redirectEnd":0,"redirectStart":0,"requestStart":255.10000000149012,"responseEnd":696.2999999970198,"responseStart":696.2999999970198,"secureConnectionStart":255.10000000149012},{"duration":441.30000000447035,"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":255.29999999701977,"connectEnd":255.29999999701977,"connectStart":255.29999999701977,"domainLookupEnd":255.29999999701977,"domainLookupStart":255.29999999701977,"fetchStart":255.29999999701977,"redirectEnd":0,"redirectStart":0,"requestStart":255.29999999701977,"responseEnd":696.6000000014901,"responseStart":696.6000000014901,"secureConnectionStart":255.29999999701977},{"duration":485.3999999985099,"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":255.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":255.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":740.8999999985099,"responseStart":0,"secureConnectionStart":0},{"duration":441.3999999985099,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":255.60000000149012,"connectEnd":255.60000000149012,"connectStart":255.60000000149012,"domainLookupEnd":255.60000000149012,"domainLookupStart":255.60000000149012,"fetchStart":255.60000000149012,"redirectEnd":0,"redirectStart":0,"requestStart":255.60000000149012,"responseEnd":697,"responseStart":697,"secureConnectionStart":255.60000000149012},{"duration":485.20000000298023,"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":255.79999999701977,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":255.79999999701977,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":741,"responseStart":0,"secureConnectionStart":0},{"duration":441.6000000014901,"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":256,"connectEnd":256,"connectStart":256,"domainLookupEnd":256,"domainLookupStart":256,"fetchStart":256,"redirectEnd":0,"redirectStart":0,"requestStart":256,"responseEnd":697.6000000014901,"responseStart":697.6000000014901,"secureConnectionStart":256},{"duration":513.8999999985099,"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":268.6000000014901,"connectEnd":268.6000000014901,"connectStart":268.6000000014901,"domainLookupEnd":268.6000000014901,"domainLookupStart":268.6000000014901,"fetchStart":268.6000000014901,"redirectEnd":0,"redirectStart":0,"requestStart":268.6000000014901,"responseEnd":782.5,"responseStart":782.5,"secureConnectionStart":268.6000000014901},{"duration":663.6999999955297,"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":268.6000000014901,"connectEnd":268.6000000014901,"connectStart":268.6000000014901,"domainLookupEnd":268.6000000014901,"domainLookupStart":268.6000000014901,"fetchStart":268.6000000014901,"redirectEnd":0,"redirectStart":0,"requestStart":268.6000000014901,"responseEnd":932.2999999970198,"responseStart":932.2999999970198,"secureConnectionStart":268.6000000014901},{"duration":139.89999999850988,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":753.1000000014901,"connectEnd":753.1000000014901,"connectStart":753.1000000014901,"domainLookupEnd":753.1000000014901,"domainLookupStart":753.1000000014901,"fetchStart":753.1000000014901,"redirectEnd":0,"redirectStart":0,"requestStart":753.1000000014901,"responseEnd":893,"responseStart":893,"secureConnectionStart":753.1000000014901}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":96,"responseStart":249,"responseEnd":260,"domLoading":252,"domInteractive":1285,"domContentLoadedEventStart":1285,"domContentLoadedEventEnd":1331,"domComplete":3125,"loadEventStart":3125,"loadEventEnd":3127,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1213.5},{"name":"bigPipe.sidebar-id.end","time":1214.3999999985099},{"name":"bigPipe.activity-panel-pipe-id.start","time":1214.6000000014901},{"name":"bigPipe.activity-panel-pipe-id.end","time":1218.2999999970198},{"name":"activityTabFullyLoaded","time":1339.3999999985099}],"measures":[],"correlationId":"5434f234dfe117","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":99,"dbReadsTimeInMs":12,"dbConnsTimeInMs":20,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}