2.0.4版本中，表建立行策略后，对于表union all查询的情况，外面嵌套一层select * ，策略失效，会查出所有数据

[x1481962098]

对于建了相同策略的a、b两张表，直接union all查询结果，得到的数据是添加策略过滤的数据，但如果再在union all外面嵌套一层select * from，就会导致策略失效，查询出所有数据

[ixzc]

Can you provide a ddl that can reproduce this problem

[x1481962098]

table:

• dwm_achievement
  CREATE TABLE
  IF
  NOT EXISTS dwm_achievement (
  achievement_id VARCHAR ( 255 ) ,
  sn VARCHAR ( 255 ) ,
  ......
  fact_purchase_price DECIMAL(23, 16),
  ......
  @update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  @syn_flag VARCHAR ( 1 ) NOT NULL DEFAULT 'Y',
  @delete_flag VARCHAR ( 1 ) NOT NULL DEFAULT 'Y'
  )
  UNIQUE KEY ( achievement_id )
  DISTRIBUTED BY HASH ( achievement_id ) BUCKETS 10
  PROPERTIES ( "replication_num" = "3" );

• dwm_achievement_lastyear
  CREATE TABLE
  IF
  NOT EXISTS dwm_achievement_lastyear (
  achievement_id VARCHAR ( 255 ) ,
  sn VARCHAR ( 255 ) ,
  ......
  fact_purchase_price DECIMAL(23, 16),
  ......
  @update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  @syn_flag VARCHAR ( 1 ) NOT NULL DEFAULT 'Y',
  @delete_flag VARCHAR ( 1 ) NOT NULL DEFAULT 'Y'
  )
  UNIQUE KEY ( achievement_id )
  DISTRIBUTED BY HASH ( achievement_id ) BUCKETS 10
  PROPERTIES ( "replication_num" = "3" );

row policy:

CREATE ROW POLICY temp_tes_dwm_achievement ON db.dwm_achievement AS RESTRICTIVE TO role temp_test USING (@delete_flag = 'N');
CREATE ROW POLICY temp_tes_dwm_achievement_lastyear ON db.dwm_achievement_lastyear AS RESTRICTIVE TO role temp_test USING (@delete_flag = 'N');

select:

select *
from (
select xxx from dwm_achievement where condition
union all
select xxx from dwm_achievement_lastyear where condition
) a

[ixzc]

i didn't reproduce your question, please add my wechat: Faith_xzc