[HIVE-24478] Subquery GroupBy with Distinct SemanticException: Invalid column reference

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Labels: pull-request-available
• Assignee: Panagiotis Garefalakis
• Reporter: Panagiotis Garefalakis
• Created: 03/Dec/20 14:40
• Updated: 17/Nov/22 08:48
• Resolved: 02/Feb/21 09:37

Description

CREATE TABLE tmp_src1(
  `npp` string,
  `nsoc` string) stored as orc;

INSERT INTO tmp_src1 (npp,nsoc) VALUES ('1-1000CG61', '7273111');

SELECT `min_nsoc`
FROM
     (SELECT `npp`,
             MIN(`nsoc`) AS `min_nsoc`,
             COUNT(DISTINCT `nsoc`) AS `nb_nsoc`
      FROM tmp_src1
      GROUP BY `npp`) `a`
WHERE `nb_nsoc` > 0;

Issue:

org.apache.hadoop.hive.ql.parse.SemanticException: Line 0:-1 Invalid column reference 'nsoc' at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genGroupByPlanGroupByOperator1(SemanticAnalyzer.java:5405)

Query runs fine when we include `nb_nsoc` in the Select expression

Issue Links

• is related to: HIVE-22335 CBO throws Invalid column reference exception
• links to: GitHub Pull Request #1732