SNOW-2043855: Window function 'rank' doesn't seem to work correcly in local testing mode

[juliankeppel]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?
   3.9.20

2. What are the Snowpark Python and pandas versions in the environment?
   pandas==2.2.3
   snowflake-snowpark-python==1.30.0

3. What did you do?

from snowflake.snowpark import Session
from snowflake.snowpark.window import Window
import snowflake.snowpark.functions as func

session = Session.builder.config("local_testing", True).create()

df_to_test = session.create_dataframe(
    [
        ("hello", 1),
        ("hello", 1),
        ("hello", 2),
        ("hello", 3),
    ],
    ["col1", "col2"],
)

window_spec_asc = Window.partition_by(func.col("col1")).order_by(func.col("col2").asc())
window_spec_desc = Window.partition_by(func.col("col1")).order_by(func.col("col2").desc())
df_ranked_asc = df_to_test.with_column("rank", func.rank().over(window_spec_asc))
df_ranked_desc = df_to_test.with_column("rank", func.rank().over(window_spec_desc))

df_ranked_asc.orderBy("rank").show()
df_ranked_desc.orderBy("rank").show()

4. What did you expect to see?
   I expected the following output:

----------------------------
|"COL1"  |"COL2"  |"RANK"  |
----------------------------
|hello   |1       |1       |
|hello   |1       |1       |
|hello   |2       |3       |
|hello   |3       |4       |
----------------------------

----------------------------
|"COL1"  |"COL2"  |"RANK"  |
----------------------------
|hello   |3       |1       |
|hello   |2       |2       |
|hello   |1       |3       |
|hello   |1       |3       |
----------------------------

But I actually get the following output:

----------------------------
|"COL1"  |"COL2"  |"RANK"  |
----------------------------
|hello   |1       |1       |
|hello   |1       |1       |
|hello   |2       |3       |
|hello   |3       |4       |
----------------------------

----------------------------
|"COL1"  |"COL2"  |"RANK"  |
----------------------------
|hello   |1       |1       |
|hello   |1       |1       |
|hello   |2       |3       |
|hello   |3       |4       |
----------------------------

So the ascending or descending order in the window specification doesn't seem to have the effect it should have.

[sfc-gh-sghosh]

Hello @juliankeppel ,

Thanks for raising the issue, we are able to reproduce the issue with local session, with regular session its working fine.
Will work on eliminating it.

Regards,
Sujan