Skip to content

[BUG] Wrong behavior for RANK over() #8403

Closed

Description

@comphead

Describe the bug

When working on #8386

RANK function gives wrong result or fails

To Reproduce

❯ 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                                                                                      |
+----------------------------------------------------------------------------------------+

Expected behavior

It has to be

1
1

for both cases

Additional context

No response

Activity

  1. self-assigned this
    on Dec 3, 2023
  2. changed the title [-]Wrong behavior for `RANK over()`[/-] [+][BUG] Wrong behavior for `RANK over()`[/+] on Dec 3, 2023
  3. comphead commented on Dec 3, 2023

    @comphead
    ContributorAuthor

    Also unstable behavior

    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.
    
  4. alamb commented on Dec 3, 2023

    @alamb
    Contributor

    Also unstable behavior

    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:

    1. ORDER BY 20, a, 10, 'a', null can produce any output order (as 20 is a constant)
    2. 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?)

  5. comphead commented on Dec 3, 2023

    @comphead
    ContributorAuthor

    Also unstable behavior

    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:

    1. ORDER BY 20, a, 10, 'a', null can produce any output order (as 20 is a constant)
    2. 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions