CODEWITHSUNDEEP
google-bigquery
cjen
cjen

Reputation: 69

Big Query System Error

I keep getting system error while using window functions with 'PARTITION BY'

It seems to work if 'PARTITION BY' or 'LIMIT' is removed

Will someone be able to help with this? Thanks!

Error: System error. The error has been logged and we will investigate.

Job ID: 719125837414:job_BD8OOBietML6_Y197QhtAHDz_Yk

SELECT id, date, Label1, amount, 
  PERCENT_RANK() OVER(PARTITION BY date, Label1 ORDER BY amount DESC) r
  FROM (SELECT id, date, Label1, SUM(Label2) amount
          FROM [table]
          GROUP EACH BY 1,2,3) LIMIT 10000

Upvotes: 1

Views: 71

Answers (1)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

I took a look at it, and I can confirm it is a bug in BigQuery. The workaround is to remove unused SUM() and grouping key from the inner query, i.e. the query you posted in the question actually works, but the one which triggered the error had extra unused SUM(FLOAT(Label2))/100.0 and IF(col1 IS NULL,col2,col1) col2 in the inner SELECT. Once I removed them - query passed.

Upvotes: 2

Related Questions