I think this might be related to some of the optimization mechanisms on how snowflake query works.

For smaller functions there is an inlining process.
You can read more here:
https://teej.ghost.io/understanding-the-snowflake-query-optimizer/

so your scalar UDF was just lucky because there is no implicit cast support

https://docs.snowflake.com/en/sql-reference/data-type-conversion#data-types-that-can-be-cast