Invalid table alias or column reference b

Question

What's wrong with this query (running in hive):

SELECT count(*) TotalCount, b.region_code
from XXX a
INNER JOIN YYY b
ON a.uid=b.uid
where a.dt = '2015-04-15'
group by b.region_code order by b.region_code

I think it should be pretty straightforward, but am getting this:

FAILED: SemanticException [Error 10004]: Line 6:32 Invalid table alias or column reference 'b': (possible column names are: _col0, _col1)

Here is the YYY table:

hive> desc YYY;
OK
status_code     int
uid    string
zip_code        string
keyword string
region_code     bigint
dt      timestamp
channel int

and XXX table:

hive> desc XXX;
OK
group_key     string
category    string
uid    string
dt      timestamp

Answer 1

Try doing this:

SELECT count(*) as TotalCount, b.region_code
from XXX a INNER JOIN
     YYY b
     ON a.ui = b.uid
where a.dt = '2015-04-15'
group by b.region_code
order by region_code

The problem with your code is that b.region_code doesn't exist after the order by. The alias exists (region_code) because that is in the select. The qualified alias does not, because the b is no longer valid after the group by. I think you could write:

order by max(b.region_code)

But that would be silly in this case.

Note that this is common to all databases, except MySQL.