Details
-
Technical task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2(EOL)
-
None
Description
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.
Attachments
Issue Links
- relates to
-
MDEV-12743 Wrong results with ROW_NUMBER OVER
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| 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. |
| Affects Version/s | 10.2.5 [ 22117 ] | |
| Issue Type | Bug [ 1 ] | Task [ 3 ] |
| Assignee | Rasmus Johansson [ ratzpo ] |
| Parent |
|
|
| Issue Type | Task [ 3 ] | Technical task [ 7 ] |
| Affects Version/s | 10.2 [ 14601 ] |
| Assignee | Rasmus Johansson [ ratzpo ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Fix Version/s | 10.2 [ 14601 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Reporter | Will Fong [ wfong ] | Chris Calender [ ccalender ] |
| Summary | FR: Oracle/DB2 Compatibility Implicit Ordering for ROW_NUMBER OVER | Oracle/DB2 Compatibility Implicit Ordering for ROW_NUMBER OVER |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| 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 ] |
| Support case ID | 14189 | 14189 not-26307 |
| NRE Projects | AC-2610/TDS-2 |
| Workflow | MariaDB v3 [ 80726 ] | MariaDB v4 [ 152144 ] |
| Zendesk Related Tickets | 118324 |