fix(mssql): exclude window frame from rank

[IndexSeek]

Description of changes

Resolves an error where the RANK window function added a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING window frame, which is unsupported by MSSQL.

The of the OVER clause cannot be specified for the RANK function. For more information, see OVER Clause (Transact-SQL).

Issues closed

Resolves #10291

[gforsyth]

Nice! Thanks @IndexSeek !

I imagine you tested this out locally, but maybe we should add a snapshot test to confirm that we're generating the correct SQL?

[IndexSeek]

Thank you @gforsyth, happy to help!

I was thinking a test for this scenario would be worthwhile, but wasn't sure how best to go about it.

Would we want to set something up a test to check that the text ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING should not appear in the compiled SQL for these backends that do not support the window frame?

[gforsyth]

Yeah, these can be tricky to test.

You can look at

ibis/ibis/backends/duckdb/tests/test_datatypes.py

Line 107 in f817a7a

@pytest.mark.parametrize("typ", ["float32", "float64"])

For an example of a snapshot test. I think if you have an expression that will generate a rank query, then we can check in the snapshot and that will confirm for us that the window bounds are excluded on mssql

I think this can/should be mssql specific

[IndexSeek]

Thank you for pointing me to an example. I gave this a try; hopefully, it covers this scenario. I'm happy to make any changes as needed!

[gforsyth]

Nice! Thanks @IndexSeek !