[UNRESOLVED_ROUTINE] Cannot resolve function `date_format`

[Jaclaglez13]

Hi all,

We are getting the following error log in a Workflow:

AnalysisException: [UNRESOLVED_ROUTINE] Cannot resolve function `date_format` on search path [`system`.`builtin`, `system`.`session`]. SQLSTATE: 42883

The Workflow consists in different notebooks, each of the notebooks runs with the same cluster in order to save idle time of the cluster. When we trigger the workflow, the first task runs successfully but when the second one starts it always fails at the same point with the error log shared above. 

This is the configuration that we are using:

• Databricks RT version: 14.3 LTS (Scala 2.12, Spark 3.5.0)
• Unity Catalog enabled

We are importing the library org.apache.spark.sql.functions._ to use the date_format and other spark functions.

Have you ever encounter this issue? Any known solution for this? 

[Brahmareddy]

Hi Jaclaglez13,

How are you doing today?, As per my understanding, yeah, this issue usually happens when Unity Catalog affects function resolution across different tasks in a Workflow. Since your first task runs fine but the second one fails, it’s likely that the session loses access to built-in SQL functions when switching notebooks. A few things to try: use the fully qualified function name (org.apache.spark.sql.functions.date_format in Python or system.builtin.date_format in SQL) to ensure proper resolution, restart the cluster before the second task to refresh the session, or check if the function is available by running SHOW FUNCTIONS LIKE 'date_format';. If the function isn’t listed, the session might not be resolving built-in functions correctly. These should help get things running smoothly—let me know what works!

Regards,

Brahma 

[Jaclaglez13]

Hi Brahmareddy! Thank you for your answer, it was really useful for testing what was going on with our pipeline. 

The main problem was that, we were importing a custom library and creating custom DataFrame objects using classes from it in our notebook, which hasn't the same spark version as the compute. Therefore this was causing an issue while calling spark built-in functions because of that spark session conflict .

In order to solve this we refactored our code and aligned the spark version.

Thank you very much for your help. 

[loic]

Hello @Jaclaglez13 

I am really interested in your ticket because we have the same kind of error.
We loose the SQL built-in function when we run the notebook a second time without restarting our "all purpose compute".
There is a point I would like to clarify regarding your last message.
What do you mean exactly by: "we were importing a custom library and creating custom DataFrame"?
Does the issue was that this "custom library" (uber jar?) was embedding some Spark libraries in conflict with the Spark version installed on the Databricks compute?
In my case, the only external dependency that we use is org.apache.common.commons-csv, otherwise, all other dependencies are compiled with the maven "scope" property set to "provided", so we use dependencies already installed on the Databricks Runtime.

Your help would be really appreciate!

Loïc

[VaDim]

Having a similar issue when moving from DBR 13 to DBR 16.4 in a multi-task Job. The first task works ok but the 2nd with identical code but different params fails. For me it fails with Cannot resolve function `and` (I assume from the .between(from_date, to_date)).

Anyone solved it? or knows of a workaround? 

[phanvy5403]

I encountered similar issue when upgrading DBR 13 to 16.4. Have been stuck with this issue for weeks. It's not gonna only happen with `and`, other functions run into this error like `coalesce`,`=`,`row_number`,.etc
Have you found any documents related to this issue? 

[vladmihalcea]

Hey, also interested if you guys find any documentation regarding this