sql - Alias of window function field causing a "not found" error when used in HAVING and WHERE clauses -

consider following bigquery query:

select     tn.object object_alias,     tn.attribute1 attribute1_alias,     tn.attribute2 attribute2_alias,     tn.score score_alias,     row_number() on (partition attribute1_alias, attribute2_alias order score_alias desc) row_num_alias     [datasetname.tablename] tn having # causes error when using     row_num_alias <= 20 

in query, reference row_num_alias field in having clause causing following error: field 'row_num_alias' not found. same error when replacing having clause where clause, , seems error thrown all window functions.

is bug in bigquery? or there error in query?

possibly related:

• mysterious error when combining lead function, second window function , column alias
• https://code.google.com/p/google-bigquery/issues/detail?id=336&q=window%20alias

one workaround convert subquery , move where clause outside subquery (see below), seems cumbersome (and isn't necessary).

select     object_alias,     attribute1_alias,     attribute2_alias,     score_alias,     row_num_alias     (select         tn.object object_alias,         tn.attribute1 attribute1_alias,         tn.attribute2 attribute2_alias,         tn.score score_alias,         row_number() on (partition attribute1_alias, attribute2_alias order score_alias desc) row_num_alias             [datasetname.tablename] tn     )     row_num_alias <= 20 

column aliases not work in where clause, in bigquery. there no guarantee work in having clause either, although databases support that. column aliases can used in order by; think support standard part of phasing out reference-by-number.

you know right solution, use subquery.

by way, having clause no group by looks awkward. such construct used in mysql, work-around -- subqueries in mysql incur more overhead in other databases because optimizer not sophisticated.