SQL translation for floating-point infinity assignment is incorrect

[lschneiderbauer]

DuckDB supports Infinity for its floating point data type (https://duckdb.org/docs/stable/sql/data_types/numeric.html).

The current translation: Inf -> 'Infinity' seems to work only when comparing to floating point variables, but not when assigning the value Inf to variables since duckdb interprets 'Infinity' as a string in this context (see the reprex below).

Explicitly using 'Infinity'::FLOAT does yield the desired result, so maybe the package should translate Inf to 'Infinity'::FLOAT instead of simply 'Infinity' in any case?

library("duckdb")
#> Warning: Paket 'duckdb' wurde unter R Version 4.4.3 erstellt
#> Lade nötiges Paket: DBI
library("dplyr")
#> 
#> Attache Paket: 'dplyr'
#> Die folgenden Objekte sind maskiert von 'package:stats':
#> 
#>     filter, lag
#> Die folgenden Objekte sind maskiert von 'package:base':
#> 
#>     intersect, setdiff, setequal, union

con <- DBI::dbConnect(duckdb::duckdb())

df <-  tibble(x = 1)

duckdb::duckdb_register(con, "df", df)

### Simple infinity assignment is interpreted as string

tbl(con, "df") |> 
  mutate(
    z = Inf
  ) |>
  show_query()
#> <SQL>
#> SELECT df.*, 'Infinity' AS z
#> FROM df

tbl(con, "df") |> 
  mutate(
    z = Inf
  )
#> # Source:   SQL [?? x 2]
#> # Database: DuckDB 1.3.3-dev231 [6999schneiderbauer@Windows 10 x64:R 4.4.2/:memory:]
#>       x z       
#>   <dbl> <chr>   
#> 1     1 Infinity

#### Attempting to explicitly typecast also does not work

tbl(con, "df") |> 
  mutate(
    z = as.numeric(Inf)
  ) |> 
  show_query()
#> <SQL>
#> SELECT df.*, CAST('Infinity' AS NUMERIC) AS z
#> FROM df

tbl(con, "df") |> 
  mutate(
    z = as.numeric(Inf)
  )
#> Error in `collect()`:
#> ! Failed to collect lazy table.
#> Caused by error in `duckdb_result()`:
#> ! Invalid Error: Conversion Error: Could not convert string "Infinity" to DECIMAL(18,3)
#> 
#> LINE 1: SELECT df.*, CAST('Infinity' AS NUMERIC) AS z
#>                      ^
#> ℹ Context: rapi_execute
#> ℹ Error type: INVALID
#> ℹ Raw message: Conversion Error: Could not convert string "Infinity" to DECIMAL(18,3)
#> 
#> LINE 1: SELECT df.*, CAST('Infinity' AS NUMERIC) AS z
#>                      ^

#### Working version with duckdb's "::TYPE" clause

tbl(con, "df") |> 
  mutate(
    z = sql("'Infinity'::FLOAT")
  ) |> 
  show_query()
#> <SQL>
#> SELECT df.*, 'Infinity'::FLOAT AS z
#> FROM df

tbl(con, "df") |> 
  mutate(
    z = sql("'Infinity'::FLOAT")
  )
#> # Source:   SQL [?? x 2]
#> # Database: DuckDB 1.3.3-dev231 [6999schneiderbauer@Windows 10 x64:R 4.4.2/:memory:]
#>       x     z
#>   <dbl> <dbl>
#> 1     1   Inf

####

DBI::dbDisconnect(con)

^{Created on 2025-09-22 with reprex v2.1.1}

[krlmlr]

Thanks, this looks useful. I remember seeing a similar problem in RPostgres. We could perhaps model the implementation after that? Would you like to contribute a PR?

[lschneiderbauer]

Could you point me to the RPostgres relevant code?
I only seem to find https://github.com/tidyverse/dbplyr/blob/0df138ea3dab075cfdb78c17f41fa3e1a9a5be6e/R/escape.R#L89 in dbplyr related to 'Infinity' (escape.double()), but nothing in the RPostgres repository.

Are you refering to something discussed in tidyverse/dbplyr#727?