column mask on <tinyint>Y columns gives error
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-11-2024 09:07 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-13-2024 04:09 AM
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.