【详述】
with cur_nums AS (
SELECT distinct 1 AS cur_cnt
, CASE WHEN action_reason_id = ‘LAY’ THEN ‘2’ ELSE ‘1’
END AS action_type, ‘A000001’ as org_id, ‘XX集团’ AS org_name_cn
FROM fdw_ads_erchannel_dimission_scenes_sum
),
last_nums AS (
SELECT distinct 2 AS last_cnt
, CASE
WHEN action_reason_id = ‘LAY’ THEN ‘2’
ELSE ‘1’
END AS action_type, ‘A000001’ as org_id, ‘XX集团’ AS org_name_cn
FROM fdw_ads_erchannel_dimission_scenes_sum
),
new_cur_nums as (
select * from last_nums tmp
where tmp.org_id in (select distinct org_id from cur_nums )
)
select a.* from cur_nums a
full outer join new_cur_nums b
on a.org_id =b.org_id and a.org_name_cn=b.org_name_cn and a.action_type=b.action_type
该sql查询报错：
Caused by: java.sql.SQLSyntaxErrorException: Not found dict for cid:156 backend [id=11002] [host=a-starrocks-with-all-features-be-2.a-starrocks-with-all-features-be-search.starrocks.svc.cluster.local]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
尝试1、如果把where tmp.org_id in (select distinct org_id from cur_nums ) 注释掉，则不会报错。
尝试2、表fdw_ads_erchannel_dimission_scenes_sum 用with 模拟数据替代，则不会报错
WITH fdw_ads_erchannel_dimission_scenes_sum_temp as
(
select ‘A000001’ AS org_id,‘XX集团’ as org_name_cn,‘LAY’ as action_reason_id,1 as cur_cnt
union all
select ‘A000001’ AS org_id,‘XX集团’ as org_name_cn,‘OTHER’ as action_type,1 as cur_cnt
)
尝试3、把sql改成下面(加粗部分)则不报错：
with cur_nums AS (
SELECT distinct 1 AS cur_cnt
, CASE WHEN action_reason_id = ‘LAY’ THEN ‘2’ ELSE ‘1’
END AS action_type, ‘OA000001’ as org_id, ‘XX集团’ AS org_name_cn
FROM fdw_ads_erchannel_dimission_scenes_sum
),
last_nums AS (
SELECT distinct 2 AS last_cnt
, CASE
WHEN action_reason_id = ‘LAY’ THEN ‘2’
ELSE ‘1’
END AS action_type, ‘OA000001’ as org_id, ‘XX集团’ AS org_name_cn
FROM fdw_ads_erchannel_dimission_scenes_sum
),
new_cur_nums as (
select * from last_nums tmp
where tmp.org_id in (select distinct org_id from fdw_ads_erchannel_dimission_scenes_sum where org_id='OA000001’)
)
select a.* from cur_nums a
full outer join new_cur_nums b
on a.org_id =b.org_id and a.org_name_cn=b.org_name_cn and a.action_type=b.action_type
【背景】无
【业务影响】
【是否存算分离】否
【StarRocks版本】例如：1.18.2
【集群规模】例如：3fe（1 follower+2observer）+3be
【联系方式】yaowj2@qq.com
【附件】
- 查询报错：
Caused by: java.sql.SQLSyntaxErrorException: Not found dict for cid:156 backend [id=11003] [host=a-starrocks-with-all-features-be-0.a-starrocks-with-all-features-be-search.starrocks.svc.cluster.local]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)- query_dump 晚点补充