SNOW-3217477: `lower` does not return string type

[DPeled]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   Python 3.11.13

2. What are the Snowpark Python and pandas versions in the environment?

    pandas==2.1.4
    pandas-gbq==0.31.0
    snowflake-snowpark-python==1.42.0
   

3. What did you do?

   when running lower() on a variant column the result are returned as variant as well:

   >>> from snowflake.snowpark import Session
   >>> from snowflake.snowpark.functions import lit, lower
   >>> session = Session.builder.config('local_testing', True).create()
   >>> df = session.create_dataframe([[{"a": "AbC"}]], schema=['entry'])
   >>> df.select(lower(df.entry['a'])).collect()
   [Row(LOWER("ENTRY"['A'])='"abc"')]

   As you can see, the result is surrounded by "...".

4. What did you expect to see?

   When executing in Snowflake

   SELECT lower('AbC'::variant)

   It returns just abc

[sfc-gh-sghosh]

Hello @DPeled ,

Thanks for raising the issue, it's a known issue .
lower() on a variant column in local testing mode with semi-structured data types returns the result wrapped in double quotes ("abc" instead of abc)

We will work on the fix to eliminate it. Meanwhile, as a workaround, can you cast it to Stringtype

session = Session.builder.config('local_testing', True).create()
df = session.create_dataframe([[{"a": "AbC"}]], schema=['entry'])
df.select(lower(df.entry['a'].cast(StringType()))).collect()

[Row(LOWER( CAST ("ENTRY"['A'] AS STRING))='abc')]