---
title: "Native values and DuckDB R compatibility"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Native values and DuckDB R compatibility}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
```

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](serialized-values.html). `?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.

```{r field-name-admission}
library(CanardAbsurd)
db <- ca_open()
tryCatch(
  ca_spawn(db, "example", list(A = 1L, a = 2L)),
  canard_value_error = conditionMessage
)
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](https://github.com/duckdb/duckdb-r/issues/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:

```{r connection}
library(DBI)
con <- dbConnect(duckdb::duckdb(shared_home = FALSE),
  config = list(autoinstall_known_extensions = "false"))
packageVersion("duckdb")
dbGetQuery(con, "PRAGMA version")
```

```{r nested-null-reproducer}
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))
})
```

```{r close-connection}
dbDisconnect(con, shutdown = TRUE)
```

The DuckDB R 2.0 development branch handles `SQLNULL` during conversion
([feeca1e1](https://github.com/duckdb/duckdb-r/commit/feeca1e1ae289cbffec4b93f8ba0d08bc9f3e422));
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](https://github.com/duckdb/duckdb/issues/24064)). Read whole
values into a `MATERIALIZED` CTE first, then extract fields. A cast alone does
not avoid the problem:

```{r indexed-value-query}
con <- dbConnect(duckdb::duckdb(shared_home = FALSE))
dbExecute(con, "CREATE TABLE events (id INTEGER PRIMARY KEY, value VARIANT)")
dbExecute(con, "INSERT INTO events VALUES (1, {'duration_ms': 100}::VARIANT)")
dbGetQuery(con, "
  WITH payload AS MATERIALIZED (
    SELECT value FROM events WHERE id = 1
  )
  SELECT value['duration_ms']::DOUBLE AS duration_ms FROM payload
")
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.