[ https://issues.apache.org/jira/browse/HIVE-18107?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16259312#comment-16259312 ]
Sergey Zadoroshnyak commented on HIVE-18107: -------------------------------------------- [~jcamachorodriguez] [~ashutoshc] [~pxiong] This issue is reproducible only if hive.cbo.enable=true. If hive.cbo.enable=false, multi Table Insert Query will be successfully compiled and executed. > CBO Multi Table Insert Query with JOIN operator and GROUPING SETS throws > SemanticException Invalid table alias or column reference 'GROUPING__ID' > --------------------------------------------------------------------------------------------------------------------------------------------------- > > Key: HIVE-18107 > URL: https://issues.apache.org/jira/browse/HIVE-18107 > Project: Hive > Issue Type: Bug > Components: CBO > Affects Versions: 2.3.0 > Reporter: Sergey Zadoroshnyak > Assignee: Jesus Camacho Rodriguez > Fix For: 3.0.0 > > > hive 2.3.0 > set hive.execution.engine=tez; > set hive.multigroupby.singlereducer=false; > *set hive.cbo.enable=true;* > Multi Table Insert Query. *Template:* > FROM (SELECT * FROM tableA) AS alias_a JOIN (SELECT * FROM tableB) AS > alias_b > ON (alias_a.column_1 = alias_b.column_1 AND alias_a.column_2 = > alias_b.column_2) > > INSERT OVERWRITE TABLE tableC PARTITION > ( > partition1='first_fragment' > ) > SELECT > GROUPING__ID, > alias_a.column4, > alias_a.column5, > alias_a.column6, > alias_a.column7, > count(1) > > > AS rownum > WHERE alias_b.column_3 = 1 > GROUP BY > alias_a.column4, > alias_a.column5, > alias_a.column6, > alias_a.column7 > GROUPING SETS > ( > (alias_a.column4), > (alias_a.column4,alias_a.column5), > (alias_a.column4,alias_a.column5,alias_a.column6,alias_a.column7) > ) > > INSERT OVERWRITE TABLE tableC PARTITION > ( > partition1='second_fragment' > ) > SELECT > GROUPING__ID, > alias_a.column4, > alias_a.column5, > alias_a.column6, > alias_a.column7, > count(1) > > > AS rownum > WHERE alias_b.column_3 = 2 > GROUP BY > alias_a.column4, > alias_a.column5, > alias_a.column6, > alias_a.column7 > GROUPING SETS > ( > (alias_a.column4), > (alias_a.column4,alias_a.column5), > (alias_a.column4,alias_a.column5,alias_a.column6,alias_a.column7) > ) > 16:39:17,822 ERROR CalcitePlanner:423 - CBO failed, skipping CBO. > org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:537 Invalid table > alias or column reference 'GROUPING__ID': (possible column names are:...... > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:11600) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:11548) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genSelectLogicalPlan(CalcitePlanner.java:3706) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genLogicalPlan(CalcitePlanner.java:3999) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1315) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1261) > at org.apache.calcite.tools.Frameworks$1.apply(Frameworks.java:113) > at > org.apache.calcite.prepare.CalcitePrepareImpl.perform(CalcitePrepareImpl.java:997) > at org.apache.calcite.tools.Frameworks.withPrepare(Frameworks.java:149) > at org.apache.calcite.tools.Frameworks.withPlanner(Frameworks.java:106) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.logicalPlan(CalcitePlanner.java:1069) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.getOptimizedAST(CalcitePlanner.java:1085) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:364) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:11119) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:286) > at > org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:258) > at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:511) > at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1316) > at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1294) > at > org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:204) > at > org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:290) > at > org.apache.hive.service.cli.operation.Operation.run(Operation.java:320) > at > org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:530) > at > org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:517) > at > org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:310) > at > org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:530) > at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at > org.apache.hive.jdbc.HiveConnection$SynchronizedHandler.invoke(HiveConnection.java:1524) > at com.sun.proxy.$Proxy17.ExecuteStatement(Unknown Source) > at > org.apache.hive.jdbc.HiveStatement.runAsyncOnServer(HiveStatement.java:312) > at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:253) > ........ > FAILED: SemanticException [Error 10004]: Line 1:537 Invalid table alias or > column reference 'GROUPING__ID': (possible column names are: > 16:39:17,852 ERROR Driver:1126 - FAILED: SemanticException [Error 10004]: > Line 1:537 Invalid table alias or column reference 'GROUPING__ID': (possible > column names are:...... > org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:537 Invalid table > alias or column reference 'GROUPING__ID': (possible column names are:...... > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:11600) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:11548) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genSelectPlan(SemanticAnalyzer.java:4386) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genSelectPlan(SemanticAnalyzer.java:4159) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPostGroupByBodyPlan(SemanticAnalyzer.java:9697) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:9636) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:10541) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:10406) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:10433) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:10419) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genOPTree(SemanticAnalyzer.java:11106) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:481) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:11119) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:286) > at > org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:258) > at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:511) > at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1316) > at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1294) > at > org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:204) > at > org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:290) > at > org.apache.hive.service.cli.operation.Operation.run(Operation.java:320) > at > org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:530) > at > org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:517) > at > org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:310) > at > org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:530) > at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at > org.apache.hive.jdbc.HiveConnection$SynchronizedHandler.invoke(HiveConnection.java:1524) > at com.sun.proxy.$Proxy17.ExecuteStatement(Unknown Source) > at > org.apache.hive.jdbc.HiveStatement.runAsyncOnServer(HiveStatement.java:312) > at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:253) > ........ -- This message was sent by Atlassian JIRA (v6.4.14#64029)