CODEWITHSUNDEEP
google-bigquery
Navneet Aron
Navneet Aron

Reputation: 75

Bigquery Query failes the first time and successfully completes the 2nd time

I'm executing the following query. 

SELECT properties.os, boundary, user, td,
  SUM(boundary) OVER(ORDER BY rows) AS session
FROM
  (
  SELECT properties.os, ROW_NUMBER() OVER() AS rows, user, td,
    CASE WHEN td > 1800 THEN 1 ELSE 0 END AS boundary
  FROM (
    SELECT properties.os, t1.properties.distinct_id AS user,
      (t2.properties.time - t1.properties.time) AS td
    FROM (
      SELECT properties.os, properties.distinct_id, properties.time, srlno,
        srlno-1 AS prev_srlno
      FROM (
        SELECT properties.os, properties.distinct_id, properties.time,
          ROW_NUMBER()
        OVER (PARTITION BY properties.distinct_id
          ORDER BY properties.time) AS srlno
        FROM [ziptrips.ziptrips_events]
        WHERE properties.time  > 1367916800
          AND properties.time < 1380003200)) AS t1
    JOIN (
      SELECT properties.distinct_id, properties.time, srlno,
        srlno-1 AS prev_srlno
      FROM (
        SELECT properties.distinct_id, properties.time,
          ROW_NUMBER() OVER
          (PARTITION BY properties.distinct_id ORDER BY properties.time) AS srlno
        FROM [ziptrips.ziptrips_events]
        WHERE
          properties.time  > 1367916800
          AND properties.time < 1380003200 )) AS t2
    ON t1.srlno  = t2.prev_srlno
      AND t1.properties.distinct_id = t2.properties.distinct_id
    WHERE (t2.properties.time - t1.properties.time) > 0))

It fails the first time with the following error. However on 2nd run it completes without any issue. I'd appreciate any pointers on what might be causing this.

The error message is: Query Failed Error: Field 'properties.os' not found in table '__R2'. Job ID: job_VWunPesUJVLxWGZsMgpoti14BM4

Thanks, Navneet

Upvotes: 0

Views: 68

Answers (1)

Jordan Tigani
Jordan Tigani

Reputation: 26637

We (the BigQuery team) are in the process of rolling out a new version of the query engine that fixes a number of issues like this one. You likely hit an old version of the query engine and then when you retried, hit the new one. It may take us a day or so with a portion of traffic pointing at the updated version in order to verify there aren't any regressions. Please let us know if you hit this again after 24 hours or so.

Upvotes: 1

Related Questions