"Error: Unknown field" using AS in simple queries

[Jake]

Hi,

Something as simple as:

SELECT foo.word FROM publicdata:samples.shakespeare AS foo

doesn't work for me, either in the browser or with the cli.

I get "Error: Unknown field: foo.word." Am I missing something simple?

Thanks,

Jake

[Michael Sheldon]

Hello Jake,

In this case the syntax would be "select <field> as <alias> from <table>". (The select query syntax docs use the more general term <expr> since you can give any expression a unique alias.)  Try:

  SELECT word AS foo FROM publicdata:samples.shakespeare LIMIT 10;

Cheers,

--Michael Sheldon

[Michael Sheldon]

Jake,

Sorry, I thought you were trying to rename the field. I see that you must be looking at the from AS syntax. Something does appear to be broken there. I'll investigate and get back to you.

Cheers,

--Michael Sheldon

[Craig Citro]

Hi Jake,

So I think the thing that's misleading here is our docs -- you're
aliasing the table just fine, but you don't need to use the alias,
just like you didn't need the original table name. So
SELECT foo FROM mytable
is the same as
SELECT foo FROM mytable AS myalias
in that neither needs the alias name. The primary place that aliases
are needed is in dealing with JOIN and/or subselects, where you need
to provide a name for some intermediate table. Would it have been more
clear if the docs suggested that aliasing wasn't needed for "most"
queries, and gave an example of where you needed them?

--

-cc

[Jake]

Hi Craig,

It was while experimenting with JOINs that I ran into the issue in the first place, and produced the sample query as a proof-of-concept that there was something fishy going on.

What if I wanted to do:

SELECT foo.year,bar.year FROM publicdata:samples.gsod AS foo,publicdata:samples.natality AS bar LIMIT 5;

which produces "Error: Unknown field: foo.year" as before.

This is a case where an alias or table name cannot simply be excluded:

SELECT year,year FROM publicdata:samples.gsod,publicdata:samples.natality LIMIT 5;

produces "Error: Ambiguous field reference: year" as I would expect. This is the point of the aliases.

However, even in this case, when I insert the full table names to fix the ambiguity, there are problems:

SELECT publicdata:samples.gsod.year,publicdata:samples.natality.year FROM publicdata:samples.gsod,publicdata:samples.natality LIMIT 5;

produces "Error: Unknown field: publicdata:samples.gsod.year" as if the table names were again unrecognized aliases...

Jake

[Jordan Tigani]

So you're hitting, I believe, a quirk of the BigQuery query language. When you separate table names with commas, (e.g. Select ... FROM TableA, TableB) you're not doing a JOIN, you're actually doing a UNION. Union has some restrictions about schemas, but in this case it actually works, but you just need one year (since you're only really selecting one field).

So if you had written the query as:

SELECT year FROM publicdata:samples.gsod,publicdata:samples.natality LIMIT 5;

It would work, and give you years from the union of those two tables.

Note that if you had wanted to join the tables, they're likely too large. (Join of only one 'big' table is supported). That said, you could do something like:

SELECT gsod.year, natality.cnt 

FROM publicdata:samples.gsod AS gsod 

JOIN (

    SELECT year, count(year) as cnt 

    FROM publicdata:samples.natality 

    GROUP BY year) AS natality 

ON natality.year = gsod.year limit 1000;

This makes the table on the right small (since it has only one row per unique year) while the table on the left can be as large as you like. 

I hope this helps.

-jordan