Error Message: TOK_ALLCOLREF is not supported in current context - while Using DISTINCT in HIVE

Question

I'm using the simple command: SELECT DISTINCT * FROM first_working_table; in HIVE 0.11, and I'm receiving the following error message:

FAILED: SemanticException TOK_ALLCOLREF is not supported in current context.

Does anyone know why this is happening? How can we solve it?

Thank you, Gal.

Answer 1

Hive doesn't support DISTINCT * syntax. You can manually specify every field of the table to get the same result:

SELECT DISTINCT field1, field2, ...., fieldN
  FROM first_working_table

Answer 2

As specified in earlier comment distinct * not supported. Which is true. One trick can be like this.

Distinct * can be used in this fashion:

select distinct * from (
select t1.col1,t1.col2,t1.col3,t2.* from t1,t2
)tbl;

I have used this syntax in Hive 2.x. So I can confirm that this works.

Answer 3

There is one trick:

Select distinct * from (select * from tableA)abc

Weird but yes it works!