Skip to contents

Native values and DuckDB R compatibility

Source: vignettes/native-values.Rmd
native-values.Rmd

Inputs, results and checkpoints are stored as DuckDB VARIANT values, so you can query them in SQL. A type descriptor stored beside each value records what SQL cannot express: NULL versus a typed NA, vector versus list, data frame versus named list, and names, factor levels, time zones, row names and I().

R value DuckDB value
length-one logical, integer, double, character BOOLEAN, INTEGER, DOUBLE, VARCHAR
longer atomic vectors typed lists such as INTEGER[]
raw vector BLOB
Date, POSIXct, difftime DATE, TIMESTAMPTZ, INTERVAL
factor, ordered factor VARCHAR, levels in the descriptor
bit64::integer64 BIGINT
named list, data frame object
unnamed list array

Timestamps and durations are kept to microsecond precision. Custom classes, matrices, environments and functions need explicit conversion; see Serialized R objects. ?ca_values has the full rules.

The package requires DuckDB R 1.5.5 or newer and writes files with storage compatibility v1.5.0. Its own schema is version 1, and there are no migrations yet.

Field names

Named lists and data frame columns become STRUCT fields, which DuckDB compares without regard to ASCII case. Names must therefore be nonempty and unique ignoring case: A and a cannot sit in the same container. The value is rejected before any SQL runs.

library(CanardAbsurd)
db <- ca_open()
tryCatch(
  ca_spawn(db, "example", list(A = 1L, a = 2L)),
  canard_value_error = conditionMessage
)
#> [1] "List names must be nonempty and unique ignoring ASCII case"
ca_close(db)

Known driver and engine issues

Three upstream defects shape how CanardAbsurd reads values.

VARIANT conversion under garbage collection

DuckDB R 1.5.5 can corrupt a VARIANT value it is converting to R if R collects garbage mid-conversion (duckdb-r #2750). Type descriptors pass through that path, so reading a task can occasionally fail or crash. Windows hits this reliably in one test, which is skipped there until a fixed driver is released.

Nested NULLs

DuckDB R 1.5.3 and 1.5.5 cannot convert an all-NULL VARIANT array, or an object with a NULL field, to R. The SQL values are valid, and casting them to a concrete type works:

library(DBI)
con <- dbConnect(duckdb::duckdb(shared_home = FALSE),
  config = list(autoinstall_known_extensions = "false"))
packageVersion("duckdb")
#> [1] '1.5.5'
dbGetQuery(con, "PRAGMA version")
#>   library_version   source_id  codename
#> 1          v1.5.5 d8cdaa33fda Variegata
queries <- c(
  scalar_null = "SELECT NULL::VARIANT AS value",
  variant_array = "SELECT [NULL, NULL]::VARIANT AS value",
  typed_array = "SELECT ([NULL, NULL]::VARIANT)::INTEGER[] AS value",
  variant_object = "SELECT {'x': NULL}::VARIANT AS value",
  typed_object = "SELECT ({'x': NULL}::VARIANT)::STRUCT(x INTEGER) AS value"
)
lapply(queries, function(sql) {
  tryCatch(dbGetQuery(con, sql), error = function(error) conditionMessage(error))
})
#> $scalar_null
#>   value
#> 1  NULL
#> 
#> $variant_array
#> [1] "Unknown type for column `variant`: \"NULL\"\n\033[34mℹ\033[39m Context: LogicalTypeId::LIST"
#> 
#> $typed_array
#>    value
#> 1 NA, NA
#> 
#> $variant_object
#> [1] "Unknown type for column `variant$x`: \"NULL\"\n\033[34mℹ\033[39m Context: LogicalTypeId::STRUCT"
#> 
#> $typed_object
#>    x
#> 1 NA
dbDisconnect(con, shutdown = TRUE)

The DuckDB R 2.0 development branch handles SQLNULL during conversion (feeca1e1); released 1.5 drivers do not.

Indexed VARIANT field extraction

On DuckDB 1.5.3 and 1.5.5, pushing VARIANT field extraction into an index scan can raise an internal error that invalidates the database (duckdb #24064). Read whole values into a MATERIALIZED CTE first, then extract fields. A cast alone does not avoid the problem:

con <- dbConnect(duckdb::duckdb(shared_home = FALSE))
dbExecute(con, "CREATE TABLE events (id INTEGER PRIMARY KEY, value VARIANT)")
#> [1] 0
dbExecute(con, "INSERT INTO events VALUES (1, {'duration_ms': 100}::VARIANT)")
#> [1] 1
dbGetQuery(con, "
  WITH payload AS MATERIALIZED (
    SELECT value FROM events WHERE id = 1
  )
  SELECT value['duration_ms']::DOUBLE AS duration_ms FROM payload
")
#>   duration_ms
#> 1         100
dbDisconnect(con, shutdown = TRUE)

How values are read

To avoid the nested-NULL defect, CanardAbsurd never hands a stored value to the driver’s generic VARIANT decoder. It reads the descriptor first, then fetches the value through a SQL projection with concrete types, inside a materialized CTE:

  1. Claim. One UPDATE takes the lease and returns the input’s descriptor. A read that requires the same lease then fetches the typed input. No handler runs until that read succeeds.
  2. Replay. Entering a step renews the lease and returns the saved descriptor; a lease-checked read then fetches the value. A failed read never reruns the callback.
  3. New checkpoint or completion. The descriptor comes from the R value, and one UPDATE stores both.

The extra read costs a round trip and is not part of the UPDATE’s transaction. If the read fails, the claim or renewal still stands. Expiry or cancellation in between raises canard_lease_lost, and a stored value that cannot be decoded raises canard_restore_error. Neither is charged to the handler.