a variable reference multiple times will cause error. about yanagishima HOT 4 CLOSED

Thank you for reporting.
Please wait next version.

from yanagishima.

I've just released 4.0.
Please check it out.

from yanagishima.

i've upgraded to 4.0 and the bug fix. thx

from yanagishima.

@lloydsheng @wyukawa
I didn't use yanagishima , I used presto-cli-executable.jar to execute query,but I had the same problem with @lloydsheng .
presto version:
0.175
JVM version (java -version):
1.8.0_131

./presto-cli-executable.jar --server node1:9500 --catalog hive
HQL:
INSERT INTO test.t_csc_debts_detail_tmp (
custom_no,
user_id,
cert_type,
cert_no,
custom_name,
channel_no,
loan_no,
thd_bus_id,
loan_code,
app_date,
product_id,
product_name,
loan_tnr,
tnr_type,
loan_usage,
contact_amt,
approve_amt,
drawn_amt,
prcp_int_amt,
ruppi_amt,
credit_grade,
drawn_date,
next_due_date,
last_due_date,
od_sts,
od_cnt,
year_od_cnt,
cur_od_cnt,
od_amt,
repayed_amt,
has_repayed_cnt,
app_sts,
end_cause,
repay_sts,
month_rate,
od_int_rate,
advisory_service_amt,
risk_amt_rate,
mobile,
repay_bank_card,
repay_open,
repay_branch,
repay_mobile,
used_rp,
rp_amt,
repay_loan_fee,
free_amt,
revs_ind,
revs_app_user,
revs_app_date,
revs_amt,
paying_process_sts,
paying_fail_reason,
province,
city,
district,
last_up_time,
last_up_user,
remark,
detail_md5,
loan_bank_card,
loan_open,
loan_branch,
loan_mobile,
sys_source,
loan_no_source
) SELECT
a.custom_no AS custom_no,
a.user_id AS user_id,
a.cert_type AS cert_type,
a.cert_no AS cert_no,
a.custom_name AS custom_name,
a.channel_no AS channel_no,
a.loan_no AS loan_no,
a.thd_bus_id AS thd_bus_id,
a.loan_code AS loan_code,
a.app_date AS app_date,
a.product_id AS product_id,
a.product_name AS product_name,
a.loan_tnr AS loan_tnr,
a.tnr_type AS tnr_type,
a.loan_usage AS loan_usage,
a.contact_amt AS contact_amt,
a.approve_amt AS approve_amt,
a.drawn_amt AS drawn_amt,
a.prcp_int_amt AS prcp_int_amt,
a.ruppi_amt AS ruppi_amt,
a.credit_grade AS credit_grade,
a.drawn_date AS drawn_date,
a.next_due_date AS next_due_date,
a.last_due_date AS last_due_date,
a.od_sts AS od_sts,
a.od_cnt AS od_cnt,
a.year_od_cnt AS year_od_cnt,
a.cur_od_cnt AS cur_od_cnt,
a.od_amt AS od_amt,
a.repayed_amt AS repayed_amt,
a.has_repayed_cnt AS has_repayed_cnt,
a.app_sts AS app_sts,
a.end_cause AS end_cause,
a.repay_sts AS repay_sts,
a.month_rate AS month_rate,
a.od_int_rate AS od_int_rate,
a.advisory_service_amt AS advisory_service_amt,
a.risk_amt_rate AS risk_amt_rate,
a.mobile AS mobile,
a.repay_bank_card AS repay_bank_card,
a.repay_open AS repay_open,
a.repay_branch AS repay_branch,
a.repay_mobile AS repay_mobile,
a.used_rp AS used_rp,
a.rp_amt AS rp_amt,
a.repay_loan_fee AS repay_loan_fee,
a.free_amt AS free_amt,
a.revs_ind AS revs_ind,
a.revs_app_user AS revs_app_user,
a.revs_app_date AS revs_app_date,
a.revs_amt AS revs_amt,
a.paying_process_sts AS paying_process_sts,
a.paying_fail_reason AS paying_fail_reason,
a.province AS province,
a.city AS city,
a.district AS district,
a.last_up_time AS last_up_time,
a.last_up_user AS last_up_user,
a.remark AS remark,
md5(
concat(
COALESCE (concat(ruppi_amt, ''), ''),
COALESCE (next_due_date, ''),
COALESCE (od_sts, ''),
COALESCE (od_cnt, ''),
COALESCE (year_od_cnt, ''),
COALESCE (cur_od_cnt, ''),
COALESCE (concat(od_amt, ''), ''),
COALESCE (concat(repayed_amt, ''), ''),
COALESCE (has_repayed_cnt, '')
)
) AS detail_md5,
a.loan_bank_card AS loan_bank_card,
a.loan_open AS loan_open,
a.loan_branch AS loan_branch,
a.loan_mobile AS loan_mobile,
1 AS sys_source,
concat('1', '_', a.loan_no) loan_no_source
FROM
(
SELECT
a.UUM_CUST_NO AS custom_no,
a.UUM_USER_ID AS user_id,
'B1301' AS cert_type,
a.CERT_ID AS cert_no,
a.CUSTOMER_NAME AS custom_name,
a.SALE_CHANNEL AS channel_no,
a.app_id AS loan_no,
a.old_app_id AS thd_bus_id,
a.LOAN_CODE AS loan_code,
a.APPAY_DATE AS app_date,
a.PRODUCT_ID AS product_id,
a.PRODUCT_NAME AS product_name,
d.loan_tnr AS loan_tnr,
'M' AS tnr_type,
a.LOAN_PURPOSE AS loan_usage,
a.CONTRACT_AMT AS contact_amt,
a.APPROVE_AMT AS approve_amt,
a.DRAWN_AMT AS drawn_amt,
d.PRINCIPAL_INTEREST AS prcp_int_amt,
d.ruppi_amt AS ruppi_amt,
a.CUSTOMER_LEVEL AS credit_grade,
a.LOAN_DATE AS drawn_date,
b.next_repay_date AS next_due_date,
b.last_due_date AS last_due_date,
b.is_overdue AS od_sts,
b.od_cnt AS od_cnt,
b.od_cnt_year AS year_od_cnt,
b.cur_overdue_count AS cur_od_cnt,
b.cur_overdue_pi AS od_amt,
b.real_repay_pi AS repayed_amt,
b.has_repayed_cnt AS has_repayed_cnt,
a.APP_STATUS AS app_sts,
a.END_REASON AS end_cause,
a.REPAY_STATUS AS repay_sts,
a.month_rate * 100 AS month_rate,
a.EXCEED_RATE * 100 AS od_int_rate,
a.advisory_service_amt AS advisory_service_amt,
a.risk_amt_rate * 100 AS risk_amt_rate,
a.PHONE AS mobile,
a.repay_bank_card AS repay_bank_card,
a.repay_open AS repay_open,
a.repay_branch AS repay_branch,
NULL AS repay_mobile,
CASE
WHEN pt.app_id IS NOT NULL
THEN 1
ELSE 0
END AS used_rp,
pt.TRANSFER_AMT AS rp_amt,
a.RISK_AMT + a.SERVICE_AMT AS repay_loan_fee,
NULL AS free_amt,
CASE
WHEN tm.app_id IS NOT NULL
THEN 1
ELSE 0
END AS revs_ind,
tm.REVS_REASON AS revs_app_user,
tm.LAST_UP_TM AS revs_app_date,
CASE
WHEN tm.app_id IS NOT NULL
THEN a.CONTRACT_AMT
ELSE 0.00
END AS revs_amt,
lt.PROC_STS AS paying_process_sts,
lw.REMARK AS paying_fail_reason,
concat(substr(a.CERT_ID, 1, 2),'0000') AS province,
concat(substr(a.CERT_ID, 1, 4), '00') AS city,
substr(a.CERT_ID, 1, 6) AS district,
from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') AS last_up_time,
NULL AS last_up_user,
NULL AS remark,
a.RECEIVE_BANK_CARD AS loan_bank_card,
a.RECEIVE_OPEN AS loan_open,
a.RECEIVE_BRANCH AS loan_branch,
NULL AS loan_mobile
FROM
bd_cac.xd_t_lon_application a
LEFT JOIN (
SELECT lt.app_id,lt.proc_sts FROM bd_cac.xd_t_loan_trans_info lt JOIN
(
SELECT app_id, max(LTI_SEQ) AS LTI_SEQ FROM bd_cac.xd_t_loan_trans_info GROUP BY app_id
) lt1 ON lt.app_id = lt1.app_id AND lt.LTI_SEQ = lt1.LTI_SEQ
) lt ON a.app_id = lt.app_id
LEFT JOIN bd_cac.xd_t_loan_withdraw_amt lw ON a.app_id = lw.app_id
LEFT JOIN (
SELECT t.app_id, t.REVS_REASON,t.LAST_UP_TM FROM bd_cac.xd_t_mls_loan_revs_log t JOIN
(
SELECT t.app_id, max(LAST_UP_TM) LAST_UP_TM FROM bd_cac.xd_t_mls_loan_revs_log t GROUP BY t.app_id
) d ON t.LAST_UP_TM = d.LAST_UP_TM AND t.app_id = d.app_id
) tm ON a.app_id = tm.app_id
LEFT JOIN (
SELECT t.app_id, sum(TRANSFER_AMT) TRANSFER_AMT FROM bd_cac.xd_t_als_red_packet_transfer t
WHERE t.STRIKE_STATUS = 'Y' AND t.REMARK = '红包转账'
GROUP BY t.app_id
) pt ON a.app_id = pt.app_id
JOIN (
SELECT
rpp.app_id,
sum(
CASE
WHEN rpp.REPAY_STATUS = 'F3202'
AND rpp.PLAN_REPAY_DATE <= '2017-09-12'
AND (
rpp.REPAY_DATE IS NULL
OR rpp.REPAY_DATE = ''
)
THEN
(PRINCIPAL + INTEREST) - (REPAY_PRINCIPAL + REPAY_INTEREST)
ELSE
0
END
) AS cur_overdue_pi,
count(
CASE
WHEN rpp.REPAY_STATUS = 'F3202'
AND rpp.PLAN_REPAY_DATE <= '2017-09-12'
AND (
rpp.REPAY_DATE IS NULL
OR rpp.REPAY_DATE = ''
)
THEN
DATEDIFF(DATE_ADD('2017-09-12',1),rpp.PLAN_REPAY_DATE)
END
) AS cur_overdue_count,
count(
CASE
WHEN rpp.REPAY_STATUS = 'F3202'
AND rpp.PLAN_REPAY_DATE <= '2017-09-12'
AND (
rpp.REPAY_DATE IS NULL
OR rpp.REPAY_DATE = ''
)
THEN
DATEDIFF(DATE_ADD('2017-09-12',1),rpp.PLAN_REPAY_DATE)
WHEN rpp.REPAY_STATUS = 'F3201'
AND rpp.PLAN_REPAY_DATE < rpp.REPAY_DATE
AND rpp.REPAY_DATE <= '2017-09-12'
THEN
DATEDIFF(rpp.REPAY_DATE,rpp.PLAN_REPAY_DATE)
WHEN rpp.REPAY_STATUS = 'F3201'
AND rpp.PLAN_REPAY_DATE < rpp.REPAY_DATE
AND rpp.PLAN_REPAY_DATE <= '2017-09-12'
AND '2017-09-12' <= rpp.REPAY_DATE
THEN
DATEDIFF(DATE_ADD('2017-09-12',1),rpp.PLAN_REPAY_DATE)
END
) AS od_cnt,
count(
CASE
WHEN rpp.REPAY_STATUS = 'F3202'
AND (( YEAR ('2017-09-12') - YEAR (rpp.PLAN_REPAY_DATE)) * 12) + (MONTH ('2017-09-12') - MONTH (rpp.PLAN_REPAY_DATE)) < 12
AND rpp.PLAN_REPAY_DATE <= '2017-09-12'
AND (rpp.REPAY_DATE IS NULL OR rpp.REPAY_DATE = '')
THEN
DATEDIFF(DATE_ADD('2017-09-12',1),rpp.PLAN_REPAY_DATE)
WHEN rpp.REPAY_STATUS = 'F3201'
AND ((YEAR ('2017-09-12') - YEAR (rpp.PLAN_REPAY_DATE)) * 12) + (MONTH ('2017-09-12') - MONTH (rpp.PLAN_REPAY_DATE)) < 12
AND rpp.PLAN_REPAY_DATE < rpp.REPAY_DATE
AND rpp.REPAY_DATE <= '2017-09-12'
THEN
DATEDIFF(rpp.REPAY_DATE,rpp.PLAN_REPAY_DATE)
WHEN rpp.REPAY_STATUS = 'F3201'
AND ((YEAR ('2017-09-12') - YEAR (rpp.PLAN_REPAY_DATE)) * 12) + (MONTH ('2017-09-12') - MONTH (rpp.PLAN_REPAY_DATE)) < 12
AND rpp.PLAN_REPAY_DATE < rpp.REPAY_DATE
AND rpp.PLAN_REPAY_DATE <= '2017-09-12'
AND '2017-09-12' <= rpp.REPAY_DATE
THEN
DATEDIFF(DATE_ADD('2017-09-12',1),rpp.PLAN_REPAY_DATE)
END
) AS od_cnt_year,
CASE
WHEN sum(
CASE
WHEN rpp.REPAY_STATUS == 'F3202' AND rpp.PLAN_REPAY_DATE <= '2017-09-12'AND (rpp.REPAY_DATE IS NULL OR rpp.REPAY_DATE = '')
THEN
DATEDIFF(DATE_ADD('2017-09-12',1),rpp.PLAN_REPAY_DATE)
WHEN rpp.REPAY_STATUS = 'F3201'
AND rpp.PLAN_REPAY_DATE < rpp.REPAY_DATE
AND rpp.REPAY_DATE <= '2017-09-12'
THEN
DATEDIFF(rpp.REPAY_DATE,rpp.PLAN_REPAY_DATE)
WHEN rpp.REPAY_STATUS = 'F3201'
AND rpp.PLAN_REPAY_DATE < rpp.REPAY_DATE
AND rpp.PLAN_REPAY_DATE <= '2017-09-12'
AND '2017-09-12' <= rpp.REPAY_DATE
THEN
DATEDIFF(DATE_ADD('2017-09-12',1),rpp.PLAN_REPAY_DATE)
ELSE
0
END
) > 0
THEN 1
ELSE 0
END AS is_overdue,
count(
CASE
WHEN rpp.REPAY_STATUS = 'F3201'
THEN 1
END
) AS has_repayed_cnt,
min(
CASE
WHEN substr(rpp.PLAN_REPAY_DATE, 1, 10) > '2017-09-12' AND rpp.REPAY_STATUS = 'F3202'
THEN
PLAN_REPAY_DATE
END
) AS next_repay_date,
sum(REPAY_PRINCIPAL + REPAY_INTEREST) AS real_repay_pi,
max(rpp.PLAN_REPAY_DATE) AS last_due_date
FROM bd_cac.xd_t_rep_repayment rpp GROUP BY app_id
) b ON a.app_id = b.app_id
JOIN (
SELECT app_id,max(stage + 0) AS loan_tnr,max(PLAN_REPAY_DATE) AS last_repay_date,
sum(PRINCIPAL) + sum(INTEREST) AS PRINCIPAL_INTEREST,
sum(PRINCIPAL) + sum(INTEREST) - sum(REPAY_PRINCIPAL) - sum(REPAY_INTEREST) AS ruppi_amt
FROM bd_cac.xd_t_rep_repayment GROUP BY app_id
) d ON a.app_id = d.app_id
WHERE
length(a.CERT_ID) > 6
GROUP BY
a.UUM_CUST_NO,
a.UUM_USER_ID,
'B1301',
a.CERT_ID,
a.CUSTOMER_NAME,
a.SALE_CHANNEL,
a.app_id,
a.old_app_id,
a.LOAN_CODE,
a.APPAY_DATE,
a.PRODUCT_ID,
a.PRODUCT_NAME,
d.loan_tnr,
'M',
a.LOAN_PURPOSE,
a.CONTRACT_AMT,
a.APPROVE_AMT,
a.DRAWN_AMT,
d.PRINCIPAL_INTEREST,
d.ruppi_amt,
a.CUSTOMER_LEVEL,
a.LOAN_DATE,
b.next_repay_date,
b.last_due_date,
b.is_overdue,
b.od_cnt,
b.od_cnt_year,
b.cur_overdue_count,
b.cur_overdue_pi,
b.real_repay_pi,
b.has_repayed_cnt,
a.APP_STATUS,
a.END_REASON,
a.repay_status,
a.month_rate,
a.EXCEED_RATE,
a.advisory_service_amt,
a.risk_amt_rate,
a.PHONE,
a.repay_bank_card,
a.repay_open,
a.repay_branch,
pt.app_id,
pt.TRANSFER_AMT,
a.RISK_AMT,
a.SERVICE_AMT,
tm.app_id,
tm.REVS_REASON,
tm.LAST_UP_TM,
lt.PROC_STS,
lw.REMARK,
a.RECEIVE_BANK_CARD,
a.RECEIVE_OPEN,
a.RECEIVE_BRANCH
) a;

in WHEN rpp.REPAY_STATUS = 'F3202' , prompt me as following:
Query .....failed : line 250:38 mismatched input 'F3202' expecting { '.', ')','[','GROUP','HAVING','LIMIT','AT','OR','UNION','EXCEPT','INTERSECT','+','-','*','/','%','||' }

But, when I pick out part of this statement to execute ,presto executed successfully.
ie:
SELECT sum(
CASE
WHEN rpp.REPAY_STATUS = 'F3202'
AND rpp.PLAN_REPAY_DATE <= '2017-09-12'
AND (
rpp.REPAY_DATE IS NULL
OR rpp.REPAY_DATE = ''
)
THEN
(PRINCIPAL + INTEREST) - (REPAY_PRINCIPAL + REPAY_INTEREST)
ELSE
0
END
) AS cur_overdue_pi from bd_cac.xd_t_rep_repayment rpp

I guess the reason is the Coordinator can't parse such a complicated statement.
How to solve this problem?

from yanagishima.