Is there anything wrong in the syntax of this query finding unique values with multiple values in other rows?

Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr As Ss
    Join (
       Select `cd`
       From nit.assrr
       Group By `cd`
       Having Count(Distinct `cd`) > 1
      ) As Y
On Ss.`cd` = Y.`cd`

I am getting this error:

[Cloudera]HiveJDBCDriver ERROR processing query/statement.

Error Code: 2, SQL state: Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask, Query: .. my query

  • What is the DBMS? MySQL, Oracle or something else? – ypercubeᵀᴹ May 10 '18 at 8:53
  • Besides the syntax issue, have you checked that the query will return 0 rows (always, no matter what data the table has)? – ypercubeᵀᴹ May 10 '18 at 8:56
  • Are you using Hive? If yes, which version exactly? – ypercubeᵀᴹ May 10 '18 at 8:59
  • 1
    What is the definition of nit.assrr (including keys)? As ypercubeᵀᴹ points out the current query do not work. Post CREATE TABLEstatements and INSERTstatements for sample data as well as expected result. – Lennart May 10 '18 at 9:14

I suspect you are using a Hive version that is before 0.13.0. In that case the problem is the AS keyword when giving alias to subquery.

From HiveQL documentation Subqueries in the FROM Clause:

SELECT ... FROM (subquery) name ...
SELECT ... FROM (subquery) AS name ...   (Note: Only valid starting with Hive 0.13.0)

... The optional keyword "AS" can be included before the subquery name in Hive 0.13.0 and later versions (HIVE-6519).

Therefore, valid syntax would be:

Select Distinct Ss.`cd`, Ss.`cd`
From nit.assrr AS Ss
    Join (
       Select `cd`
       From nit.assrr
       Group By `cd`
       Having Count(Distinct `cd`) > 1
      ) Y                                  -- AS removed
On Ss.`cd` = Y.`cd` ;

Regarding the semantics of the query. I'm not sure what the actual problem is but this query is guaranteed to return 0 rows, no matter what the data in the table are.

COUNT(DISTINCT cd) will be 1 when you have GROUP BY cd so the HAVING clause will be FALSE always and the Y subquery will have 0 rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.