Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
25.10.7
-
None
-
None
Description
CREATE TABLE wf_t (g INT, h INT, v INT) ENGINE=Columnstore;
|
INSERT INTO wf_t VALUES (1,10,5),(1,11,3),(1,12,1),(2,10,2),(2,13,9),(3,14,4),(3,11,7); |
SELECT g, h FROM (
|
SELECT g, h, CASE WHEN ROW_NUMBER() OVER (PARTITION BY g ORDER BY v DESC) = 1 THEN 1 ELSE 0 END f |
FROM wf_t) x
|
WHERE f = 1; |
Result with innodb and set columnstore_select_handler = 0;
+------+------+
|
| g | h |
|
+------+------+
|
| 1 | 10 | |
| 2 | 13 | |
| 3 | 11 | |
+------+------+
|
Result with set columnstore_select_handler = 1;
+------+------+
|
| g | h |
|
+------+------+
|
| 1 | 10 | |
| 1 | 11 | |
| 1 | 12 | |
| 2 | 13 | |
| 2 | 10 | |
| 3 | 11 | |
| 3 | 14 | |
+------+------+
|
Workaround: select window functions as plain columns and apply comparisons in the outer WHERE.
SELECT g, h FROM (
|
SELECT g, h, ROW_NUMBER() OVER (PARTITION BY g ORDER BY v DESC) rn
|
FROM wf_t
|
) x WHERE rn = 1; |