Feature request for Oracle/DB2 migration compatibility:
We are using the window function "ROW_NUMBER() OVER()" to generate the row sequence number required for pagination across the pages. For example:
SELECT ROW_NUMBER() OVER() as RN, COL1, COL2 FROM MYTABLE;
Since there is no expression given in the OVER() clause, DB2 will just assign sequential numbers to the output. The column RN returned from the query will have numbers in perfect order.
However, MariaDB does not guarantee the numbers in order without an explicit ORDER BY.
This will require query re-writing in order to migrate.
relates to
MDEV-12743
Wrong results with ROW_NUMBER OVER
Closed
Sergei Petrunia
made changes -
2017-05-11 09:18
Description
Feature request for Oracle/DB2 migration compatibility:
We are using the window function "ROW_NUMBER() ORDER()" to generate the row sequence number required for pagination across the pages. For example:
{code}
SELECT ROW_NUMBER() OVER() as RN, COL1, COL2 FROM MYTABLE;
{code}
Since there is no expression given in the OVER() clause, DB2 will just assign sequential numbers to the output. The column RN returned from the query will have numbers in perfect order.
However, MariaDB does not guarantee the numbers in order without an explicit ORDER BY.
This will require query re-writing in order to migrate.
Feature request for Oracle/DB2 migration compatibility:
We are using the window function "ROW_NUMBER() OVER()" to generate the row sequence number required for pagination across the pages. For example:
{code}
SELECT ROW_NUMBER() OVER() as RN, COL1, COL2 FROM MYTABLE;
{code}
Since there is no expression given in the OVER() clause, DB2 will just assign sequential numbers to the output. The column RN returned from the query will have numbers in perfect order.
However, MariaDB does not guarantee the numbers in order without an explicit ORDER BY.
This will require query re-writing in order to migrate.
Elena Stepanova
made changes -
2017-05-11 10:55
Affects Version/s
10.2.5
[ 22117
]
Issue Type
Bug
[ 1
]
Task
[ 3
]
Elena Stepanova
made changes -
2017-05-11 10:56
Assignee
Rasmus Johansson
[ ratzpo
]
Sergei Golubchik
made changes -
2017-10-03 16:36
Affects Version/s
10.2
[ 14601
]
Sergei Golubchik
made changes -
2017-10-04 13:56
Assignee
Rasmus Johansson
[ ratzpo
]
Vicentiu Ciorbaru
[ cvicentiu
]
Julien Fritsch
made changes -
2018-03-14 09:17
Status
Open
[ 1
]
In Progress
[ 3
]
Julien Fritsch
made changes -
2018-03-21 15:46
Reporter
Will Fong
[ wfong
]
Chris Calender
[ ccalender
]
Ralf Gebhardt
made changes -
2018-06-20 08:24
Summary
FR: Oracle/DB2 Compatibility Implicit Ordering for ROW_NUMBER OVER
Oracle/DB2 Compatibility Implicit Ordering for ROW_NUMBER OVER
Julien Fritsch
made changes -
2018-10-30 17:59
Priority
Major
[ 3
]
Critical
[ 2
]
Vicențiu Ciorbaru
made changes -
2018-11-01 19:17
Component/s
Optimizer - Window functions
[ 13502
]
Fix Version/s
10.3.11
[ 23141
]
Fix Version/s
10.2.19
[ 23207
]
Fix Version/s
10.2
[ 14601
]
Resolution
Fixed
[ 1
]
Status
In Progress
[ 3
]
Closed
[ 6
]
Ralf Gebhardt
made changes -
2019-02-14 09:56
Support case ID
14189
14189 not-26307
Ralf Gebhardt
made changes -
2019-02-14 10:03
NRE Projects
AC-2610/TDS-2
Sergei Golubchik
made changes -
2021-12-06 21:44
Workflow
MariaDB v3
[ 80726
]
MariaDB v4
[ 152144
]
{"report":{"fcp":1293.8999998569489,"ttfb":308.2000000476837,"pageVisibility":"visible","entityId":61314,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":32,"apdex":0.5,"journeyId":"e86961ed-183c-498a-94c6-990517c52d76","navigationType":0,"readyForUser":1534.5999999046326,"redirectCount":0,"resourceLoadedEnd":1594.8999998569489,"resourceLoadedStart":318.39999985694885,"resourceTiming":[{"duration":333.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":318.39999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":318.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":652.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":333.7999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/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&whisper-enabled=true","startTime":318.7999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":318.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":652.5999999046326,"responseStart":0,"secureConnectionStart":0},{"duration":344.7999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":319,"connectEnd":319,"connectStart":319,"domainLookupEnd":319,"domainLookupStart":319,"fetchStart":319,"redirectEnd":0,"redirectStart":0,"requestStart":319,"responseEnd":663.7999999523163,"responseStart":663.7999999523163,"secureConnectionStart":319},{"duration":464.60000014305115,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/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&whisper-enabled=true","startTime":319.89999985694885,"connectEnd":319.89999985694885,"connectStart":319.89999985694885,"domainLookupEnd":319.89999985694885,"domainLookupStart":319.89999985694885,"fetchStart":319.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":319.89999985694885,"responseEnd":784.5,"responseStart":784.5,"secureConnectionStart":319.89999985694885},{"duration":478.09999990463257,"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":320,"connectEnd":320,"connectStart":320,"domainLookupEnd":320,"domainLookupStart":320,"fetchStart":320,"redirectEnd":0,"redirectStart":0,"requestStart":320,"responseEnd":798.0999999046326,"responseStart":798.0999999046326,"secureConnectionStart":320},{"duration":478.7000000476837,"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":320.09999990463257,"connectEnd":320.09999990463257,"connectStart":320.09999990463257,"domainLookupEnd":320.09999990463257,"domainLookupStart":320.09999990463257,"fetchStart":320.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":320.09999990463257,"responseEnd":798.7999999523163,"responseStart":798.7999999523163,"secureConnectionStart":320.09999990463257},{"duration":478.90000009536743,"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":320.39999985694885,"connectEnd":320.39999985694885,"connectStart":320.39999985694885,"domainLookupEnd":320.39999985694885,"domainLookupStart":320.39999985694885,"fetchStart":320.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":320.39999985694885,"responseEnd":799.2999999523163,"responseStart":799.2999999523163,"secureConnectionStart":320.39999985694885},{"duration":560,"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":320.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":320.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":880.5,"responseStart":0,"secureConnectionStart":0},{"duration":479.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":320.7000000476837,"connectEnd":320.7000000476837,"connectStart":320.7000000476837,"domainLookupEnd":320.7000000476837,"domainLookupStart":320.7000000476837,"fetchStart":320.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":320.7000000476837,"responseEnd":800,"responseStart":800,"secureConnectionStart":320.7000000476837},{"duration":559.9000000953674,"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":320.89999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":320.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":880.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":479.89999985694885,"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":321,"connectEnd":321,"connectStart":321,"domainLookupEnd":321,"domainLookupStart":321,"fetchStart":321,"redirectEnd":0,"redirectStart":0,"requestStart":321,"responseEnd":800.8999998569489,"responseStart":800.8999998569489,"secureConnectionStart":321},{"duration":905.7000000476837,"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":321.89999985694885,"connectEnd":321.89999985694885,"connectStart":321.89999985694885,"domainLookupEnd":321.89999985694885,"domainLookupStart":321.89999985694885,"fetchStart":321.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":321.89999985694885,"responseEnd":1227.5999999046326,"responseStart":1227.5999999046326,"secureConnectionStart":321.89999985694885},{"duration":1101.7000000476837,"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":332.59999990463257,"connectEnd":332.59999990463257,"connectStart":332.59999990463257,"domainLookupEnd":332.59999990463257,"domainLookupStart":332.59999990463257,"fetchStart":332.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":332.59999990463257,"responseEnd":1434.2999999523163,"responseStart":1434.2999999523163,"secureConnectionStart":332.59999990463257},{"duration":295,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":933.7000000476837,"connectEnd":933.7000000476837,"connectStart":933.7000000476837,"domainLookupEnd":933.7000000476837,"domainLookupStart":933.7000000476837,"fetchStart":933.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":933.7000000476837,"responseEnd":1228.7000000476837,"responseStart":1228.7000000476837,"secureConnectionStart":933.7000000476837},{"duration":321.2999999523163,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1282.0999999046326,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1282.0999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1603.3999998569489,"responseStart":0,"secureConnectionStart":0},{"duration":281.59999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/d5715adaadd168a9002b108b2b039b50-CDN/lu2cib/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&whisper-enabled=true","startTime":1313.2999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1313.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1594.8999998569489,"responseStart":0,"secureConnectionStart":0},{"duration":172.29999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/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&whisper-enabled=true","startTime":1315,"connectEnd":1315,"connectStart":1315,"domainLookupEnd":1315,"domainLookupStart":1315,"fetchStart":1315,"redirectEnd":0,"redirectStart":0,"requestStart":1315,"responseEnd":1487.2999999523163,"responseStart":1487.2999999523163,"secureConnectionStart":1315},{"duration":175.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/097ae97cb8fbec7d6ea4bbb1f26955b9-CDN/lu2cib/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&whisper-enabled=true","startTime":1316,"connectEnd":1316,"connectStart":1316,"domainLookupEnd":1316,"domainLookupStart":1316,"fetchStart":1316,"redirectEnd":0,"redirectStart":0,"requestStart":1316,"responseEnd":1491.7000000476837,"responseStart":1491.7000000476837,"secureConnectionStart":1316}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":96,"responseStart":308,"responseEnd":329,"domLoading":312,"domInteractive":1672,"domContentLoadedEventStart":1672,"domContentLoadedEventEnd":1742,"domComplete":2510,"loadEventStart":2510,"loadEventEnd":2511,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1616.8999998569489},{"name":"bigPipe.sidebar-id.end","time":1617.7000000476837},{"name":"bigPipe.activity-panel-pipe-id.start","time":1617.7999999523163},{"name":"bigPipe.activity-panel-pipe-id.end","time":1622.8999998569489},{"name":"activityTabFullyLoaded","time":1822.0999999046326}],"measures":[],"correlationId":"b8d430bb3ae5f4","effectiveType":"4g","downlink":9.4,"rtt":0,"serverDuration":153,"dbReadsTimeInMs":34,"dbConnsTimeInMs":44,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
(I assume "ROW_NUMBER() ORDER()" had a typo, changed it to be "ROW_NUMBER() OVER()" )