You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
❯ select rank() over () from (select 1 a union all select 2 a) q;
Arrow error: Invalid argument error: number of columns(2) must match number of fields(1) in schema
❯ select rank() over (order by 1) from (select 1 a union all select 2 a) q;
+----------------------------------------------------------------------------------------+
| RANK() ORDER BY [q.a ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
+----------------------------------------------------------------------------------------+
| 1 |
| 2 |
+----------------------------------------------------------------------------------------+
DataFusion CLI v33.0.0
❯ select a,
rank() over (partition by 10, a, 'a', null order by 20, a, 10, 'a', null) rnk,
row_number() over (partition by 10, a, 'a', null order by 20, a, 10, 'a', null) rn
from (select 2 a union all select 1 a) q;
+---+-----+----+
| a | rnk | rn |
+---+-----+----+
| 2 | 1 | 1 |
| 1 | 1 | 1 |
+---+-----+----+
2 rows in set. Query took 0.010 seconds.
Restart cli
DataFusion CLI v33.0.0
❯ select a,
rank() over (partition by 10, a, 'a', null order by 20, a, 10, 'a', null) rnk,
row_number() over (partition by 10, a, 'a', null order by 20, a, 10, 'a', null) rn
from (select 2 a union all select 1 a) q;
+---+-----+----+
| a | rnk | rn |
+---+-----+----+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
+---+-----+----+
2 rows in set. Query took 0.018 seconds.
It isn't clear to me that the order of the result from that query is well defined (as in I think it may not be a bug that the output is unstable)
Specifically:
ORDER BY 20, a, 10, 'a', null can produce any output order (as 20 is a constant)
PARTITION BY 10, a, 'a', null puts all values in the same partition
(as an aside these queries look like they are generated by a sql generator, are you testing out DataFusion with such a system?)
you are right @alamb , I was confused that I wasnt able to have the same behavior with PG, but I managed to get the same in duck DB, and the explanation sounds very reasonable for me.
How ever the main purpose of this ticket is still valid.
Activity
[-]Wrong behavior for `RANK over()`[/-][+][BUG] Wrong behavior for `RANK over()`[/+]comphead commentedon Dec 3, 2023
Also unstable behavior
alamb commentedon Dec 3, 2023
It isn't clear to me that the order of the result from that query is well defined (as in I think it may not be a bug that the output is unstable)
Specifically:
20, a, 10, 'a', nullcan produce any output order (as20is a constant)10, a, 'a', nullputs all values in the same partition(as an aside these queries look like they are generated by a sql generator, are you testing out DataFusion with such a system?)
comphead commentedon Dec 3, 2023
you are right @alamb , I was confused that I wasnt able to have the same behavior with PG, but I managed to get the same in duck DB, and the explanation sounds very reasonable for me.
How ever the main purpose of this ticket is still valid.