[jira] [Commented] (SPARK-21337) SQL which has large ‘case when’ expressions may cause code generation beyond 64KB

fengchaoge (JIRA) Sun, 09 Jul 2017 18:44:11 -0700

    [ 
https://issues.apache.org/jira/browse/SPARK-21337?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16079773#comment-16079773
 ]


fengchaoge commented on SPARK-21337:
------------------------------------

1. create database GBD_DM_PAC_SAFE;
2. use GBD_DM_PAC_SAFE;
3. create  table app_claim_assess_rule_granularity;
SQL like this,just  for test:

SELECT x___sql___.2jjg AS cjjg, x___sql___.3jjg AS djjg, (((CASE WHEN ((CASE 
WHEN (CASE WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) AS calculation_0290210162047568, x___sql___.updated_date AS 
calculation_0910125090644141, (CASE WHEN (x___sql___.small_type = '01') THEN 
'人工报价' ELSE (CASE WHEN (x___sql___.small_type = '02') THEN '指导人' ELSE 
x___sql___.small_type END) END) AS calculation_1700125090616887, 
x___sql___.impairment_amount AS calculation_1750125100625463, (CASE WHEN ((CASE 
WHEN (CASE WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) AS calculation_2170210160935298, (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
AS calculation_2390124154901057, (CASE WHEN (x___sql___.application_code = 
'DSFS') THEN '定损发送规则' ELSE x___sql___.application_code END) AS 
calculation_2770125090429540, x___sql___.rule_name AS 
calculation_3060125090537403, (CASE WHEN ((CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
< 10) THEN '暂不考虑规则' ELSE (CASE WHEN ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 13) THEN '最紧要优化规则' WHEN ((((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 12) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 22)) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 23)) THEN '次紧要优化规则' WHEN (((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 11) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 21)) THEN '一般需优化规则' ELSE '高效规则' END) END) AS 
calculation_3210210162219412, x___sql___.rule_code AS 
calculation_3220125090552536, (CASE WHEN x___sql___.sb = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.sb END) AS 
calculation_3590125102741790, x___sql___.sb AS calculation_3650125100815314, 
x___sql___.id_clm_channel_process AS calculation_3710125090518479, (CASE WHEN 
(CASE WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) AS calculation_4420125102328121, x___sql___.assess_um AS 
calculation_4630125134921574, x___sql___.privilege_group_name AS 
calculation_4950125094834941, x___sql___.report_no AS 
calculation_5530125090529367, x___sql___.jcbj AS calculation_6860125100718128, 
(CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS DOUBLE) ELSE 
x___sql___.impairment_amount / x___sql___.jcbj END) AS 
calculation_7280210161155142, (CASE WHEN ((CASE WHEN ((CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
< 10) THEN '暂不考虑规则' ELSE (CASE WHEN ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 13) THEN '最紧要优化规则' WHEN ((((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 12) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 22)) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 23)) THEN '次紧要优化规则' WHEN (((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 11) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 21)) THEN '一般需优化规则' ELSE '高效规则' END) END) = '最紧要优化规则') THEN 1 
WHEN ((CASE WHEN ((CASE WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 
WHEN NOT (x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS 
INT) END) < 10) THEN '暂不考虑规则' ELSE (CASE WHEN ((((CASE WHEN ((CASE WHEN (CASE 
WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 13) THEN '最紧要优化规则' WHEN ((((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 12) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 22)) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 23)) THEN '次紧要优化规则' WHEN (((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 11) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 21)) THEN '一般需优化规则' ELSE '高效规则' END) END) = '次紧要优化规则') THEN 2 
WHEN ((CASE WHEN ((CASE WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 
WHEN NOT (x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS 
INT) END) < 10) THEN '暂不考虑规则' ELSE (CASE WHEN ((((CASE WHEN ((CASE WHEN (CASE 
WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 13) THEN '最紧要优化规则' WHEN ((((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 12) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 22)) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 23)) THEN '次紧要优化规则' WHEN (((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 11) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 21)) THEN '一般需优化规则' ELSE '高效规则' END) END) = '一般需优化规则') THEN 3 
WHEN ((CASE WHEN ((CASE WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 
WHEN NOT (x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS 
INT) END) < 10) THEN '暂不考虑规则' ELSE (CASE WHEN ((((CASE WHEN ((CASE WHEN (CASE 
WHEN (x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 13) THEN '最紧要优化规则' WHEN ((((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 12) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 22)) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 23)) THEN '次紧要优化规则' WHEN (((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 11) OR ((((CASE WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.20000000000000001) THEN 1 WHEN ((CASE WHEN (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
= 0 THEN CAST(NULL AS DOUBLE) ELSE CAST(x___sql___.jcbj AS DOUBLE) / (CASE WHEN 
(x___sql___.id_clm_channel_process IS NULL) THEN 0 WHEN NOT 
(x___sql___.id_clm_channel_process IS NULL) THEN 1 ELSE CAST(NULL AS INT) END) 
END) < 0.5) THEN 2 ELSE 3 END) * 10) + (CASE WHEN ((CASE WHEN x___sql___.jcbj = 
0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj 
END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN CAST(NULL AS 
DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 2000) THEN 2 
ELSE 3 END)) = 21)) THEN '一般需优化规则' ELSE '高效规则' END) END) = '高效规则') THEN 5 ELSE 
4 END) AS calculation_8110210170832003, (CASE WHEN ((CASE WHEN x___sql___.jcbj 
= 0 THEN CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / 
x___sql___.jcbj END) < 300) THEN 1 WHEN ((CASE WHEN x___sql___.jcbj = 0 THEN 
CAST(NULL AS DOUBLE) ELSE x___sql___.impairment_amount / x___sql___.jcbj END) < 
2000) THEN 2 ELSE 3 END) AS calculation_8410210161919519, 
CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP) AS 
calculation_9280124151234671, x___sql___.application_code AS application_code, 
x___sql___.impairment_amount AS impairment_amount, x___sql___.jcbj AS jcbj, 
x___sql___.sb AS sb, x___sql___.small_type AS small_type FROM ( select 
z.report_no, z.id_clm_channel_process, z.updated_date, z.2jjg, z.3jjg, 
z.privilege_group_name, z.rule_code, z.rule_name, z.application_code, 
z.small_type,z.assess_um, sum(z.sb) sb, sum(z.sb-z.zz) impairment_amount, (case 
when sum(z.jcbj)>0 then 1 else 0 end ) jcbj from (select a.report_no, 
a.id_clm_channel_process, a.loss_object_no, a.case_times, a.assess_times, 
a.updated_date, a.end_case_date, (case when substr(a.ASSESS_DEPT_CODE,1,3) in 
('243','244') then '204' when substr(a.ASSESS_DEPT_CODE,1,3) = '247' then '212' 
when substr(a.ASSESS_DEPT_CODE,1,5) = '22412' then '251' else 
substr(a.ASSESS_DEPT_CODE,1,3) end) as 2jjg, a.ASSESS_DEPT_CODE 3jjg, 
a.department_code, a.privilege_group_name, a.assess_um, a.praepostor_type, 
a.small_type, a.channel_flag, a.loss_type, a.garage_code, a.garage_name, 
a.garage_type, a.car_mark, a.brand_name, a.manufacturer_name, a.series_name, 
a.group_name, a.model_name, a.loss_name, a.max_loss_amount sb, (case when 
a.loss_agree_amount is null then '0' else a.loss_agree_amount end) zz, 
a.loss_count_agree, a.impairment_amount, a.rule_code, a.rule_name, 
a.application_code, a.small_type, (case when 
(a.max_loss_amount-a.loss_agree_amount)>0 then 1 else 0 end) jcbj from (SELECT 
A.* FROM GBD_DM_PAC_SAFE.app_claim_assess_rule_granularity A WHERE 
A.department_code_02= '217' AND '2'='2' AND 'D3AD4E94222C326F' = 
'D3AD4E94222C326F' AND '车险理赔' = '车险理赔' AND '产险' = '产险' ) a where a.rule_name is 
not null) z group by z.report_no, z.id_clm_channel_process, z.updated_date, 
z.2jjg, z.3jjg, z.privilege_group_name, z.rule_code, z.rule_name, 
z.application_code, z.small_type, z.assess_um ) x___sql___ WHERE 
((x___sql___.2jjg = '217') AND 
((((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS 
TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170606 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170607 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170608 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170609 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170610 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170611 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170612 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170613 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170614 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170615 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170616 OR (((YEAR(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') 
AS TIMESTAMP)) * 10000) + (MONTH(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 
00:00:00') AS TIMESTAMP)) * 100)) + 
DAY(CAST(CONCAT(TO_DATE(x___sql___.updated_date),' 00:00:00') AS TIMESTAMP))) = 
20170617)) LIMIT 100;

> SQL which has large ‘case when’ expressions may cause code generation beyond 
> 64KB
> ---------------------------------------------------------------------------------
>
>                 Key: SPARK-21337
>                 URL: https://issues.apache.org/jira/browse/SPARK-21337
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 2.1.1
>         Environment: spark-2.1.1-hadoop-2.6.0-cdh-5.4.2
>            Reporter: fengchaoge
>             Fix For: 2.1.1
>
>
> when there are large 'case when ' expressions in spark sql,the CodeGenerator 
> failed to compile it. 
> Error message is followed by a huge dump of generated source code,at last 
> failed.
> java.util.concurrent.ExecutionException: java.lang.Exception: failed to 
> compile: org.codehaus.janino.JaninoRuntimeException: Code of method 
> &quot;apply_9$(Lorg/apache/spark/sql/catalyst/expressions/GeneratedClass$SpecificUnsafeProjection;Lorg/apache/spark/sql/catalyst/InternalRow;)V&quot;
>  of class 
> &quot;org.apache.spark.sql.catalyst.expressions.GeneratedClass$SpecificUnsafeProjection&quot;
>  grows beyond 64 KB.
> It seems that SPARK-13242 has solved this problem in spark-1.6.2,however it  
> apparence in spark-2.1.1 again. 
> https://issues.apache.org/jira/browse/SPARK-13242.
> is there something wrong ? 



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org

[jira] [Commented] (SPARK-21337) SQL which has large ‘case when’ expressions may cause code generation beyond 64KB

Reply via email to