databricks-ai-functions: transform() over VARIANT sample fails with ARRAY type mismatch

[badr-db]

Problem or opportunity

The databricks-ai-functions skill's Document Processing Pipeline sample (Stage 1) parses
documents with ai_parse_document, flattens the element list, and filters out parse errors.
That stage has two independent VARIANT-handling bugs: Bug 1 raises an error; Bug 2 returns wrong
results with no error.

-- Stage 1 as written in the skill:
SELECT path,
  concat_ws('\n', transform(parsed:document:elements, e -> e:content::STRING)) AS text_blocks,  -- Bug 1
  parsed:error_status AS parse_error                                                             -- Bug 2
FROM ( SELECT path, ai_parse_document(content, map('version','2.0')) AS parsed FROM STREAM read_files(...) )
WHERE parsed:error_status IS NULL;                                                               -- Bug 2

Bug 1 — transform() over a VARIANT fails with an ARRAY type mismatch

ai_parse_document returns a VARIANT, and colon / variant_get navigation into it
(parsed:document:elements) stays VARIANT even though the underlying value is an array.
transform() (like explode()) requires a real ARRAY, so it raises:

[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] ... transform(...) ...
The first parameter requires the "ARRAY" type, however
"variant_get(parsed, '$.document.elements', 'VARIANT')" has the type "VARIANT".

Stage 1 is the first stage of the pipeline, so the sample fails on its first statement.

The skill is internally inconsistent here: it already applies the ARRAY<VARIANT> cast for the
parallel explode() case, and its Common Issues table already lists an "explode() fails on a
VARIANT" entry with this fix — but the transform() samples were not given the same treatment.

Bug 2 — error_status filter drops (or mislabels) every successfully-parsed row

ai_parse_document sets error_status to a VARIANT JSON-null on a clean parse — the key
is present with a JSON null value; it is not absent, and not SQL NULL. A JSON null inside a
VARIANT is not SQL NULL. Verified on a live workspace:

SELECT parse_json('null') IS NULL;          -- false
SELECT to_json(parse_json('null'));         -- 'null'  (the literal string)
SELECT is_variant_null(parse_json('null')); -- true

This breaks both places the sample touches error_status:

1. WHERE parsed:error_status IS NULL (SKILL.md Stage 1 filter; PySpark
   .filter("parse_error IS NULL")) — on a clean parse the predicate is false, so the filter
   drops every successfully-parsed row and keeps only failures. No error is raised.

2. parsed:error_status AS parse_error (the exposed column) — surfaces the VARIANT
   JSON-null. Stringified with to_json(...) or guarded with a raw IS NOT NULL check, a clean
   parse yields the string 'null' instead of SQL NULL, so a downstream
   error_message IS NOT NULL status flag marks every row an error. (Observed: a pipeline routed
   100% of rows to quarantine, each with error_message = 'null', and ai_extract was skipped
   because the fake error short-circuited the extract guard.)

Minimal repro of the filter dropping good rows:

SELECT parse_json('null') IS NULL AS keeps_row;  -- false -> WHERE ... IS NULL drops the row

Proposed change

Bug 1 — cast to ARRAY<VARIANT> before transform()

Matching the pattern the skill already uses for explode():

-- current (fails)
concat_ws('\n', transform(parsed:document:elements, e -> e:content::STRING)) AS text_blocks

-- fixed
concat_ws('\n', transform(parsed:document:elements::ARRAY<VARIANT>, e -> e:content::STRING)) AS text_blocks

(variant_get(parsed, '$.document.elements', 'ARRAY<VARIANT>') is an equivalent alternative,
consistent with the explode sample.)

Bug 2 — treat error_status as an array and collapse the JSON-null

error_status is documented as a per-page error array ({error_message, page_id} per page). The
correct test for a real parse error is "the error array is non-empty" — and casting to a concrete
ARRAY collapses the VARIANT JSON-null to SQL NULL (as a scalar ::STRING cast does for
ai_extract's error_message).

Fix the success filter:

-- current (drops every clean parse)
WHERE parsed:error_status IS NULL

-- fixed (absent / JSON-null / empty array = no error; non-empty array = real per-page errors)
WHERE coalesce(size(try_cast(parsed:error_status AS ARRAY<VARIANT>)), 0) = 0

Fix the exposed column so it is a nullable STRING (SQL NULL on success, JSON on real errors)
rather than a raw VARIANT that stringifies to 'null':

-- current (flags as 'null' on every clean parse)
parsed:error_status AS parse_error

-- fixed
CASE WHEN size(try_cast(parsed:error_status AS ARRAY<VARIANT>)) > 0
     THEN to_json(parsed:error_status) END AS parse_error

The PySpark .filter("parse_error IS NULL") gets the same treatment — filter on the derived
parse_error column above, or use the coalesce(size(try_cast(...)), 0) = 0 predicate directly.

Verified on a live workspace: the fixed predicate keeps clean parses and empty-array rows and
drops rows with a real per-page error; the fixed parse_error column is SQL NULL on a clean
parse and the JSON array string on a real error.

Optionally, add two rows to the Common Issues table, mirroring the existing explode() entry:

transform() fails on a VARIANT | transform needs ARRAY — cast first: transform(parsed:document:elements::ARRAY<VARIANT>, e -> ...).

ai_parse_document output all lands in quarantine / error_status filter drops every row | A JSON null inside a VARIANT is not SQL NULL, so parsed:error_status IS NULL is false on a clean parse and to_json(parsed:error_status) returns the string 'null'. Cast to a concrete type first: WHERE coalesce(size(try_cast(parsed:error_status AS ARRAY<VARIANT>)), 0) = 0, or use is_variant_null().

Affected skill or area

Skill: databricks-ai-functions (plugin databricks, observed in v0.2.12).

Bug 1 (un-cast transform(...)) appears in:

• skills/databricks-ai-functions/SKILL.md — "Document Processing Pipeline", Stage 1 (raw_parsed), the text_blocks expression.
• skills/databricks-ai-functions/references/1-task-functions.md — the same SQL sample.
• skills/databricks-ai-functions/references/1-task-functions.md — the PySpark expr("...") string form of the same sample.

Bug 2 (error_status JSON-null treated as SQL NULL) appears in:

• skills/databricks-ai-functions/SKILL.md — "Document Processing Pipeline", Stage 1: WHERE parsed:error_status IS NULL and parsed:error_status AS parse_error.
• skills/databricks-ai-functions/references/1-task-functions.md (~line 359) — parsed:error_status AS parse_error (SQL sample).
• skills/databricks-ai-functions/references/1-task-functions.md (~lines 394 & 396) — "parsed:error_status AS parse_error" and .filter("parse_error IS NULL") (PySpark sample).

For contrast, the correct ARRAY<VARIANT> cast is already present for explode in SKILL.md
(the parsed_chunks / RAG sample) and its Common Issues table entry, and in the corresponding
references/1-task-functions.md ai_prep_search examples.

Additional context

Both bugs come from VARIANT semantics, and each is worth a Common Issues entry:

• Array-consuming functions need an explicit ARRAY cast. VARIANT navigation does not
  auto-coerce to ARRAY, so transform, explode, filter, aggregate, … need an explicit
  ::ARRAY<...> cast or variant_get(..., 'ARRAY<...>'). (Bug 1; same root cause as the
  documented explode() gotcha.)
• A JSON null inside a VARIANT is not SQL NULL. IS [NOT] NULL checks and to_json()
  on a raw VARIANT behave differently from plain SQL (parse_json('null') IS NULL → false;
  to_json(parse_json('null')) → 'null'). Cast to a concrete type (a scalar ::STRING or an
  ::ARRAY<VARIANT> cast collapses the JSON-null to SQL NULL), or use is_variant_null().
  (Bug 2.)

No secrets, customer data, or sensitive identifiers are included in this report.