BigQuery subselect in JOIN does not recognize fields

Question

I have a table with several dated snapshots for each user and a table with the latest snapshot date for each user (generated using a query).

I tried several options to get a simple mix of the two, but I had no luck. I want to select all the records from the snapshot table that match the user ID and date from another table.

I have many errors, but this is the last one (subselections and renaming were done for debugging, which field may cause the problem):

SELECT t1.uuid, t1.username, t1.d FROM (SELECT uuid, username, date AS d FROM [Activity.user_snapshots]) as t1 JOIN EACH (SELECT uuid, date AS dg FROM [Activity.latest_snapshots]) as t2 ON t1.uuid = t2.uuid AND t1.d = t2.dg; 

The error response that I get in this case is:

 Error: Field 'dg' not found in table '__S0'. 

When I tried a much more direct request:

 SELECT t1.uuid, t1.username, t1.date FROM [Activity.user_snapshots] as t1 JOIN EACH [Activity.latest_snapshots] as t2 ON t1.uuid = t2.uuid AND t1.date = t2.date; 

I get this error:

 Error: Field date from table __S0 is not a leaf field. 

Any ideas?

Answer 1

There is an error associated with timestamp values. If you force them to their basic microsecond values, you should be good. This query should work:

 SELECT t1.uuid, t1.username, USEC_TO_TIMESTAMP(t1.d) FROM ( SELECT uuid, username, TIMESTAMP_TO_USEC(date) AS d FROM [Activity.user_snapshots]) as t1 JOIN EACH ( SELECT uuid, TIMESTAMP_TO_USEC(date) AS dg FROM [Activity.latest_snapshots]) as t2 ON t1.uuid = t2.uuid AND t1.d = t2.dg; 

Answer 2

In case it is useful to anyone else. The problem I ran into was that when I created the latest_snapshots table, since I had to convert the STRING date field to a timestamp in order to make it a MAX statement, it was saved in the resulting table as a timestamp object.

Thus, error messages are misleading. Annoyingly, I had to create a new table where I converted the timestamp back to a string object, since there was no way in JOIN-ON to do this.

If anyone knows how to do this, this is one query without creating an extra table, that would be cool. So far, my attempts to do this with subsamples have failed.

Please note that the issue of joining timestamp was fixed in a previous release; let us know if you continue to see problems with it.