[ https://issues.apache.org/jira/browse/HIVE-25220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Krisztian Kasa updated HIVE-25220: ---------------------------------- Description: {code} explain cbo with meod AS (select max(data_as_of_date) data_as_of_date from governed.cc_forecast_pnl), daod as (select min(f.cob_date) data_as_of_date from governed.cc_forecast_pnl f, meod where f.data_as_of_date = meod.data_as_of_date), me_rates as ( SELECT refRateFX.to_currency_code, refRateFX.from_currency_code, cast(refRateFX.exchange_rate as decimal(38,18)) exchange_rate, cast('GC2' AS string) currency_label FROM (SELECT MAX(fx.data_as_of_date) data_as_of_date FROM governed.standard_fx_rates fx, daod WHERE fx.data_as_of_date LIKE '%_MCR_MTD' and fx.data_as_of_date <= concat(daod.data_as_of_date, '_MCR_MTD')) fx, -- get most recent rates not later than the delivery period governed.standard_fx_rates refRateFX WHERE refRateFX.data_as_of_date = fx.data_as_of_date AND refRateFX.to_currency_code = 'USD' UNION ALL SELECT refRateFX2.from_currency_code to_currency_code, refRateFX1.from_currency_code, cast(cast(refRateFX1.exchange_rate as double)/cast(refRateFX2.exchange_rate as double) as decimal(38,18)) exchange_rate, CAST('GC1' AS string) currency_label FROM (SELECT MAX(fx.data_as_of_date) data_as_of_date FROM governed.standard_fx_rates fx, daod WHERE fx.data_as_of_date LIKE '%_MCR_MTD' and fx.data_as_of_date <= concat(daod.data_as_of_date, '_MCR_MTD')) fx, -- get most recent rates not later than the delivery period governed.standard_fx_rates refRateFX1, governed.standard_fx_rates refRateFX2 WHERE refRateFX1.data_as_of_date = fx.data_as_of_date AND refRateFX2.data_as_of_date = fx.data_as_of_date AND refRateFX1.to_currency_code = 'USD' AND refRateFX2.from_currency_code = 'CHF' AND refRateFX2.to_currency_code = 'USD' ), cc_func_hier_filter as( SELECT DISTINCT LEVEL10 FUNCTION_CD FROM GOVERNED.CC_CYBOS_HIER_FUNCTION WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM GOVERNED.CC_CYBOS_HIER_FUNCTION) AND LEVEL2='N14954' ), cc_unified_acc_hier_filter as( SELECT DISTINCT LEVEL14 GROUP_ACCOUNT_CD FROM governed.cc_cybos_hier_acct WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM governed.cc_cybos_hier_acct) AND LEVEL1='U00000' AND LEVEL6 = 'U52000' ), cc_sign_reversal as( SELECT DISTINCT LEVEL14 GROUP_ACCOUNT_CD, CAST(-1 AS DECIMAL(38,18)) reverse_sign FROM governed.cc_cybos_hier_acct WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM governed.cc_cybos_hier_acct) AND ((LEVEL1='U00000' AND LEVEL5 = 'U30175') OR (LEVEL2 = 'EAR90006')) ), cc_unified_acc_hier as( SELECT DISTINCT TRIM(level14) level14 FROM provision.cc_hier_unified_acct_vw WHERE level5_desc = 'Total operating expense' AND TRIM(level14) NOT IN (SELECT group_account_cd FROM governed.cc_temp_reg_exclude_rules WHERE data_as_of_date IN (SELECT MAX(data_as_of_date) from governed.cc_temp_reg_exclude_rules)) ), tempreg as( SELECT function_cd, tt_cd FROM governed.cc_temp_reg_rules WHERE data_as_of_date IN (SELECT MAX(data_as_of_date) FROM governed.cc_temp_reg_rules) ), gov as( select cob_date, count(*) as gov_count, sum(case when measure_amt <> 0 then 1 else 0 end) gov_non_zero_count, sum(MEASURE_AMT) as gov_amt from ( select pnl.cob_date, CASE WHEN tr.function_cd IS NOT NULL AND h.level14 IS NOT NULL THEN tr.TT_CD ELSE NULL END AS PERFORMANCE_VIEW_TYPE, pnl.company_code, pnl.function_code, pnl.group_account_code, pnl.gaap_code, 'Actual Rate' AS CURRENCY_TYPE, me.to_currency_code AS CURRENCY_CODE, pnl.group_account_code MEASURE_ID, sum(CAST(cast((cast(pnl.posting_lc_amt as double) * cast(NVL(sr.reverse_sign, 1) as double)) as double) * cast(me.exchange_rate as double) as decimal(38,18))) as MEASURE_AMT, 'FORECAST' AS PROJECTION_TYPE, CASE WHEN GROUP_ACCOUNT_CODE LIKE 'EAR%' THEN 'RETAINED_EARNINGS' ELSE 'PNL' END AS MACRO_MEASURE, me.currency_label AS MACRO_MEASURE_SUB_TYPE, pnl.cob_date AS partition_date_key from governed.cc_forecast_pnl pnl, me_rates me left outer join cc_func_hier_filter fHier on pnl.function_code = fHier.FUNCTION_CD left outer join cc_unified_acc_hier_filter aHier on pnl.group_account_code = aHier.group_account_cd left outer join cc_sign_reversal sr on pnl.group_account_code = sr.group_account_cd left outer join tempreg tr on pnl.function_code = tr.function_cd left outer join cc_unified_acc_hier h on pnl.group_account_code = h.level14 WHERE me.from_currency_code = (CASE WHEN pnl.local_currency_code LIKE '____AR' THEN SUBSTR(pnl.local_currency_code, 1, 3) ELSE pnl.local_currency_code END) and data_as_of_date in (select max(data_as_of_date) from governed.cc_forecast_pnl) AND (fHier.FUNCTION_CD IS NOT NULL OR aHier.group_account_cd IS NOT NULL) group by pnl.cob_date,CASE WHEN tr.function_cd IS NOT NULL AND h.level14 IS NOT NULL THEN tr.TT_CD ELSE NULL END, pnl.company_code,pnl.function_code,pnl.group_account_code,pnl.gaap_code,me.to_currency_code,me.currency_label)a group by cob_date ), prov as( select cob_date, count(*) as prov_count, sum(case when measure_amt <> 0 then 1 else 0 end) prov_non_zero_count, sum(MEASURE_AMT) as prov_amt from ( select cob_date, performance_view_type, company_code, function_code, group_account_code, gaap_code, currency_type, currency_code, measure_id, sum(measure_amt) as measure_amt, projection_type, macro_measure, macro_measure_sub_type, partition_date_key from provision.corp_center_fact_merged where macro_measure in ('PNL','RETAINED_EARNINGS') and projection_type = 'FORECAST' and currency_type = 'Actual Rate' and group_account_code <> 'TM1-5460' and data_as_of_date in (select max(data_as_of_date) from provision.corp_center_fact_merged where macro_measure in ('PNL','RETAINED_EARNINGS') and projection_type = 'FORECAST' and currency_type = 'Actual Rate') group by cob_date, performance_view_type, company_code, function_code, group_account_code, gaap_code, currency_type, currency_code, measure_id, projection_type, macro_measure, macro_measure_sub_type, partition_date_key )b group by cob_date ) SELECT * from gov, prov where gov.cob_date = prov.cob_date; {code} {code} 2021-06-08T08:15:14,450 ERROR [6241f234-77e0-4e63-9873-6eb9d655421c HiveServer2-Handler-Pool: Thread-79] parse.CalcitePlanner: CBO failed, skipping CBO. java.lang.RuntimeException: java.lang.OutOfMemoryError: Java heap space at org.apache.hadoop.hive.ql.parse.CalcitePlanner.rethrowCalciteException(CalcitePlanner.java:1728) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.parse.CalcitePlanner.logicalPlan(CalcitePlanner.java:1564) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:538) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:12680) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:428) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:288) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.parse.ExplainSemanticAnalyzer.analyzeInternal(ExplainSemanticAnalyzer.java:170) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:288) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.Compiler.analyze(Compiler.java:221) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.Compiler.compile(Compiler.java:104) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:188) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:600) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:546) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:540) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hadoop.hive.ql.reexec.ReExecDriver.compileAndRespond(ReExecDriver.java:127) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:199) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:260) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.cli.operation.Operation.run(Operation.java:274) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:565) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:551) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_262] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_262] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_262] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_262] at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at java.security.AccessController.doPrivileged(Native Method) ~[?:1.8.0_262] at javax.security.auth.Subject.doAs(Subject.java:422) ~[?:1.8.0_262] at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1729) ~[hadoop-common-3.1.1.jar:?] at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at com.sun.proxy.$Proxy39.executeStatementAsync(Unknown Source) ~[?:?] at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:315) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:567) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1557) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1542) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286) ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_262] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_262] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_262] Caused by: java.lang.OutOfMemoryError: Java heap space at java.util.Arrays.copyOf(Arrays.java:3332) ~[?:1.8.0_262] at java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124) ~[?:1.8.0_262] at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448) ~[?:1.8.0_262] at java.lang.StringBuilder.append(StringBuilder.java:136) ~[?:1.8.0_262] at java.lang.StringBuilder.append(StringBuilder.java:131) ~[?:1.8.0_262] at org.apache.calcite.rex.RexCall.appendOperands(RexCall.java:109) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rex.RexCall.computeDigest(RexCall.java:166) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rex.RexCall.toString(RexCall.java:183) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rex.RexCall.hashCode(RexCall.java:268) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at java.util.HashMap.hash(HashMap.java:339) ~[?:1.8.0_262] at java.util.HashMap.put(HashMap.java:612) ~[?:1.8.0_262] at java.util.HashSet.add(HashSet.java:220) ~[?:1.8.0_262] at java.util.AbstractCollection.addAll(AbstractCollection.java:344) ~[?:1.8.0_262] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:459) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] at org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] {code} > Query with union fails CBO with OOM > ----------------------------------- > > Key: HIVE-25220 > URL: https://issues.apache.org/jira/browse/HIVE-25220 > Project: Hive > Issue Type: Bug > Components: CBO > Reporter: Krisztian Kasa > Assignee: Krisztian Kasa > Priority: Major > Fix For: 4.0.0 > > > {code} > explain cbo > with meod AS (select max(data_as_of_date) data_as_of_date from > governed.cc_forecast_pnl), > daod as (select min(f.cob_date) data_as_of_date from governed.cc_forecast_pnl > f, meod where f.data_as_of_date = meod.data_as_of_date), > me_rates as ( > SELECT > refRateFX.to_currency_code, > refRateFX.from_currency_code, > cast(refRateFX.exchange_rate as decimal(38,18)) exchange_rate, > cast('GC2' AS string) currency_label > FROM > (SELECT MAX(fx.data_as_of_date) data_as_of_date > FROM governed.standard_fx_rates fx, daod > WHERE fx.data_as_of_date LIKE '%_MCR_MTD' > and fx.data_as_of_date <= concat(daod.data_as_of_date, '_MCR_MTD')) fx, -- > get most recent rates not later than the delivery period > governed.standard_fx_rates refRateFX > WHERE refRateFX.data_as_of_date = fx.data_as_of_date > AND refRateFX.to_currency_code = 'USD' > UNION ALL > SELECT > refRateFX2.from_currency_code to_currency_code, > refRateFX1.from_currency_code, > cast(cast(refRateFX1.exchange_rate as double)/cast(refRateFX2.exchange_rate > as double) as decimal(38,18)) exchange_rate, > CAST('GC1' AS string) currency_label > FROM > (SELECT MAX(fx.data_as_of_date) data_as_of_date > FROM governed.standard_fx_rates fx, daod > WHERE fx.data_as_of_date LIKE '%_MCR_MTD' > and fx.data_as_of_date <= concat(daod.data_as_of_date, '_MCR_MTD')) fx, -- > get most recent rates not later than the delivery period > governed.standard_fx_rates refRateFX1, > governed.standard_fx_rates refRateFX2 > WHERE refRateFX1.data_as_of_date = fx.data_as_of_date > AND refRateFX2.data_as_of_date = fx.data_as_of_date > AND refRateFX1.to_currency_code = 'USD' > AND refRateFX2.from_currency_code = 'CHF' > AND refRateFX2.to_currency_code = 'USD' > ), > cc_func_hier_filter as( > SELECT DISTINCT LEVEL10 FUNCTION_CD > FROM GOVERNED.CC_CYBOS_HIER_FUNCTION > WHERE DATA_AS_OF_DATE in > (SELECT MAX(DATA_AS_OF_DATE) FROM GOVERNED.CC_CYBOS_HIER_FUNCTION) > AND LEVEL2='N14954' > ), > cc_unified_acc_hier_filter as( > SELECT DISTINCT LEVEL14 GROUP_ACCOUNT_CD > FROM governed.cc_cybos_hier_acct > WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM > governed.cc_cybos_hier_acct) > AND LEVEL1='U00000' AND LEVEL6 = 'U52000' > ), > cc_sign_reversal as( > SELECT DISTINCT LEVEL14 GROUP_ACCOUNT_CD, CAST(-1 AS DECIMAL(38,18)) > reverse_sign > FROM governed.cc_cybos_hier_acct > WHERE DATA_AS_OF_DATE in (SELECT MAX(DATA_AS_OF_DATE) FROM > governed.cc_cybos_hier_acct) > AND ((LEVEL1='U00000' AND LEVEL5 = 'U30175') OR (LEVEL2 = 'EAR90006')) > ), > cc_unified_acc_hier as( > SELECT DISTINCT TRIM(level14) level14 > FROM provision.cc_hier_unified_acct_vw > WHERE level5_desc = 'Total operating expense' > AND TRIM(level14) NOT IN > (SELECT group_account_cd FROM governed.cc_temp_reg_exclude_rules > WHERE data_as_of_date IN (SELECT MAX(data_as_of_date) from > governed.cc_temp_reg_exclude_rules)) > ), > tempreg as( > SELECT function_cd, tt_cd > FROM governed.cc_temp_reg_rules > WHERE data_as_of_date IN (SELECT MAX(data_as_of_date) FROM > governed.cc_temp_reg_rules) > ), > gov as( > select cob_date, count(*) as gov_count, sum(case when measure_amt <> 0 then 1 > else 0 end) gov_non_zero_count, sum(MEASURE_AMT) as gov_amt > from ( > select pnl.cob_date, > CASE WHEN tr.function_cd IS NOT NULL AND h.level14 IS NOT NULL THEN tr.TT_CD > ELSE NULL END AS PERFORMANCE_VIEW_TYPE, > pnl.company_code, > pnl.function_code, > pnl.group_account_code, > pnl.gaap_code, > 'Actual Rate' AS CURRENCY_TYPE, > me.to_currency_code AS CURRENCY_CODE, > pnl.group_account_code MEASURE_ID, > sum(CAST(cast((cast(pnl.posting_lc_amt as double) * cast(NVL(sr.reverse_sign, > 1) as double)) as double) * cast(me.exchange_rate as double) as > decimal(38,18))) as MEASURE_AMT, > 'FORECAST' AS PROJECTION_TYPE, > CASE WHEN GROUP_ACCOUNT_CODE LIKE 'EAR%' THEN 'RETAINED_EARNINGS' ELSE 'PNL' > END AS MACRO_MEASURE, > me.currency_label AS MACRO_MEASURE_SUB_TYPE, > pnl.cob_date AS partition_date_key > from governed.cc_forecast_pnl pnl, > me_rates me > left outer join cc_func_hier_filter fHier > on pnl.function_code = fHier.FUNCTION_CD > left outer join cc_unified_acc_hier_filter aHier > on pnl.group_account_code = aHier.group_account_cd > left outer join cc_sign_reversal sr > on pnl.group_account_code = sr.group_account_cd > left outer join tempreg tr > on pnl.function_code = tr.function_cd > left outer join cc_unified_acc_hier h > on pnl.group_account_code = h.level14 > WHERE me.from_currency_code = (CASE WHEN pnl.local_currency_code LIKE > '____AR' THEN SUBSTR(pnl.local_currency_code, 1, 3) ELSE > pnl.local_currency_code END) and data_as_of_date in > (select max(data_as_of_date) from governed.cc_forecast_pnl) > AND (fHier.FUNCTION_CD IS NOT NULL OR aHier.group_account_cd IS NOT NULL) > group by pnl.cob_date,CASE WHEN tr.function_cd IS NOT NULL AND h.level14 IS > NOT NULL THEN tr.TT_CD ELSE NULL END, > pnl.company_code,pnl.function_code,pnl.group_account_code,pnl.gaap_code,me.to_currency_code,me.currency_label)a > group by cob_date > ), > prov as( > select cob_date, count(*) as prov_count, sum(case when measure_amt <> 0 then > 1 else 0 end) prov_non_zero_count, sum(MEASURE_AMT) as prov_amt > from ( > select cob_date, performance_view_type, company_code, function_code, > group_account_code, gaap_code, currency_type, currency_code, measure_id, > sum(measure_amt) as measure_amt, projection_type, macro_measure, > macro_measure_sub_type, partition_date_key > from provision.corp_center_fact_merged > where macro_measure in ('PNL','RETAINED_EARNINGS') and projection_type = > 'FORECAST' and currency_type = 'Actual Rate' and group_account_code <> > 'TM1-5460' and data_as_of_date in (select max(data_as_of_date) from > provision.corp_center_fact_merged > where macro_measure in ('PNL','RETAINED_EARNINGS') and projection_type = > 'FORECAST' and currency_type = 'Actual Rate') > group by cob_date, performance_view_type, company_code, function_code, > group_account_code, gaap_code, currency_type, currency_code, measure_id, > projection_type, macro_measure, macro_measure_sub_type, partition_date_key )b > group by cob_date > ) > SELECT * from gov, prov > where gov.cob_date = prov.cob_date; > {code} > {code} > 2021-06-08T08:15:14,450 ERROR [6241f234-77e0-4e63-9873-6eb9d655421c > HiveServer2-Handler-Pool: Thread-79] parse.CalcitePlanner: CBO failed, > skipping CBO. > java.lang.RuntimeException: java.lang.OutOfMemoryError: Java heap space > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.rethrowCalciteException(CalcitePlanner.java:1728) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.logicalPlan(CalcitePlanner.java:1564) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:538) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:12680) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:428) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:288) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.parse.ExplainSemanticAnalyzer.analyzeInternal(ExplainSemanticAnalyzer.java:170) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:288) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at org.apache.hadoop.hive.ql.Compiler.analyze(Compiler.java:221) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at org.apache.hadoop.hive.ql.Compiler.compile(Compiler.java:104) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:188) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:600) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:546) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:540) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hadoop.hive.ql.reexec.ReExecDriver.compileAndRespond(ReExecDriver.java:127) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:199) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:260) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.cli.operation.Operation.run(Operation.java:274) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:565) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:551) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > ~[?:1.8.0_262] > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > ~[?:1.8.0_262] > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > ~[?:1.8.0_262] > at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_262] > at > org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:78) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.cli.session.HiveSessionProxy.access$000(HiveSessionProxy.java:36) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:63) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at java.security.AccessController.doPrivileged(Native Method) > ~[?:1.8.0_262] > at javax.security.auth.Subject.doAs(Subject.java:422) ~[?:1.8.0_262] > at > org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1729) > ~[hadoop-common-3.1.1.jar:?] > at > org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:59) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at com.sun.proxy.$Proxy39.executeStatementAsync(Unknown Source) ~[?:?] > at > org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:315) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:567) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1557) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1542) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) > ~[hive-service-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286) > ~[hive-exec-3.1.3000.7.2.6.3-1.jar:3.1.3000.7.2.6.3-1] > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) > ~[?:1.8.0_262] > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) > ~[?:1.8.0_262] > at java.lang.Thread.run(Thread.java:748) [?:1.8.0_262] > Caused by: java.lang.OutOfMemoryError: Java heap space > at java.util.Arrays.copyOf(Arrays.java:3332) ~[?:1.8.0_262] > at > java.lang.AbstractStringBuilder.ensureCapacityInternal(AbstractStringBuilder.java:124) > ~[?:1.8.0_262] > at > java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:448) > ~[?:1.8.0_262] > at java.lang.StringBuilder.append(StringBuilder.java:136) > ~[?:1.8.0_262] > at java.lang.StringBuilder.append(StringBuilder.java:131) > ~[?:1.8.0_262] > at org.apache.calcite.rex.RexCall.appendOperands(RexCall.java:109) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at org.apache.calcite.rex.RexCall.computeDigest(RexCall.java:166) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at org.apache.calcite.rex.RexCall.toString(RexCall.java:183) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at org.apache.calcite.rex.RexCall.hashCode(RexCall.java:268) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at java.util.HashMap.hash(HashMap.java:339) ~[?:1.8.0_262] > at java.util.HashMap.put(HashMap.java:612) ~[?:1.8.0_262] > at java.util.HashSet.add(HashSet.java:220) ~[?:1.8.0_262] > at java.util.AbstractCollection.addAll(AbstractCollection.java:344) > ~[?:1.8.0_262] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:459) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createAllPossibleExpressions(RelMdExpressionLineage.java:440) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > at > org.apache.calcite.rel.metadata.RelMdExpressionLineage.createExpressions(RelMdExpressionLineage.java:462) > ~[calcite-core-1.21.0.7.2.6.3-1.jar:1.21.0.7.2.6.3-1] > {code} -- This message was sent by Atlassian Jira (v8.3.4#803005)