column mask on Y columns gives error

DW — Fri, 11 Oct 2024 16:07:27 GMT

My table breaks when I try to mask a column with a name like `<tinyint>Y`

-- Create a table with a masked column
> CREATE FUNCTION mask_int_col(col_val INTEGER) RETURN CASE WHEN is_member('HumanResourceDept') THEN col_val ELSE CAST(NULL as INTEGER) END;
> CREATE TABLE persons(name STRING, 1Y INTEGER MASK mask_int_col);
> INSERT INTO persons VALUES('James', 1);

-- What I expect when I'm not a member of the Human Resource Department
> SELECT * FROM persons;
James NULL

-- What I get
> SELECT * FROM persons;
[UNRESOLVED_COLUMN.WITHOUT_SUGGESTION] A column, variable, or function parameter with name `1Y` cannot be resolved. SQLSTATE: 42703

Column names like e.g. 128Y or 1Ya are not a problem.

I understand column names like 1Y are better to be avoided, but that a table breaks because of this does surprise me.

Is there a solution for this problem?

Luckily I can access the table again when dropping the mask.

ALTER TABLE persons ALTER COLUMN `1Y` DROP MASK

Re: column mask on Y columns gives error

filipniziol — Sun, 13 Oct 2024 11:09:44 GMT

Hi @DW ,

I have replicated your scenario and encountered the same error when applying a column mask to a column named 1Y in Databricks SQL.

In short, it makes sense simply to follow Databricks documentation and use the SQL naming conventions, so that column names:

Begin with a letter or an underscore.
Contain only letters, digits, and underscores.

In my experience, using not standard column names, then the need to use ` backticks characters is asking oneself for trouble.

topic Re: column mask on <tinyint>Y columns gives error in Get Started Discussions

column mask on Y columns gives error

Re: column mask on Y columns gives error