[jira] [Updated] (HIVE-25220) Query with union fails CBO with OOM
[
https://issues.apache.org/jira/browse/HIVE-25220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Krisztian Kasa updated HIVE-25220:
--
Resolution: Fixed
Status: Resolved (was: Patch Available)
Pushed to master. Thanks [~jcamachorodriguez] for review.
> 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
> Labels: pull-request-available
> Fix For: 4.0.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> {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
>
[jira] [Updated] (HIVE-25220) Query with union fails CBO with OOM
[
https://issues.apache.org/jira/browse/HIVE-25220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Krisztian Kasa updated HIVE-25220:
--
Status: Patch Available (was: Open)
> 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
> Labels: pull-request-available
> Fix For: 4.0.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> {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)
>
[jira] [Updated] (HIVE-25220) Query with union fails CBO with OOM
[
https://issues.apache.org/jira/browse/HIVE-25220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Krisztian Kasa updated HIVE-25220:
--
Description:
{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)
[jira] [Updated] (HIVE-25220) Query with union fails CBO with OOM
[
https://issues.apache.org/jira/browse/HIVE-25220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated HIVE-25220:
--
Labels: pull-request-available (was: )
> 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
> Labels: pull-request-available
> Fix For: 4.0.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> {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='U0' 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='U0' 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
>
[jira] [Updated] (HIVE-25220) Query with union fails CBO with OOM
[
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='U0' 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='U0' 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
