About Data Masking in Use

[BigStupid666]

create table memory.default.test(id varchar(50));
insert into memory.default.test values('101');

select * from memory.default.test
id |
---+
101|

Add in rule：
{
"name": "id",
"mask": "cast(to_hex(md5(to_utf8(id))) as varchar(32))"
}

select * from memory.default.test
id |
------------------------------------------+
38B3EFF8BAF56627478EC76A704E9B52|

When I add the where condition ，It didn't work as I thought
select * from memory.default.test where id = '101'
id |
--+

The way I want to work is：
select * from memory.default.test where id = '101'
id |
------------------------------------------+
38B3EFF8BAF56627478EC76A704E9B52|

But it actually work as:
select * from memory.default.test where id = '38B3EFF8BAF56627478EC76A704E9B52'
id |
------------------------------------------+
38B3EFF8BAF56627478EC76A704E9B52|

How can I achieve it as I want ?

[martint]

It's not possible to do what you're asking with masking rules. The purpose of masks is to prevent users from getting access to the underlying data in its original form. From the user query perspective, the data comes out of the table in the already-masked form, so there's no way to refer to the underlying values.

[jywjyw]

The purpose of masks is to prevent users from getting access to the underlying data in its original form.

I believe column mask should only take effect during display, people don't have high expectations for its security. Otherwise, all aggregation SQL results would be wrong, which has a significant impact.