Read DuckDB agent mode from stderr, not from the table

John Burns

Systems, Automation

Read DuckDB agent mode from stderr, not from the table

A script that reads only stdout from duckdb -c "SELECT 42 AS answer;" cannot see the line that says agent mode is on. That line goes to stderr. The stdout it does see is either a box table whose type row says int32, or a Markdown row whose header says answer:INTEGER. Both are exit 0. Both contain 42. A parser that splits on the box-drawing character will miss every row of the second form and still report a clean exit.

DuckDB 2.0.0, released 21 October 2026, added that renderer so a coding agent gets a compact table, a row-count footer, and JSON errors. The 9 October agent-mode post and the 2.0 CLI page describe the switch. They do not show what a non-interactive caller receives when the variable is empty, set to 0, set to true, or paired with -csv. That is the check a wrapper has to make before it parses a table.

The CLI that was measured

The probe ran on Debian 13, Linux amd64. The binary was the official 2.0.0 Linux amd64 CLI archive from the DuckDB install page, duckdb-cli-linux-amd64.tar.gz. The page publishes this SHA-256:

c6507206baf7793c6013dbd82368469a45f20dc0e17a6f92f204602f652ed2bc

The downloaded archive matched that digest. duckdb -version printed:

v2.0.0 (Cyanoptera) d54cbbe3df

That short hash is the release commit d54cbbe3df17e8e5ce68312f483b4155836112aa named on the v2.0.0 tag. The probe used a clean home directory, so ~/.duckdbrc could not change .startup_text, .mode, or the row caps. Agent-related variables were unset except the one under test. Stdout was a pipe unless a case says it was a pseudo-terminal.

This run did not open a 1.x database file, start a Quack server, or compare the PEG parser with the legacy parser. Those are separate migrations. The renderer is the contract a CLI wrapper sees on every call, including a call that only asks for the version.

A pipe is not an agent

Without a recognized variable, the piped command stayed in duckbox mode. .show reported agent: off and mode: duckbox. Stderr was empty.

┌────────┐
│ answer │
│ int32  │
├────────┤
│     42 │
└────────┘

Setting CLAUDECODE=1 on the same pipe changed both streams. Stdout became:

| answer:INTEGER |
|---|
| 42 |

Stderr became one line:

duckdb agent mode on: CLAUDECODE is set and stdout is not a terminal; -no-agent turns it off, .help agent explains the output

.show then reported mode: markdown and agent: claude-code.

An empty CLAUDECODE stayed in the box. The name has to be set to a non-empty value. CLAUDECODE=0 is non-empty, and it turned agent mode on. That string is not a boolean off switch. The off switches measured later are DUCKDB_AGENT_MODE=0, DUCKDB_AGENT_MODE=false, and -no-agent.

Names that only contain a substring did not match. GPG_AGENT_INFO, set to a socket-style value, left the box renderer in place. So did MY_AGENT=1 and CLAUDECODE_EXTRA=1. The match is the exact name from the agent-mode page, not a search for the word agent.

These non-empty values turned the piped renderer on. .show labeled the ones that were checked:

  • CLAUDECODE printed claude-code
  • CURSOR_AGENT printed cursor
  • GEMINI_CLI printed gemini-cli
  • COPILOT_AGENT and COPILOT_CLI printed github-copilot
  • CODEX_CI and CODEX_THREAD_ID printed codex
  • AI_AGENT=1 and AGENT=1 printed the value 1, not a product name

CODEX_SANDBOX and COPILOT_AGENT_SESSION_ID also turned the mode on. Their .show labels were not recorded in this run, so a job that branches on the label should print .show rather than assume the product name.

-agent with none of those variables forced the mode on. .show said agent: on. The stderr line was duckdb agent mode on (-agent); .help agent explains the output. It did not mention the terminal.

A pseudo-terminal changed the detection result. CLAUDECODE=1 with stdout attached to a pty, and -light-mode so the CLI would not probe the background color, printed the box table and no agent line. The same pty with -agent printed the Markdown table. Detection requires a non-terminal stdout. Forcing the mode does not.

On a pty that did not answer the color probe, and without -light-mode or -dark-mode, the CLI wrote this before the table:

Timeout trying to read terminal background color (> 5s elapsed).
Disable terminal background color detection by using duckdb -dark-mode or duckdb -light-mode.

That delay is the terminal probe, not agent mode. A wrapper that allocates a pty and expects a fast -c result should pass one of those two flags. The color flag did not, by itself, turn agent mode on or off. -light-mode with CLAUDECODE=1 on a pipe still entered agent mode.

The tables are different dialects

The 10-row query SELECT range AS n FROM range(10) is a useful size because it is the smallest result in this run that printed a footer.

Agent mode wrote 110 bytes of stdout:

| n:BIGINT |
|---|
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
10 rows, hash 885edae15671293b

-no-agent wrote 252 bytes, with no footer and a type row of int64 rather than BIGINT in the header. A second run of the agent query printed the same hash. The box form is the one a person at a terminal already knows. The agent form glues the SQL type to the column name and drops the alignment padding.

-markdown is not agent mode. With CLAUDECODE=1 and -markdown, stderr was empty and stdout was:

| answer |
|-------:|
| 42     |

The header has no type. The separator is alignment syntax. The cell is padded. A caller that passes -markdown to get the agent table gets neither the type suffix nor the JSON errors.

-json and -csv with CLAUDECODE=1 also left agent mode off. No banner. -csv printed a header line and the value:

answer
42

-box with CLAUDECODE=1 was a third shape: a box with no type row. That is box mode, not duckbox. Passing any output-mode flag is enough to suppress automatic detection. The flag does not have to be -markdown.

Explicit -agent -csv did not follow that detection rule. Stderr still announced agent mode. Stdout was CSV. A binder error under -agent -csv left stdout empty, wrote one JSON object to stderr, and exited 1. DUCKDB_AGENT_MODE=1 with -csv did the same split: banner on stderr, CSV on stdout, JSON on a binder error. -help says an output-mode flag leaves agent mode off. That sentence matches automatic detection. It does not match -agent or a forcing DUCKDB_AGENT_MODE value. .help agent is the text to read, and even that line has to be split into two rules: detection stays off when a format flag is present, and an explicit force still turns the mode on.

.help agent says a result of up to 1000 rows and 10000 bytes is rendered whole, and that the footer carries the row count and, when the whole result was read, an order-independent hash.

The footer itself starts earlier than the cap, and it is absent for the small queries a wrapper is most likely to use as a smoke test:

  • 0 rows printed a header and then 0 rows, hash 0000000000000000. Empty results get a footer. The hash is sixteen zero hex digits.
  • 9 rows printed the nine values and no footer.
  • 10 rows printed the footer and hash 885edae15671293b.
  • 11 rows printed a footer with a different hash, 2c9af2092a9adb4d.

A script that treats a missing footer as “agent mode is off” will misread every successful query of 1 to 9 rows. Those queries are in agent mode. They just do not print the footer. The 9 October post says the footer is printed for empty results, for results of 10 rows or more, and whenever output was capped. This run matches that rule. SELECT 42 is the example that hides it.

Order did not change the hash. Ten pairs from (1,'a') through (10,'j') produced 613ece3e477ba612 in ascending order and in descending order. A caller that wants to know whether a result changed can compare footers without storing both row orders, but only after the result is large enough to have a footer.

NULL and the string NULL render as the same cell text. They do not hash the same, and the header type gives the distinction away before the hash does. Ten SQL nulls used the header v:NULL and hash 0cee1a0601103eba. Ten copies of the string NULL used the header v:VARCHAR and hash 63cc1e5d54b92a7c. A parser that reads the cell and ignores the header type will collapse those two results. The hash will not.

A 200-row result of 80-character strings crossed the byte cap before the row cap. Stdout was 3530 bytes and ended with:

first 20 and last 20 of 200 rows (.maxbytes 0 for all), hash 7fed118f64b874f0

The omitted-row marker was a single row reading … 160 rows omitted …. The hash was present because the CLI still read the whole result. It did not print every row. The footer tells the caller how to get the rest: .maxbytes 0.

SELECT range FROM range(1000000000) did not print a hash. After the mode line, stderr was:

estimate: ~1000000000 rows read (range ~1000000000), ~1000000000 rows returned

Stdout ended with:

first 20 of at least 102400 rows (query stopped early; .maxrows -1 for all)

The exit code was 0. A job that treats exit 0 plus a Markdown table as a complete result will accept a stopped query. The footer is the line that says the result is incomplete, and it has no hash in this case. The 9 October post says the hash is omitted when the query is stopped early, because the hash would not cover the whole result. That matched. The estimate line is on stderr, with the mode announcement, so a stdout-only capture misses both the warning that the scan is large and the fact that agent mode is on.

Force flags accept more words than -help lists

-help says DUCKDB_AGENT_MODE=1 / 0 forces the mode on or off. The binary accepts a wider set, and it warns on at least one illegal value.

With no agent variable set, these values turned agent mode on: 1, true, True, TRUE, yes, YES, on, and ON. The stderr line for all of them was duckdb agent mode on (DUCKDB_AGENT_MODE is set); .help agent explains the output. It does not echo the word that was used.

These left the box renderer in place when no agent variable was set: an empty string, 2, and auto.

2 was the value that complained:

warning: ignoring DUCKDB_AGENT_MODE=2 (expected 1, 0 or auto)

auto is a legal value. Alone, it did not force the mode on, and it printed no warning. With CLAUDECODE=1 and stdout a pipe, auto left detection in charge and agent mode came on. So auto means detect, not on. A job that sets DUCKDB_AGENT_MODE=auto and expects a forced Markdown table will get the box renderer unless a recognized agent variable is also set and stdout is not a terminal.

false, off, and no forced the mode off even when CLAUDECODE=1 was set. They printed no warning. 0 did the same. A job that sets DUCKDB_AGENT_MODE=false to disable the mode will get the box renderer. A job that sets CLAUDECODE=0 to disable it will not. Those two controls are not interchangeable, and neither one is spelled the way a boolean environment variable usually is.

Command-line flags beat the environment for the cases that were run. -no-agent with CLAUDECODE=1 stayed in the box and printed no banner. -agent with no variable printed the Markdown table, including on a pty.

-version is not exempt from the banner. With AI_AGENT=1 and stdout a pipe, stdout was only the version line and stderr still carried the agent-mode line. A version probe that checks stderr for emptiness will fail in an agent environment even though the version string is intact. With the agent variables cleared, duckdb -version wrote the version to stdout and left stderr empty.

Do not put a token in AI_AGENT

.show prints the raw value of AI_AGENT and AGENT in the agent: field. With AI_AGENT=lab-token, the field was agent: lab-token. The product-named variables do not do that. CLAUDECODE prints claude-code, not the value that was assigned. If a wrapper stores a session secret in AI_AGENT or AGENT, .show and any transcript of it will contain that secret. Use a non-secret marker, or use DUCKDB_AGENT_MODE and -agent / -no-agent. Those do not echo an arbitrary string into .show. -agent prints agent: on.

A failed command on a pipe with no agent variable adds a hint after the normal error text:

hint: -agent renders errors as JSON and results compactly for AI coding agents (duckdb -help lists all options)

That hint is how an unrecognized agent is supposed to find the mode. It also means a log scraper that alerts on stderr from duckdb will see a hint on ordinary binder errors whenever stdout is a pipe and agent mode is off. The hint was absent once the mode was on. The error then was one JSON object, exit 1, empty stdout. In this run the binder object’s fields, in order, were exception_type, exception_message, error_subtype, position, location, and name. An unknown dot command used exception_type of Invalid Input and did not include error_subtype. A parser that requires error_subtype on every JSON error will reject shell errors that the CLI still reports as JSON.

Verify the renderer before parsing

Decide the renderer in the command, then read both streams.

To keep the box table no matter who set the environment:

duckdb -no-agent -c "SELECT 42 AS answer;"

To force the compact table and JSON errors:

duckdb -agent -c "SELECT 42 AS answer;"

If the caller needs CSV or JSON rows, pass that flag and still read stderr. -csv with automatic detection leaves agent mode off. -csv with -agent or DUCKDB_AGENT_MODE=1 keeps the mode on, keeps JSON errors, and changes only the row format. Do not use -markdown as a synonym for agent mode. It suppresses detection and prints a padded table with no type suffix.

After the command, check three facts rather than the exit code alone:

  • Exit status. A binder error is exit 1 with empty stdout in both modes. The stopped billion-row scan was exit 0.
  • The stderr mode line, if the job needs to know which renderer ran. It is not in the table. It is also present on duckdb -version when detection matches.
  • For agent mode, the footer when the result has 0 rows, 10 or more rows, or was capped. A 1- to 9-row success has no footer. A stopped query has a footer and no hash. A hash of sixteen zeros is the empty result, not a missing hash.

.show is the direct report. On a pipe it prints agent: off, agent: on, a product label, or, for AI_AGENT and AGENT, the variable’s value. A Markdown-looking table is not proof of agent mode.

The same binary can be pinned with the archive digest above. A later 2.0 patch may widen the word list or the footer rule. Re-run the empty-variable pipe, CLAUDECODE=0, DUCKDB_AGENT_MODE=false against CLAUDECODE=1, and the 9-row versus 10-row query before treating a new build as the same contract.

Sources

Previous post

Check three Valkey 9.1.3 results before the client upgrade