search

google-code-export / google-bigquery

Automatically exported from code.google.com/p/google-bigquery
1 stars 0 forks source link

SELECT of aliased field fails if scoped aggregation in subquery #219

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
This query fails:

SELECT
  g.repository.url,
  cnt,
FROM
  (SELECT
    repository.url,
    COUNT(payload.pages.action) WITHIN RECORD as cnt,
  FROM publicdata:samples.github_nested) g
LIMIT 10

With the error:

Field 'g.repository.url' not found; did you mean 'repository.url'?
It looks like aliased fields in the SELECT clause don't work when the SELECT 
includes a field calculated from a scoped aggregation in the subquery and you 
don't have any JOINs.

I found a related bug 
(https://code.google.com/p/google-bigquery/issues/detail?id=40) which is 
labelled as fixed but I think the presence of a scoped aggregation in the 
subquery might be an edge case that wasn't covered.

This query does work:

SELECT
  g.repository.url,
  cnt,
FROM
  (SELECT
    repository.url,
    99 as cnt,
  FROM publicdata:samples.github_nested) g
LIMIT 10

I can obviously work around this by removing the aliases from the SELECT clause 
but the SQL is actually generated by a querying tool we're using so it's not 
easy for us to fix.

Note: I first asked about this on stackoverflow and Mosha Pasumansky confirmed 
it's a bug.

http://stackoverflow.com/questions/28439390/bigquery-bug-select-of-aliased-field
-fails-if-scoped-aggregation-in-subquery

Original issue reported on code.google.com by a...@realself.com on 10 Feb 2015 at 11:07