title: BigQuery: Get the first non-null value in a window?
body: <p>I would like to obtain the first non-null, non-&quot;undefined&quot; value in a list of values as part of a window.</p>
  <p>Minimal example:</p>
  <p>Given the following code:</p>
  <pre><code>SELECT
      FIRST_VALUE(
          CASE WHEN val = &quot;undefined&quot; THEN NULL ELSE val END
          IGNORE NULLS
      )
      OVER (ORDER BY order_key)
      AS res
  FROM (
      SELECT 1 AS order_key, CAST(NULL AS STRING) AS val
      UNION ALL
      SELECT 2 AS order_key, &quot;undefined&quot; AS val
      UNION ALL
      SELECT 3 AS order_key, &quot;value&quot; AS val
  ) base
  </code></pre>
  <p>I'd expect</p>
  <pre><code>res
  value
  value
  value
  </code></pre>
  <p>as the result set. Yet, the result given by the above is the following:</p>
  <pre><code>res
  null
  null
  value
  </code></pre>
  <p>The <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#first_value" rel="nofollow noreferrer">documentation</a> states the following:</p>
  <blockquote>
  <p><code>FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])</code></p>
  <p>Returns the value of the value_expression for the first row in the current window frame.</p>
  <p>This function includes NULL values in the calculation unless IGNORE NULLS is present. If IGNORE NULLS is present, the function excludes NULL values from the calculation.</p>
  </blockquote>
  <p>Yet it seems like value_expression is not what is tested for NULLs in this case.
  It seems that instead FIRST_VALUE checks NULLs against the source field, not the CASE statement (effectively <em>value_expression</em> in the above).</p>
  <p>While the problem can easily be fixed by doing the case as part of the subquery, I'd like to better understand why this is an issue. Why does <code>FIRST_VALUE</code> not ignore the NULLs provided through the CASE statement?</p>