[Error] [SECRET_FUNCTION_INVALID_LOCATION]: While running secret function with create or replace

[sumit23]

Hi, recently we made an upgrade to our databricks warehouse, transitioning from SQL Classic to SQL PRO.
However, we started encountering the following error message when attempting to execute the "CREATE or REPLACE" table query with the secret function that was previously working fine with the Classic warehouse.

Error Message:

[SECRET_FUNCTION_INVALID_LOCATION] Cannot execute INSERT command with one or more non-encrypted references to the SECRET function; please encrypt the result of each such function call with AES_ENCRYPT and try the command again.

Steps to reproduce:

i) Create an employee table:

create or replace table <catalog_name>.default.employee (
    id int,
    name string,
    email string,
    joining_date date
);

ii) Inserted data in employee table and encrypted the email column:

with emp_data as (
    select 123 as id, 'John' as name, 'john.doe@gmail.com' as email, '2022-11-01' as joining_date
    union all
    select 456, 'Joe', 'joe@gmail.com', '2023-01-02'
    union all
    select 789, 'Andy', 'andy@gmail.com', '2022-12-15'
)

insert into <catalog_name>.default.employee
select
    id,
    name,
    base64(
        aes_encrypt(
            email,
            secret(<scope_name>, <secret_key>)
        )
    ),
    joining_date
from emp_data;

iii) Create a new table that will have details of all employee who joined in 2023 with decrypted hashed email:

create or replace table <catalog_name>.default.employee_2023
select
    id,
    name,
    sha2(
        cast(
            aes_decrypt(
                unbase64(email),
                secret(<scope_name>, <secret_key>)
            ) as string
        ), 256
    ) as email,
    joining_date
from <catalog_name>.default.employee
where year(joining_date) = '2023';

Note: <catalog_name>, <scope_name> and <secret_key> needs to be updated before running the above code.

Expected Behaviour:

Table employee_2023 should contain the details of all employee who joined in 2023 with decrypted hashed email.

Observed Behaviour:

While decrypting the email column in employee_2023 table creation, databricks is throwing the error
(SECRET_FUNCTION_INVALID_LOCATION) which is mentioned above.

Version:

Databricks Runtime Version: 12.2 LTS
JDBC URL: 2.6.25 or later
Channel: Current(v 2023.30)

Suggestion:

The documentation(https://docs.databricks.com/error-messages/index.html) didn't provide necessary information regarding the resolution of issue. It will be very helpful if the document is updated with the proper and detailed information about the issue.

Also we would really appreciate any help or guidance you can provide to resolve this issue. Waiting for the solution and expecting to get some positive response soon.