Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-18107

CBO Multi Table Insert Query with JOIN operator and GROUPING SETS throws SemanticException Invalid table alias or column reference 'GROUPING__ID'

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 2.3.0
    • None
    • CBO
    • None

    Description

      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)
      ........

      Attachments

        Activity

          People

            Unassigned Unassigned
            Spring Serhii Zadorozhniak
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: