tidb 5.0.3 查询视图报错

tidb 5.0.3
视图创建成功了,查询的时候报错了。

报错信息:
mysql> select * from sdb_core_monitor limit 10;
ERROR 1105 (HY000): line 1 column 7021 near "AS ord LEFT JOIN (SELECT shuidi_sdb_crm.external_insurance_order.id,shuidi_sdb_crm.external_insurance_rm.external_insurance_order.user_id,shuidi_sdb_crm.external_insurance_order.display_id,shuidi_sdb_crm.external_insurance_order.external_displaal_insurance_order.origin,shuidi_sdb_crm.external_insurance_order.money,shuidi_sdb_crm.external_insurance_order.refund_amount,`shuidi_sdb_cr
mysql> s
→ \s

视图内容:
create view sdb_core_monitor as
select case when channel like ‘kmbx%’ then ‘其他’
when is_long=1 and is_crm_order=1 then ‘网电销’
when is_long=0 or (is_long=1 and is_crm_order=0) then ‘平台业务’
end as first_order_channel_group,
case when channel like ‘kmbx%’ then ‘其他’
when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
when is_long=1 and is_crm_order=1 then ‘无职场’
when is_long=0 then ‘平台-短险’
when is_long=1 and is_crm_order=0 then ‘平台-长险’
end as sec_order_channel_group,
case when channel like ‘kmbx%’ then ‘其他’
when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
when is_long=1 and is_crm_order=1 then ‘无职场’
when is_long=1 and is_crm_order=0 and product_name = ‘e养添年养老年金保险’ then ‘长险-养老金’
when is_long=1 and is_crm_order=0 and channel like ‘sdbbtt%’ then ‘长险-投放’
when is_long=1 and is_crm_order=0 then ‘长险-其他’
when is_long=0 and parent_policy_no <> ‘’ then ‘短险-续保’
when is_long=0 and is_crm_order=1 then ‘短险-保’
when is_long=0 and channel in (‘cf’,‘jkbz’,‘cfjk’,‘cfjkgj’,‘sdcjk’,‘sdcjkgj’,‘jksh’,‘sdhz’) then ‘短险-筹’
when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) then ‘短险-投放’
else ‘短险-保’ end third_order_channel_group,
case when channel like ‘kmbx%’ then ‘其他’
when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
when is_long=1 and is_crm_order=1 then ‘无职场’
when is_long=1 and is_crm_order=0 and product_name = ‘e养添年养老年金保险’ then ‘长险-养老金’
when is_long=1 and is_crm_order=0 and channel like ‘sdbbtt%’ then ‘长险-分销’
when is_long=1 and is_crm_order=0 then ‘长险-其他’
when is_long=0 and parent_policy_no <> ‘’ then ‘短险-续保’
when is_long=0 and is_crm_order = 1 then ‘电销’
when is_long=0 and channel in (‘cf’,‘jkbz’,‘cfjk’,‘cfjkgj’,‘sdcjk’,‘sdcjkgj’,‘jksh’,‘sdhz’) then ‘短险-筹’
when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) and channel like ‘sdbbtt%’ then ‘短险-分销’
when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) and (channel in (‘feed’,‘sem’) or channel like ‘BD%’) then ‘短险-H5’
when (channel like ‘%sdjk%’ or channel like ‘%sdik%’) then ‘健康’
when (channel=‘sdbaoapp’ or (platform in (3,4) and channel in (‘default_channel’,‘sdbapp’,‘sdbao’,‘sdbaoapp’))) then ‘保APP’
when (channel like ‘qxyy%’ or channel like ‘qywx%’) then ‘企信’
when channel like ‘mcn%’ then ‘内容获客’
when (sub_channel like ‘BA_NO_C1_Dx_back%’
or sub_channel LIKE ‘%ZH%’
or sub_channel LIKE ‘%BAM_NO_C1_zhaohuiMessages%’
or sub_channel LIKE ‘BA_NO_C1_MB%’
or sub_channel LIKE ‘BAM_NO_C1_m10%’
or sub_channel LIKE ‘BA_NO_C1_Messages%’
or sub_channel LIKE ‘BA_NO_C1_Template%’
or sub_channel LIKE ‘%BA_NO_C1_yuebaonei_0730%’
– or landing_sub_channel like ‘%BAM_NO_C1_zhaohuiMessages%’
– or landing_sub_channel like ‘%BA_NO_C1_Messages%’
) then ‘短信’
when channel like ‘sdbao%’ then ‘保公众号’
else ‘保公众号’ end as fourth_order_channel_group,
order_no,
policy_time,
origin,
is_crm_order
from
(select ord.order_no
,ord.parent_policy_no
,ord.platform
,ord.policy_time
,ord.origin
,case
when date(ord.policy_time) >= ‘2020-09-01’ and ord.platform in (23, 24) and main_ord.order_source not like ‘sdjk%’ then ‘sdjkapp’
else main_ord.order_source
end as channel
, main_ord.sub_order_source as sub_channel
,case when crm.order_no is not null then 1 else 0 end as is_crm_order
,case when ins_type.long_short_type is null then ord.is_long else ins_type.long_short_type end as is_long
,‘’ as agent_type
,‘’ as product_name
from (select *,
case when datediff(invalid_time, effect_time) > 366 then 1 else 0 end as is_long
from shuidi_sdb_crm.insurance_order
where status in (3,4,6,11,12,30) and valid=1 and order_type <> 1) ord
left join (select * from shuidi_sdb_crm.external_insurance_order )main_ord on ord.external_display_id = main_ord.display_id
left join shuidi_sdb_crm.sdb_crm_order crm on ord.order_no = crm.order_no
left join (select case term_type
when 2 then 0
when 3 then 0
when 1 then 1
end as long_short_type,
product_no
from shuidi_sdb_crm.sdb_product) ins_type on ord.base_product_no = ins_type.product_no ) t1 ;

语法有问题吧,把视图里面的select单独拉出来跑一下看看

语法问题,第52行和第53行有-,去掉试试。

1 个赞

去掉了,还是报这个错误:

拿出来跑没有问题

先把视图里的select跑一下

mysql> select case when channel like ‘kmbx%’ then ‘其他’
-> when is_long=1 and is_crm_order=1 then ‘网电销’
-> when is_long=0 or (is_long=1 and is_crm_order=0) then ‘平台业务’
-> end as first_order_channel_group,
-> case when channel like ‘kmbx%’ then ‘其他’
-> when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
-> when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
-> when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
-> when is_long=1 and is_crm_order=1 then ‘无职场’
-> when is_long=0 then ‘平台-短险’
-> when is_long=1 and is_crm_order=0 then ‘平台-长险’
-> end as sec_order_channel_group,
-> case when channel like ‘kmbx%’ then ‘其他’
-> when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
-> when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
-> when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
-> when is_long=1 and is_crm_order=1 then ‘无职场’
-> when is_long=1 and is_crm_order=0 and product_name = ‘e养添年养老年金保险’ then ‘长险-养老金’
-> when is_long=1 and is_crm_order=0 and channel like ‘sdbbtt%’ then ‘长险-投放’
-> when is_long=1 and is_crm_order=0 then ‘长险-其他’
-> when is_long=0 and parent_policy_no <> ‘’ then ‘短险-续保’
-> when is_long=0 and is_crm_order=1 then ‘短险-保’
-> when is_long=0 and channel in (‘cf’,‘jkbz’,‘cfjk’,‘cfjkgj’,‘sdcjk’,‘sdcjkgj’,‘jksh’,‘sdhz’) then ‘短险-筹’
-> when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) then ‘短险-投放’
-> else ‘短险-保’ end third_order_channel_group,
-> case when channel like ‘kmbx%’ then ‘其他’
-> when is_long=1 and is_crm_order=1 and agent_type=1 then ‘自建’
-> when is_long=1 and is_crm_order=1 and agent_type=2 then ‘共建’
-> when is_long=1 and is_crm_order=1 and agent_type=3 then ‘加盟’
-> when is_long=1 and is_crm_order=1 then ‘无职场’
-> when is_long=1 and is_crm_order=0 and product_name = ‘e养添年养老年金保险’ then ‘长险-养老金’
-> when is_long=1 and is_crm_order=0 and channel like ‘sdbbtt%’ then ‘长险-分销’
-> when is_long=1 and is_crm_order=0 then ‘长险-其他’
-> when is_long=0 and parent_policy_no <> ‘’ then ‘短险-续保’
-> when is_long=0 and is_crm_order = 1 then ‘电销’
-> when is_long=0 and channel in (‘cf’,‘jkbz’,‘cfjk’,‘cfjkgj’,‘sdcjk’,‘sdcjkgj’,‘jksh’,‘sdhz’) then ‘短险-筹’
-> when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) and channel like ‘sdbbtt%’ then ‘短险-分销’
-> when is_long=0 and (channel in (‘feed’,‘sem’) or channel like ‘BD%’ or channel like ‘sdbbtt%’) and (channel in (‘feed’,‘sem’) or channel like ‘BD%’) then ‘短险-H5’
-> when (channel like ‘%sdjk%’ or channel like ‘%sdik%’) then ‘健康’
-> when (channel=‘sdbaoapp’ or (platform in (3,4) and channel in (‘default_channel’,‘sdbapp’,‘sdbao’,‘sdbaoapp’))) then ‘保APP’
-> when (channel like ‘qxyy%’ or channel like ‘qywx%’) then ‘企信’
-> when channel like ‘mcn%’ then ‘内容获客’
-> when (sub_channel like ‘BA_NO_C1_Dx_back%’
-> or sub_channel LIKE ‘%ZH%’
-> or sub_channel LIKE ‘%BAM_NO_C1_zhaohuiMessages%’
-> or sub_channel LIKE ‘BA_NO_C1_MB%’
-> or sub_channel LIKE ‘BAM_NO_C1_m10%’
-> or sub_channel LIKE ‘BA_NO_C1_Messages%’
-> or sub_channel LIKE ‘BA_NO_C1_Template%’
-> or sub_channel LIKE ‘%BA_NO_C1_yuebaonei_0730%’
-> ) then ‘短信’
-> when channel like ‘sdbao%’ then ‘保公众号’
-> else ‘保公众号’ end as fourth_order_channel_group,
-> order_no,
-> policy_time,
-> origin,
-> is_crm_order
-> from
-> (select ord.order_no
-> ,ord.parent_policy_no
-> ,ord.platform
-> ,ord.policy_time
-> ,ord.origin
-> ,case
-> when date(ord.policy_time) >= ‘2020-09-01’ and ord.platform in (23, 24) and main_ord.order_source not like ‘sdjk%’ then ‘sdjkapp’
-> else main_ord.order_source
-> end as channel
-> , main_ord.sub_order_source as sub_channel
-> ,case when crm.order_no is not null then 1 else 0 end as is_crm_order
-> ,case when ins_type.long_short_type is null then ord.is_long else ins_type.long_short_type end as is_long
-> ,’’ as agent_type
-> ,’’ as product_name
-> from (select *,
-> case when datediff(invalid_time, effect_time) > 366 then 1 else 0 end as is_long
-> from shuidi_sdb_crm.insurance_order
-> where status in (3,4,6,11,12,30) and valid=1 and order_type <> 1) ord
-> left join (select * from shuidi_sdb_crm.external_insurance_order )main_ord on ord.external_display_id = main_ord.display_id
-> left join shuidi_sdb_crm.sdb_crm_order crm on ord.order_no = crm.order_no
-> left join (select case term_type
-> when 2 then 0
-> when 3 then 0
-> when 1 then 1
-> end as long_short_type,
-> product_no
-> from shuidi_sdb_crm.sdb_product) ins_type on ord.base_product_no = ins_type.product_no ) t1 limit 10;
±--------------------------±------------------------±--------------------------±---------------------------±------------------------±--------------------±----------±-------------+
| first_order_channel_group | sec_order_channel_group | third_order_channel_group | fourth_order_channel_group | order_no | policy_time | origin | is_crm_order |
±--------------------------±------------------------±--------------------------±---------------------------±------------------------±--------------------±----------±-------------+
| 平台业务 | 平台-短险 | 短险-投放 | 短险-H5 | SD158079978581278676298 | 2020-02-04 15:03:25 | 43750.00 | 0 |
| 平台业务 | 平台-短险 | 短险-投放 | 短险-H5 | SD158079979969330604469 | 2020-02-04 15:03:38 | 26700.00 | 0 |
| 平台业务 | 平台-短险 | 短险-投放 | 短险-H5 | SD158079979967565616293 | 2020-02-04 15:03:33 | 136150.00 | 0 |
| 平台业务 | 平台-短险 | 短险-投放 | 短险-H5 | SD158079979045525584338 | 2020-02-04 15:04:17 | 43750.00 | 0 |
| 平台业务 | 平台-短险 | 短险-投放 | 短险-H5 | SD158079977805693445486 | 2020-02-04 15:03:10 | 21200.00 | 0 |
| 平台业务 | 平台-短险 | 短险-保 | 保公众号 | SD158079977761771015305 | 2020-02-04 15:07:47 | 32200.00 | 0 |
| 平台业务 | 平台-短险 | 短险-保 | 保公众号 | SD158079979852116030098 | 2020-02-04 15:03:36 | 32200.00 | 0 |
| 平台业务 | 平台-短险 | 短险-保 | 保公众号 | SD158079978438615881346 | 2020-02-04 15:03:25 | 32200.00 | 0 |
| 网电销 | 无职场 | 无职场 | 无职场 | SD158079979641691439977 | 2020-02-04 15:04:49 | 133596.00 | 1 |
| 网电销 | 无职场 | 无职场 | 无职场 | SD158079979489251461803 | 2020-02-04 15:06:13 | 311580.00 | 1 |
±--------------------------±------------------------±--------------------------±---------------------------±------------------------±--------------------±----------±-------------+
10 rows in set (0.03 sec)

看起来像是已知的bug:https://github.com/pingcap/tidb/issues/25646
可以看下这个issue,现象应该是一致的

2 个赞

感谢,是这个BUG。 对内层子查询解析有问题,把里面的子查询换成视图就好了,视图引用视图绕过去。

2 个赞

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。