视图查询报无效正则表达式

[allure]

CREATE VIEW V_rm_pdb_data_campaign_group_deal_bind AS SELECT a.prefix AS prefix, a.key AS key, a.value AS value, a.create_by AS create_by, a.update_by AS update_by, a.create_time AS create_time, a.is_delete AS is_delete, a.is_delete_from_cache AS is_delete_from_cache, a.ad_type_id AS ad_type_id, a.ad_style_id AS ad_style_id, a.media_type AS media_type, a.placement AS placement, a.position_id AS position_id, a.position AS position, a.market AS market, a.start_time AS start_time, a.end_time AS end_time, a.trans_type AS trans_type, a.order_id AS order_id, a.meid AS meid, b.region_name AS region_name, b.position_name AS position_name, b.brand_Id AS brand_id, b.brand_name AS brand_name, b.product_id AS product_id, b.product_name AS product_name, b.campaign_platform_name AS campaign_platform_name FROM (SELECT prefix AS prefix, key AS key, value AS value, create_by AS create_by, update_by AS update_by, create_time AS create_time, is_delete AS is_delete, is_delete_from_cache AS is_delete_from_cache, ad_type_id AS ad_type_id, ad_style_id AS ad_style_id, media_type AS media_type, placement AS placement, position_id AS position_id, position AS position, market AS market, start_time AS start_time, end_time AS end_time, trans_type AS trans_type, replace(replace(replace(value, ‘]’, ‘’), ‘"’, ‘’), ‘[’, ‘’) AS order_id, CASE WHEN (replace(key, prefix, ‘’) LIKE ‘%_mobile%’) THEN regexp_replace(replace(key, prefix, ‘’), ‘_mobile’, ‘’) WHEN (replace(key, prefix, ‘’) LIKE ‘%_pc%’) THEN regexp_replace(replace(key, prefix, ‘’), ‘_pc’, ‘’) WHEN (replace(key, prefix, ‘’) LIKE ‘%_ott%’) THEN regexp_replace(replace(key, prefix, ‘’), ‘_ott’, ‘’) ELSE replace(key, prefix, ‘’) END AS meid FROM (SELECT key AS key, value AS value, create_by AS create_by, update_by AS update_by, create_time AS create_time, is_delete AS is_delete, prefix AS prefix, is_delete_from_cache AS is_delete_from_cache, ad_type_id AS ad_type_id, ad_style_id AS ad_style_id, media_type AS media_type, placement AS placement, position_id AS position_id, position AS position, market AS market, start_time AS start_time, end_time AS end_time, trans_type AS trans_type FROM default_cluster:starRocks.rm_pdb_data_campaign_group_deal_bind UNION SELECT key AS key, value AS value, create_by AS create_by, update_by AS update_by, create_time AS create_time, is_delete AS is_delete, prefix AS prefix, is_delete_from_cache AS is_delete_from_cache, ad_type_id AS ad_type_id, ad_style_id AS ad_style_id, media_type AS media_type, placement AS placement, position_id AS position_id, position AS position, market AS market, start_time AS start_time, end_time AS end_time, trans_type AS trans_type FROM default_cluster:starRocks.rm_pdb_data_deal_bind) ww) a LEFT OUTER JOIN default_cluster:starRocks.rmos_order b ON a.order_id = b.order_id;

查询视图子句正常，查询视图时报错无效正则表达式

[jingdan]

你好，请提供下base表建表语句、版本信息和异常栈，谢谢

[allure]

CREATE TABLE rm_pdb_data_campaign_group_deal_bind (
id int(11) NOT NULL COMMENT “ID”,
campaign_group_id int(11) NULL COMMENT “活动组ID”,
prefix varchar(8) NOT NULL COMMENT “媒体前缀”,
key varchar(180) NOT NULL COMMENT “媒体订单ID”,
value varchar(512) NOT NULL COMMENT “rm订单ID”,
create_by int(11) NOT NULL COMMENT “创建者”,
update_by int(11) NOT NULL COMMENT “更新者”,
create_time int(11) NOT NULL COMMENT “创建时间”,
update_time int(11) NOT NULL COMMENT “更新时间”,
is_delete tinyint(4) NOT NULL COMMENT “是否删除0/否,1/是”,
is_delete_from_cache tinyint(4) NOT NULL DEFAULT “0” COMMENT “是否从缓存中删除0/否,1/是”,
ad_type_id int(11) NOT NULL COMMENT “广告位类型id”,
ad_style_id int(11) NOT NULL COMMENT “广告形式/样式id”,
media_type varchar(8) NOT NULL COMMENT “设备端”,
placement varchar(8) NOT NULL COMMENT “时长”,
position_id int(11) NOT NULL COMMENT “广告id”,
position varchar(64) NOT NULL COMMENT “广告位”,
market varchar(16) NOT NULL COMMENT “市场”,
start_time varchar(16) NOT NULL COMMENT “开始时间”,
end_time varchar(16) NOT NULL COMMENT “结束时间”,
trans_type tinyint(4) NOT NULL COMMENT “投放/交易类型，目前除了PD:2，其余都是PDB:1”
) ENGINE=OLAP
UNIQUE KEY(id)
COMMENT “OLAP”
DISTRIBUTED BY HASH(id) BUCKETS 20
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”
);

CREATE TABLE rm_pdb_data_deal_bind (
id int(11) NOT NULL COMMENT “ID”,
campaign_id int(11) NULL COMMENT “活动ID”,
key varchar(180) NOT NULL DEFAULT “” COMMENT “媒体订单ID”,
value varchar(65533) NULL COMMENT “rm订单ID”,
create_by int(11) NOT NULL COMMENT “创建者”,
update_by int(11) NOT NULL COMMENT “更新者”,
create_time int(11) NOT NULL COMMENT “创建时间”,
update_time int(11) NOT NULL COMMENT “更新时间”,
is_delete tinyint(4) NOT NULL COMMENT “是否删除0/否,1/是”,
prefix varchar(8) NOT NULL COMMENT “媒体前缀”,
is_delete_from_cache tinyint(4) NOT NULL DEFAULT “0” COMMENT “是否从缓存中删除0/否,1/是”,
ad_type_id int(11) NOT NULL COMMENT “广告位类型id”,
ad_style_id int(11) NOT NULL COMMENT “广告形式/样式id”,
media_type varchar(8) NOT NULL COMMENT “设备端”,
placement varchar(8) NOT NULL COMMENT “时长”,
position_id int(11) NOT NULL COMMENT “广告id”,
position varchar(64) NOT NULL COMMENT “广告位”,
market varchar(16) NOT NULL COMMENT “市场”,
start_time varchar(16) NOT NULL COMMENT “开始时间”,
end_time varchar(16) NOT NULL COMMENT “结束时间”,
trans_type tinyint(4) NOT NULL COMMENT “投放/交易类型，目前除了PD:2，其余都是PDB:1”
) ENGINE=OLAP
UNIQUE KEY(id)
COMMENT “OLAP”
DISTRIBUTED BY HASH(id) BUCKETS 20
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”
);

CREATE TABLE rmos_order (
order_id int(11) NOT NULL COMMENT “”,
campaign_id int(11) NULL COMMENT “”,
publisher_id int(11) NULL COMMENT “”,
region_id int(11) NULL COMMENT “”,
platform_id int(11) NULL COMMENT “”,
placement_id int(11) NULL COMMENT “”,
channel_id int(11) NULL COMMENT “”,
market varchar(1209) NULL COMMENT “”,
is_del boolean NULL COMMENT “”,
ta_score float NULL COMMENT “”,
advertiser_Id int(11) NULL COMMENT “”,
brand_Id int(11) NULL COMMENT “”,
product_id int(11) NULL COMMENT “”,
campaign_group_id int(11) NULL COMMENT “”,
target varchar(100) NULL COMMENT “”,
agent_Id int(11) NULL COMMENT “”,
ad_type varchar(32) NULL COMMENT “”,
start_time datetime NULL COMMENT “”,
end_time datetime NULL COMMENT “”,
dmp_id int(11) NULL COMMENT “”,
media_type varchar(10) NULL COMMENT “”,
region_name varchar(32) NULL COMMENT “”,
update_time int(11) NULL COMMENT “”,
position_name varchar(128) NULL COMMENT “”,
position_id int(11) NULL COMMENT “”,
channel_name varchar(255) NULL COMMENT “”,
campaign_name varchar(255) NULL COMMENT “”,
brand_name varchar(255) NULL COMMENT “”,
product_name varchar(255) NULL COMMENT “”,
agent_name varchar(255) NULL COMMENT “”,
campaign_platform_name varchar(255) NULL COMMENT “”,
campaign_group_name varchar(255) NULL COMMENT “”,
placement_name varchar(64) NULL COMMENT “”,
advertiser_name varchar(64) NULL COMMENT “”,
dmp_set_update_time int(11) NULL COMMENT “”,
campaign_type int(11) NULL COMMENT “”,
position varchar(100) NULL COMMENT “”,
ad_format int(11) NULL COMMENT “”,
rm_publisher_id int(11) NULL COMMENT “”,
ad_format_name varchar(10) NULL COMMENT “”,
Buy_in_Model varchar(24) NULL COMMENT “”,
ad_type_id int(11) NULL COMMENT “”,
buy_type int(11) NULL COMMENT “”,
rolling_rank varchar(300) NULL COMMENT “”,
price int(11) NULL COMMENT “”,
ext_update_time int(11) NULL COMMENT “”,
app varchar(128) NULL COMMENT “”,
type_name varchar(128) NULL COMMENT “”
) ENGINE=OLAP
UNIQUE KEY(order_id)
COMMENT “OLAP”
DISTRIBUTED BY HASH(order_id) BUCKETS 20
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”
);

版本信息：2.0.1

报错信息：
2022/02/28 14:25:35 - 表输入 3.0 - ERROR (version 8.2.0.7-719, build 8.2.0.7-719 from 2019-06-27 12.22.51 by kettle) : Unexpected error
2022/02/28 14:25:35 - 表输入 3.0 - ERROR (version 8.2.0.7-719, build 8.2.0.7-719 from 2019-06-27 12.22.51 by kettle) : org.pentaho.di.core.exception.KettleDatabaseException:
2022/02/28 14:25:35 - 表输入 3.0 - An error occurred executing SQL:
2022/02/28 14:25:35 - 表输入 3.0 - SELECT * FROM V_rm_pdb_data_campaign_group_deal_bind ;
2022/02/28 14:25:35 - 表输入 3.0 - Invalid regex expression: [6�=
2022/02/28 14:25:35 - 表输入 3.0 -
2022/02/28 14:25:35 - 表输入 3.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1768)
2022/02/28 14:25:35 - 表输入 3.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:236)
2022/02/28 14:25:35 - 表输入 3.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:140)
2022/02/28 14:25:35 - 表输入 3.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2022/02/28 14:25:35 - 表输入 3.0 - at java.lang.Thread.run(Unknown Source)
2022/02/28 14:25:35 - 表输入 3.0 - Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Invalid regex expression: [6�=
2022/02/28 14:25:35 - 表输入 3.0 - at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
2022/02/28 14:25:35 - 表输入 3.0 - at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
2022/02/28 14:25:35 - 表输入 3.0 - at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
2022/02/28 14:25:35 - 表输入 3.0 - at java.lang.reflect.Constructor.newInstance(Unknown Source)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.Util.getInstance(Util.java:360)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
2022/02/28 14:25:35 - 表输入 3.0 - at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
2022/02/28 14:25:35 - 表输入 3.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1757)
2022/02/28 14:25:35 - 表输入 3.0 - … 4 more
2022/02/28 14:25:35 - 表输入 3.0 - Finished reading query, closing connection.