bug: MSSQL RANK OVER does not support row clause

[kalo37]

What happened?

It looks like if rows are not specified when calling .over(), by default the compiler will add "UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" to the rows clause. This causes issue with T-SQL since the rows clause cannot be specified when using its ranking functions.

When executing the query, this causes a SQL error saying "The function "rank" may not have a window frame"

Compiled:

SELECT 
[t0].[col1], 
[t0].[col2], 
[t0].[col3], 
RANK() OVER (PARTITION BY [t0].[PfId] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - 1 AS [rank_] 
FROM table [t0]

The query would work after removing ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

What version of ibis are you using?

10.0.0dev97

What backend(s) are you using, if any?

MSSQL

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[IndexSeek]

Thank you for reporting this! I could reproduce this and was getting a similar error.

data = {
    "color": ["E", "E", "E", "I", "J"],
    "price": [326, 326, 327, 334, 335],
}

df = pd.DataFrame(data)

con.create_table("diamonds_sample", df, overwrite=True)
expr = con.table("diamonds_sample").mutate(ibis.rank().over(group_by="color", order_by="price"))
expr