Error while compiling statement: FAILED: SemanticException [Error 10002]

Question

select d.order_type from migu_td_aaa_order_log_d d where  exists(select 1 
from migu_user r where r.user_id = '156210106' and r.user_num = 
d.serv_number) and d.product_id in ('2028594290','2028596512','2028597138' ) 
order by d.opr_time desc limit 1

why the above sql failed ,indicates : FAILED: SemanticException [Error 10002]: Line 4:11 Invalid column reference 'opr_time'

but the below one works :

select temp.order_type from (
select d.* from migu_td_aaa_order_log_d d where  exists(select 1 from 
migu_user r where r.user_id = '156210106' and r.user_num = d.serv_number) 
and d.product_id in ('2028594290','2028596512','2028597138' ) order by 
d.opr_time desc limit 1) temp;

this one works fine ,too ,and much more efficient than the second one:

select d.* from migu_td_aaa_order_log_d d where  exists(select 1 from 
migu_user r where r.user_id = '156210106' and r.user_num = d.serv_number) 
and d.product_id in ('2028594290','2028596512','2028597138' ) 
order by d.opr_time desc limit 1

I only need to get order_type field,so even though the second one works,but it cost much more time. Can anyone help me? Thanks a lot!

score 2 · Answer 1 · edited May 23 '17 at 12:18

2

Your first query does not work because, in the first select statement, you are just getting one column (d.order_type), but you are trying to order by another column (d.opr_time), which you have not included in your select statement

select d.order_type from ...
...
order by d.opr_time desc limit 1

Note that if you added the column d.opr_time to your first query, it would work:

select d.order_type, d.opr_time from ...
...
order by d.opr_time desc limit 1

Your second query works because, in the subquery, you have selected all the columns of d (d.*), so when you order by opr_time, that column is present. (Same for the third query).

select temp.order_type from (
select d.* ... order by d.opr_time ...

EDITED:

According to the Hive documentation:

When using group by clause, the select statement can only include columns included in the group by clause. Of course, you can have as many aggregation functions (e.g. count) in the select statement as well.

So, this query:

select d.order_type, d.opr_time from ...
...
order by d.opr_time desc limit 1

Shouldn't work either, because the select clause has an additional column (d.order_type) that is not included in the group by clause.

I hope this helps.

P.S. This answer about SQL execution order might be useful.

edited May 23 '17 at 12:18

Community

1
1

answered May 02 '17 at 07:36

Jaime Caffarel

2,401
4
30
42

Thanks for your answer!But in mysql,it is fine to order by column without including it in the select statement,so I did not consider this reason.. – Rick May 02 '17 at 08:10
@RickGeng Yes, that's possible in mysql, but because that database extends the use of `group by`: ("MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the SELECT list that do not appear in the GROUP BY clause.")(http://stackoverflow.com/a/1023349/2026277). It doesn't appear to be the case in Hive. – Jaime Caffarel May 02 '17 at 08:37
thanks for your help,and sorry to undo acceptance...Dudu Markovitz's answer happen to solve my real problem.I hope you wouldn't mind it.I am new to this community,I don't quite know the rules. – Rick May 02 '17 at 13:24
No need to say sorry at all. The other answer is definitely more accurate and answers exactly what you need, so it should be the accepted answer. And welcome to StackOverflow :-) – Jaime Caffarel May 02 '17 at 13:51
I really wish I could choose more than one answer..Thanks for your consideration and your patient answer – Rick May 03 '17 at 07:25

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

1.

Hive currently have an order by limitation.
The current status of this issue is PATCH AVAILABLE.

see -
"Can't order by an unselected column"
https://issues.apache.org/jira/browse/HIVE-15160

2.

You might want to get familiar with LEFT SEMI JOIN which is a cleaner syntax for EXISTS https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins#LanguageManualJoins-JoinSyntax

3.

using min / max over a struct / named_struct can be used instead of order by ... asc / desc and limit 1

Here is an alternative solution:

select      max(named_struct('opr_time',d.opr_time,'order_type',d.order_type)).order_type

from                        migu_td_aaa_order_log_d d 
            
            left semi join  migu_user               r 
            
            on              r.user_num  =  
                            d.serv_number 
                        
                        and r.user_id   = '156210106' 

where       d.product_id in ('2028594290','2028596512','2028597138') 
;

P.s.

You seriously want to consider to treat IDs (user_id, product_id) as numeric and not as strings.

I really appreciate your generous help,this function **named_struct** solved my problem.At first,I decided to use temporary table.With your help,I can use this one instead. `select temp.* from ( select d.serv_number,r.user_id,max(named_struct('opr_time',d.opr_time,'order_type',d.order_type)).order_type from migu_td_aaa_order_log_d d , migu_user r where d.serv_number = r.user_num and r.user_id in ('156210106','156547688') and d.product_id in ('2028596512','2028597138') group by d.serv_number,r.user_id )temp where temp.order_type = '1'` — Rick, May 02 '17 at 13:13

Error while compiling statement: FAILED: SemanticException [Error 10002]

2 Answers2

1.

2.

3.