create table ac_meid_122981 as select md5(meid) as meid_hash,meid ,campaign_platform_name
from V_rm_pdb_data_campaign_group_deal_bind a
where order_id != ‘null’ and order_id in (select order_id from rmos_order where campaign_group_id in (4782,4781,4780))
group by 1,2,campaign_platform_name ;
2.2-rc2版本，select语句可以查出结果，但是create as 语句报错unknown
be的日志中有什么信息么？后面的查询可以执行成功么？
be.WARING没有明显日志，后面的select语句是可以执行成功的
麻烦发下fe的info和warn日志
请问下是昨天的问题？你发的今天的日志没有对应的表的日志，辛苦发下昨天的fe日志
这个今天也执行了的
日志里面没有搜到create的相关日志，我这边本地也没有复现，帮忙发下这个几个表的建表语句，我再复现下。
这个应该是视图的原因，换成实体表是可以的，视图sql如下
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, if(substring(value, 1, 1) = ‘[’, split_part(replace(replace(substring(value, 2), ‘"’, ‘’), ‘]’, ‘’), ‘,’, 1), value) AS order_id, CASE WHEN key LIKE ‘%_mobile%’ THEN replace(replace(key, prefix, ‘’), ‘_mobile’, ‘’) WHEN key LIKE ‘%_pc%’ THEN replace(replace(key, prefix, ‘’), ‘_pc’, ‘’) WHEN key LIKE ‘%_ott%’ THEN 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) wer) a LEFT OUTER JOIN default_cluster:starRocks.rmos_order b ON a.order_id = b.order_id;
辛苦发下base表的建表语句
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 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 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.1.5版本这个有修复吗
我也遇到这个问题，单独测试 create table test as select 1 as a,2 as b 可以执行ok ，但是执行我的复杂关联查询语句报 1064 - Unknown error, Time: 0.012000s
d.created_at,
d.updated_at,
d.deleted_at
from
(select *FROM ods.pre_project_tasks_ods limit 10) d
left join
(select a.id,a.name as project_name,a.userid as project_userid,b.nickname as project_username,a.created_at as project_created_at ,a.updated_at as project_updated_at from
(select id,name,userid,created_at,updated_at,deleted_at from ods.pre_projects_ods limit 10) a
left join ods.pre_users_ods b on a.userid=b.userid) c ON d.project_id=c.id
) e
left join ods.pre_project_columns_ods f on e.column_id=f.id
left join ods.pre_users_ods g on e.userid=g.userid
java.lang.NullPointerException: null
at com.starrocks.sql.analyzer.CTASAnalyzer.transformCTASStmt(CTASAnalyzer.java:66) ~[starrocks-fe.jar:?]
at com.starrocks.sql.analyzer.Analyzer.analyze(Analyzer.java:60) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:34) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:299) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:268) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:415) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:651) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$419(ReadListener.java:55) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_212]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_212]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_212]
CREATE table test
as select 1 as a,2 as b,"" as d,null as c 这样测试就报错 1064 - Unsupported CTAS transform type: null, Time: 0.011000s 在使用create table as select 时字段值不能为null ？？？
你好，这个问题已经修复合入main分支，预计在这周发布的2.2正式版本发布，请知晓。具体可参考https://github.com/StarRocks/starrocks/pull/6109
你好，这个问题已经修复，会在下次发版中发布。具体可参考https://github.com/StarRocks/starrocks/issues/6074
，点赞