title: BigQuery internal error on query with a join select
body: <p>I am currently doing something very similar as here:
  <a href="https://stackoverflow.com/questions/13196190/mysql-subtracting-value-from-previous-row-group-by">MySQL - Subtracting value from previous row, group by</a></p>
  
  <p>My current query is:</p>
  
  <pre><code>SELECT a.x, a.y, a.z, COALESCE(a.z - b.z,0) AS diff
  FROM [bla] AS a
  LEFT JOIN EACH
  [bla] AS b
  ON b.x=a.x
  AND b.y = (SELECT MAX(y) FROM [bla] WHERE x = a.x AND y &lt; a.y)
  </code></pre>
  
  <p>However, I end up with the following error:</p>
  
  <p><code>Error: An internal error occurred and the request could not be completed.</code></p>
  
  <p>This error is not really helpful and I do not know what is wrong here. The problem seems to be the final ON statement with the SELECT subquery.</p>