case when语句中有子查询时,case字段后面的字段报错信息异常Only support subquery in binary predicate in case statement

Viewed 67

在查询语句中使用case when语句时,当case when语句中有子查询时,case字段后面的字段,无论发生什么错误,报错信息都会为ERROR 1105 (HY000): errCode = 2, detailMessage = Only support subquery in binary predicate in case statement.

如果将case when语句中有子查询的sql作为子查询关联表时,无论该子查询外的sql有什么错误,报错信息都会为ERROR 1105 (HY000): errCode = 2, detailMessage = Only support subquery in binary predicate in case statement.

有如下表结构的两张表
CREATE TABLE int_mkt_chan_gridtlab_doris_hhgk_d (
CHANNEL_ID BIGINT NOT NULL,
CHANNEL_NAME VARCHAR NULL,
SOURCE_FLAG BIGINT NULL
);
CREATE TABLE szqtest (
channel_id BIGINT NULL,
is_pz TINYINT NULL
);

执行如下语句:

select a.channel_id,
case 
when a.source_flag in (select channel_id from szqtest) then 'sp' 
else 'yxt' 
end ff,
abc
from int_mkt_chan_gridtlab_doris_hhgk_d a

正常来说应该报错信息应该为:detailMessage = Unknown column 'abc' in 'table list'.
但是实际报错信息为:detailMessage = Only support subquery in binary predicate in case statement.

2 Answers

已知问题,应该是旧优化器回退问题,可以把这个关了,SET enable_fallback_to_original_planner=true; 再验证下,在2.1.6上这个回退到旧优化器了,报错不太友好,高版本会逐渐关闭回退就没有这个问题了

终于,找到答案了。