feat(arrow-ord): support boolean in rank and add tests for sorting lists of booleans

[rluvaton]

Which issue does this PR close?

Part of #6911

Rationale for this change

I want to sort list of of booleans

What changes are included in this PR?

added rank function for boolean and added tests for sorting list of booleans and ranking booleans

Are there any user-facing changes?

Yes, the rank function now support BooleanArray and sort support sorting list of booleans

this build on top of:

• chore(arrow-ord): move can_rank to the rank file #6910

[tustvold]

Rather than sorting booleans directly, it would be orders of magnitude faster to count the number of bits and use this to compute the result. Nulls can be handled by first computing the bitwise and with the values.

[rluvaton]

Rather than sorting booleans directly, it would be orders of magnitude faster to count the number of bits and use this to compute the result. Nulls can be handled by first computing the bitwise and with the values.

Great idea, did not think about it (I was just trying to implement before trying to make it fast as you said)

[tustvold]

Fair, I'll try to find some time over the next few weeks to review this, but I'm afraid I have very little time to review PRs, especially of this size

[rluvaton]

@tustvold Updated to improve performance, please let me know if that's what you meant

[Jefffrey]

Updated to improve performance, please let me know if that's what you meant

I think he was referring to the sort logic for booleans, which doesn't actually seem to be implemented in this PR? It might be worth splitting this PR as its doing two separate things and might cause confusion.

[alamb]

Marking as draft as I think this PR is no longer waiting on feedback. Please mark it as ready for review when it is ready for another look

[rluvaton]

@alamb, @Jefffrey this is now ready for review, sorry for the long delay

[Jefffrey]

LGTM; I think just adding a few last test cases to get complete coverage should push this over the line.

---

Something interesting about the rank function in arrow-rs is that it seems to behave differently compared to others.

Postgres:

rank () → bigint
Returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.

• https://www.postgresql.org/docs/current/functions-window.html

DuckDB:

Description The rank of the current row with gaps; same as row_number of its first peer.

• https://duckdb.org/docs/sql/functions/window_functions.html#rank

SQL Server:

If two or more rows tie for a rank, each tied row receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one.

• https://learn.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver16

It seems these other ones would have ties take the lower values, whereas for arrow-rs the existing rank function (and subsequently what is introduced here) takes the higher value; I guess this might be worth a broader discussion, as this PR itself currently follows the existing behaviour

🤔

[rluvaton]

@Jefffrey done

[Jefffrey]

LGTM 👍

I'll merge in next few days if no further comments

[alamb]

Thanks @rluvaton @tustvold and @Jefffrey ❤️

[Jefffrey]

Thanks @rluvaton & co

[Jefffrey]

LGTM; I think just adding a few last test cases to get complete coverage should push this over the line.

Something interesting about the rank function in arrow-rs is that it seems to behave differently compared to others.

Postgres:

rank () → bigint
Returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.

* https://www.postgresql.org/docs/current/functions-window.html

DuckDB:

Description The rank of the current row with gaps; same as row_number of its first peer.

* https://duckdb.org/docs/sql/functions/window_functions.html#rank

SQL Server:

If two or more rows tie for a rank, each tied row receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one.

* https://learn.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver16

It seems these other ones would have ties take the lower values, whereas for arrow-rs the existing rank function (and subsequently what is introduced here) takes the higher value; I guess this might be worth a broader discussion, as this PR itself currently follows the existing behaviour

🤔

Raised #7024