Hive query not working as expected

Question

id                      bigint                  from deserializer   
created_at              string                  from deserializer   
source                  string                  from deserializer   
favorited               boolean                 from deserializer   
retweeted_status        struct<text:string,user:struct<screen_name:string,name:string>,retweet_count:int>   from deserializer   
entities                struct<urls:array<struct<expanded_url:string>>,user_mentions:array<struct<screen_name:string,name:string>>,hashtags:array<struct<text:string>>> from deserializer   
text                    string                  from deserializer   
user                    struct<screen_name:string,name:string,friends_count:int,followers_count:int,statuses_count:int,verified:boolean,utc_offset:int,time_zone:string,location:string>    from deserializer   
in_reply_to_screen_name string                  from deserializer

.

select  id from election order by election.retweeted_status.retweet_count desc  limit 10

This query is not working the error is "SemanticException [Error 10002]: Line 1:53 Invalid column reference 'retweeted_status'"

 select  * from election order by election.retweeted_status.retweet_count desc  limit 10

But this query is working

My 2 cents: you cannot `ORDER BY` something that is not present in the `SELECT`. — Samson Scharfrichter, Jul 29 '16 at 10:13
BTW, what's the point of that silly tag "mysql-logic" -- even assuming that there is any "logic" in MySQL, here you are running a **Hive** query. — Samson Scharfrichter, Jul 29 '16 at 10:16
So maybe the question should be labeled *Developer not working as expected by database*... — Samson Scharfrichter, Jul 29 '16 at 10:18

score 0 · Accepted Answer · answered Jul 29 '16 at 18:01

Query executes in below sequence

1 FROM clause

2 WHERE clause

3 GROUP BY clause

4 HAVING clause

5 SELECT clause

6 ORDER BY clause

7 limit

As order executes after we have got result set from select clause, as you have not selected column in select clause orderby clause will not able to find the column reference to perform operation.

Hive query not working as expected

1 Answers1