Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
None
Description
Consider an example:
create table t1 (
|
a int,
|
b int,
|
c int
|
);
|
|
|
insert into t1 values
|
(10, 1, 1),
|
(10, 3, 10),
|
(10, 1, 10),
|
(10, 3, 100),
|
(10, 5, 1000),
|
(10, 1, 100);
|
explain format=json
|
select
|
a,b,c,
|
row_number() over (partition by a),
|
row_number() over (partition by a, b)
|
from t1;
|
The query plan is incorrect. We can't just sort by "a":
"query_block": {
|
"select_id": 1,
|
"window_functions_computation": {
|
"sorts": {
|
"filesort": {
|
"sort_key": "t1.a"
|
}
|
},
|
"temporary_table": {
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"rows": 6,
|
"filtered": 100
|
}
|
}
|
}
|
}
|
Attachments
Activity
Sergey,
It means only that you have to sort by (a,b). Choose the correct order list.
This bug apparently was fixed some time ago in 10.2
I added the test case for it into win.test.
And running the SELECT produces a wrong result (added "ORDER BY b" to the query so it's easier to understand):
MariaDB [j81]> select a,b,c, row_number() over (partition by a), row_number() over (partition by a, b) from t1 order by b;+------+------+------+------------------------------------+---------------------------------------+| a | b | c | row_number() over (partition by a) | row_number() over (partition by a, b) |+------+------+------+------------------------------------+---------------------------------------+| 10 | 1 | 10 | 1 | 1 || 10 | 1 | 1 | 2 | 2 || 10 | 1 | 10 | 6 | 1 || 10 | 1 | 100 | 7 | 2 || 10 | 1 | 1 | 10 | 1 || 10 | 3 | 100 | 3 | 1 || 10 | 3 | 10 | 4 | 2 || 10 | 3 | 100 | 8 | 1 || 10 | 3 | 10 | 11 | 1 || 10 | 5 | 1000 | 5 | 1 || 10 | 5 | 1000 | 9 | 1 |+------+------+------+------------------------------------+---------------------------------------+