How do I use columns I just created to create another column in Impala?

Question

I'm trying to figure out how to create a column based on other columns I just created. This is what I'm trying:

CREATE TABLE test AS 
(SELECT 
1 as column_1,
2 as column_2,
column_1 + column_2 as column_3
) 

But I get this error,

AnalysisException: Could not resolve column/field reference: 'column_1'

I'm using HUE and Impala.

Answer 1

You use a subquery or CTE:

SELECT column_1, column_2,
       column_1 + column_2 as column_3
FROM (SELECT 1 as column_1, 2 as column_2) x

SQL does not allow the re-use of column aliases in the SELECT where it is defined. The reason is simple: SQL does not guarantee the order of processing the expressions in a SELECT.