Dear All,
for the later versions impala or new version odbc for impala ? If new version for impala have a big problem for deploy on my produciton!!!! Best Regards, Siksit Santirojanakul (Eak) IT - CIS Customer Delivery / Campaign True Info Tech Co., Ltd. Tel. 02-699-7428 From: Tim Armstrong <tarmstr...@cloudera.com> To: user@impala.incubator.apache.org Cc: u...@impala.apache.org, user-subscr...@impala.incubator.apache.org Date: 24/09/2016 06:46 Subject: Re: COUNT(DISTINCT col) returns wrong results: Impala ODBC: Error It's probably one of the multiple bugs along this line that have been fixed in later versions of Impala. E.g. https://issues.cloudera.org/browse/IMPALA-2216?filter=11799 On Thu, Sep 22, 2016 at 8:44 PM, <siksit_...@truecorp.co.th> wrote: retry Best Regards, Siksit Santirojanakul (Eak) IT - CIS Customer Delivery / Campaign True Info Tech Co., Ltd. Tel. 02-699-7428 From: Siksit Santirojanakul/True Corp To: user@impala.incubator.apache.org Date: 21/09/2016 14:54 Subject: Fw: COUNT(DISTINCT col) returns wrong results: Impala ODBC: Error Dear All, Have problem from impala Impala 2.2.2 , odbc driver for Impala 2.05.28.1008. Error msg "Exception: DBD[Cloudera][ImpalaODBC] (110) Error while excuting a query in Impala: [HY000]: illegalState: Illegal reference to no-materialized slot: tid=0 sid=5 State: S1000" My Sql Query: with a1 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE , COUNT(DISTINCT L.AUDIENCEKEY) AS TOTAL_MSISDN , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0 END) AS TOPUP_COUNT_20 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0 END) AS TOPUP_COUNT_30 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0 END) AS TOPUP_COUNT_50 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0 END) AS TOPUP_COUNT_100 , SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_COUNT , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_20 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_30 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_50 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT_100 , SUM(CASE WHEN CDR_TYPE = 1 THEN TOPUP_AMOUNT ELSE 0 END) AS TOPUP_AMOUNT , SUM(CASE WHEN CDR_TYPE = 2 THEN 1 ELSE 0 END) AS FIRSTCALL_COUNT , COUNT(*) AS LUCKYCODE_COUNT , SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0 END) AS REDEEM_TRAN_20 , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0 END) AS REDEEM_TRAN_30 , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0 END) AS REDEEM_TRAN_50 , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0 END) AS REDEEM_TRAN_100 , SUM(CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 THEN 1 ELSE 0 END) AS REDEEM_TRAN , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN 1 ELSE 0 END) AS TOPUP_TRAN_20 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN 1 ELSE 0 END) AS TOPUP_TRAN_30 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN 1 ELSE 0 END) AS TOPUP_TRAN_50 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN 1 ELSE 0 END) AS TOPUP_TRAN_100 , SUM(CASE WHEN CDR_TYPE = 1 THEN 1 ELSE 0 END) AS TOPUP_TRAN , SUM(CASE WHEN trim(PROVISION_ID) <> '' THEN 1 ELSE 0 END) AS REDEEM_COUNT , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_20 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_30 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_50 , SUM(CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN BONUS_AMOUNT ELSE 0 END) AS BONUS_AMOUNT_100 , SUM(CASE WHEN CDR_TYPE = 1 THEN BONUS_AMOUNT ELSE 0 END) AS TOTAL_BONUS_AMOUNT , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND F.PROVISIONPARAM4 = '20'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_20 , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND F.PROVISIONPARAM4 = '30'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_30 , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND F.PROVISIONPARAM4 = '50'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_50 , SUM(CASE WHEN CDR_TYPE = 1 AND trim(PROVISION_ID) <> '' AND F.PROVISIONPARAM4 = '100'THEN BONUS_AMOUNT ELSE 0 END) AS REDEEM_AMOUNT_100 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a2 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 THEN L.AUDIENCEKEY ELSE NULL END) AS TOPUP_MSISDN FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a3 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' THEN L.AUDIENCEKEY ELSE '' END) AS REDEEM_MSISDN FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a4 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN L.AUDIENCEKEY ELSE null END) AS REDEEM_MSISDN_20 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a5 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN L.AUDIENCEKEY ELSE null END) AS REDEEM_MSISDN_30 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a6 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN L.AUDIENCEKEY ELSE null END) AS REDEEM_MSISDN_50 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a7 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN trim(PROVISION_ID) <> '' AND CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN L.AUDIENCEKEY ELSE null END) AS REDEEM_MSISDN_100 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a8 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '20' THEN L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_20 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a9 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '30' THEN L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_30 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a10 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '50' THEN L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_50 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) ,a11 as ( SELECT TO_DATE(L.INSERT_DATE) AS LUCKY_DATE ,COUNT(DISTINCT CASE WHEN CDR_TYPE = 1 AND F.PROVISIONPARAM4 = '100' THEN L.AUDIENCEKEY ELSE null END) AS TOPUP_MSISDN_100 FROM unica.BM_LUCKYDRAW L INNER JOIN unica.BM_FULFILLMENT F ON F.TREATMENTCODE = L.TREATMENTCODE AND F.AUDIENCELEVEL = L.AUDIENCELEVEL AND F.AUDIENCEKEY = L.AUDIENCEKEY WHERE L.CAMPAIGNCODE IN('C000000301') GROUP BY TO_DATE(INSERT_DATE) ) SELECT COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) as LUCKY_DATE, COALESCE(a12.TOTAL_REGISTED, last_value(a12.TOTAL_REGISTED) OVER (order by COALESCE(a1.LUCKY_DATE, a12.LUCKY_DATE) ASC)) AS TOTAL_REGISTED, COALESCE(a12.TODAY_REGISTED, 0) AS TODAY_REGISTED, COALESCE(a1.TOTAL_MSISDN, 0) AS TOTAL_MSISDN, COALESCE(a1.TOPUP_AMOUNT_20, 0) AS TOPUP_AMOUNT_20,COALESCE(a1.TOPUP_AMOUNT_30, 0) AS TOPUP_AMOUNT_30, COALESCE(a1.TOPUP_AMOUNT_50, 0) AS TOPUP_AMOUNT_50, COALESCE(a1.TOPUP_AMOUNT_100, 0) AS TOPUP_AMOUNT_100, COALESCE(a1.TOPUP_AMOUNT, 0) AS TOPUP_AMOUNT, COALESCE(a1.FIRSTCALL_COUNT, 0) AS FIRSTCALL_COUNT, COALESCE(a1.LUCKYCODE_COUNT, 0) AS LUCKYCODE_COUNT, COALESCE(a1.BONUS_AMOUNT_20, 0) AS BONUS_AMOUNT_20,COALESCE(a1.BONUS_AMOUNT_30, 0) AS BONUS_AMOUNT_30,COALESCE(a1.BONUS_AMOUNT_50, 0) AS BONUS_AMOUNT_50, COALESCE(a1.BONUS_AMOUNT_100, 0) AS BONUS_AMOUNT_100, COALESCE(a1.TOTAL_BONUS_AMOUNT, 0) AS TOTAL_BONUS_AMOUNT, COALESCE(a1.REDEEM_AMOUNT_20, 0) AS REDEEM_AMOUNT_20,COALESCE(a1.REDEEM_AMOUNT_30, 0) AS REDEEM_AMOUNT_30, COALESCE(a1.REDEEM_AMOUNT_50, 0) AS REDEEM_AMOUNT_50, COALESCE(a1.REDEEM_AMOUNT_100, 0) AS REDEEM_AMOUNT_100, COALESCE(a1.REDEEM_AMOUNT, 0) AS REDEEM_AMOUNT, COALESCE(a4.REDEEM_MSISDN_20, 0) AS REDEEM_MSISDN_20,COALESCE(a5.REDEEM_MSISDN_30, 0) AS REDEEM_MSISDN_30,COALESCE(a6.REDEEM_MSISDN_50, 0) AS REDEEM_MSISDN_50,COALESCE(a7.REDEEM_MSISDN_100, 0) AS REDEEM_MSISDN_100, COALESCE(a3.REDEEM_MSISDN, 0) AS REDEEM_MSISDN, COALESCE(a1.REDEEM_TRAN_20, 0) AS REDEEM_TRAN_20,COALESCE(a1.REDEEM_TRAN_30, 0) AS REDEEM_TRAN_30,COALESCE(a1.REDEEM_TRAN_50, 0) AS REDEEM_TRAN_50,COALESCE(a1.REDEEM_TRAN_100, 0) AS REDEEM_TRAN_100, COALESCE(a1.REDEEM_TRAN, 0) AS REDEEM_TRAN, COALESCE(a1.TOPUP_COUNT_20, 0) AS TOPUP_COUNT_20,COALESCE(a1.TOPUP_COUNT_30, 0) AS TOPUP_COUNT_30,COALESCE(a1.TOPUP_COUNT_50, 0) AS TOPUP_COUNT_50,COALESCE(a1.TOPUP_COUNT_100, 0) AS TOPUP_COUNT_100, COALESCE(a1.TOPUP_COUNT, 0) AS TOPUP_COUNT, COALESCE(a8.TOPUP_MSISDN_20, 0) AS TOPUP_MSISDN_20,COALESCE(a9.TOPUP_MSISDN_30, 0) AS TOPUP_MSISDN_30,COALESCE(a10.TOPUP_MSISDN_50, 0) AS TOPUP_MSISDN_50,COALESCE(a11.TOPUP_MSISDN_100, 0) AS TOPUP_MSISDN_100, COALESCE(a2.TOPUP_MSISDN, 0) AS TOPUP_MSISDN, COALESCE(a1.TOPUP_TRAN_20, 0) AS TOPUP_TRAN_20, COALESCE(a1.TOPUP_TRAN_30, 0) AS TOPUP_TRAN_30, COALESCE(a1.TOPUP_TRAN_50, 0) AS TOPUP_TRAN_50, COALESCE(a1.TOPUP_TRAN_100, 0) AS TOPUP_TRAN_100, COALESCE(a1.TOPUP_TRAN , 0) AS TOPUP_TRAN, COALESCE(a13.TODAY_REGISTED_SUCC, 0) TODAY_REGISTED_SUCC, COALESCE(a14.REDEEM_SUCC, 0 ) AS REDEEM_SUCC, COALESCE(a14.REDEEM_FAIL, 0 ) AS REDEEM_FAIL, COALESCE(a15.sum_input, 0 ) aS SUM_INPUT, COALESCE(a15.sum_output, 0 ) aS sum_output from a1 FULL OUTER JOIN unica.v_Today_Reg a12 on a1.LUCKY_DATE = a12.LUCKY_DATE FUll OUTER JOIN unica.v_Today_Reg_Succ a13 on a1.LUCKY_DATE= a13.LUCKY_DATE FUll OUTER JOIN unica.V_Redeem a14 on a1.LUCKY_DATE= a14.LUCKY_DATE FULL JOIN unica.V_Eligible a15 on a1.LUCKY_DATE = a15.PROCESS_DATE FULL JOIN a2 on a1.LUCKY_DATE = a2.LUCKY_DATE FULL JOIN a3 on a1.LUCKY_DATE = a3.LUCKY_DATE FULL JOIN a4 on a1.LUCKY_DATE = a4.LUCKY_DATE FULL JOIN a5 on a1.LUCKY_DATE = a5.LUCKY_DATE FULL JOIN a6 on a1.LUCKY_DATE = a6.LUCKY_DATE FULL JOIN a7 on a1.LUCKY_DATE = a7.LUCKY_DATE FULL JOIN a8 on a1.LUCKY_DATE = a8.LUCKY_DATE FULL JOIN a9 on a1.LUCKY_DATE = a9.LUCKY_DATE FULL JOIN a10 on a1.LUCKY_DATE = a10.LUCKY_DATE FULL JOIN a11 on a1.LUCKY_DATE = a11.LUCKY_DATE where cast(a1.LUCKY_DATE as timestamp) = cast('2016-09-09' as timestamp) Best Regards, Siksit Santirojanakul (Eak) IT - CIS Customer Delivery / Campaign True Info Tech Co., Ltd. Tel. 02-699-7428 Important Confidentiality: This Information is intended for the above-named person and may contain confidential and/or legally privileged material. Any opinions expressed in this information are not necessarily those of the company. If it has come to you in error you must take no action based on it, nor must you copy or show it to anyone; please delete/destroy and inform the sender immediately. Monitoring/Viruses True Corporation and subsidiaries reserves the right to monitor all incoming and outgoing emails via True Corporation and subsidiaries's systems. Although we have security program to monitor and eliminate virus, we also advise that in keeping with good computing practice the recipient should ensure they are actually virus free. Important Confidentiality: This Information is intended for the above-named person and may contain confidential and/or legally privileged material. Any opinions expressed in this information are not necessarily those of the company. If it has come to you in error you must take no action based on it, nor must you copy or show it to anyone; please delete/destroy and inform the sender immediately. Monitoring/Viruses True Corporation and subsidiaries reserves the right to monitor all incoming and outgoing emails via True Corporation and subsidiaries's systems. Although we have security program to monitor and eliminate virus, we also advise that in keeping with good computing practice the recipient should ensure they are actually virus free.
