BigQuery Standard SQL: Setting a value to positive or negative infinity

Question

I note from the docs for BigQuery Standard SQL Mathematical functions that we can test if a number "is infinity" using the IS_INF() function something like this:

WITH demo_tbl AS (
  SELECT 1 AS val UNION ALL
  SELECT 100 AS val
)

SELECT 
  val,
  IS_INF(val) as is_infinity
FROM demo_tbl

which outputs:

+---+-----+-------------+
|   | val | is_infinity |
+---+-----+-------------+
| 0 |   1 | False       |
| 1 | 100 | False       |
+---+-----+-------------+

but is it possible to explicitly set a value to be positive or negative infinity using some constant value or key word?

Perhaps something like this:

WITH demo_tbl AS (
  SELECT 1 AS val UNION ALL
  SELECT +inf AS val -- <-- THIS doesnt work
)

SELECT 
  val,
  IS_INF(val) as is_infinity
FROM demo_tbl

in order to give desired output similar to this:

+---+-----+-------------+
|   | val | is_infinity |
+---+-----+-------------+
| 0 |   1 | False       |
| 1 | inf | True        |
+---+-----+-------------+

I searched the documentation as best as I could and had a Google around this but couldn't turn up an answer.

Answer 1

You can create the value using cast():

select is_inf(cast('Infinity' as float64))

or:

select is_inf(cast('inf' as float64))

Buried in the documentation is:

There is no literal representation of NaN or infinity, but the following case-insensitive strings can be explicitly cast to float:

• "NaN"
• "inf" or "+inf"
• "-inf"