# Halo SQL dialect and query discipline

> **Shared platform corpus** — `halo-platform/` is vendored byte-identical across the plugins that carry it; a consistency check enforces identity. Do not hand-edit a vendored copy — edit the canonical copy (see `halo-platform/README.md`) and re-run the corpus sync.

Halo SQL runs in three execution contexts with different dialects and constraints. Identify the context before writing a line of SQL.

| Context | Dialect | Write access | Notes |
|---|---|---|---|
| **MindsDB proxy** (`mcp__HaloSQL__query`) | MySQL-compatible parser over the MSSQL backend | read-only in practice | Schema discovery + simple queries. Tables prefixed `haloitsm.`. |
| **Halo report builder** (report source SQL) | raw T-SQL, wrapped by Halo in an outer subquery | read-only | Powers reports and runbook-backed MCP read tools. |
| **Runbook Native SQL step** (`auto_action: 18`) | raw T-SQL inside a runbook | **READ-ONLY — never write through it** | Drives iteration loops (query → bind → iterate); writes go through Halo API Action steps, never SQL. |

## 1. The ORDER BY rule

**Never include `ORDER BY` in Halo report SQL or in ad-hoc test/lookup SQL. Sort downstream** — in the report UI, the consuming tool, or in memory after the read.

Why: Halo wraps every report's source query in an outer subquery for paging and post-processing, and T-SQL forbids a bare `ORDER BY` inside a subquery (it must be paired with `TOP`, `OFFSET`, `FETCH`, or `FOR XML`). The failure mode is split — the report definition **saves cleanly** but **fails at runtime** — which makes the bug cheap to introduce and expensive to discover. The report framework owns ordering; source SQL should not.

If you inherit legacy SQL that must keep its `ORDER BY`, the minimum grammar fix is appending `OFFSET 0 ROWS` (a no-op for output). New SQL omits `ORDER BY` entirely.

## 2. MindsDB dialect (the `mcp__HaloSQL__query` surface)

MindsDB acts as a SQL proxy to the Halo MSSQL database. The Halo data lives in the `haloitsm` database — always prefix table names (`haloitsm.FAULTS`), including EVERY table in a JOIN.

| Feature | Behavior |
|---|---|
| `DESCRIBE table` | Does NOT work. Use `SHOW COLUMNS FROM haloitsm.TABLE` |
| `LIMIT N` | Works (translated to `TOP N`) |
| `SHOW TABLES FROM haloitsm` / `SHOW COLUMNS FROM haloitsm.T` | Work — the schema-discovery tools |
| Column case | Case-sensitive (MSSQL backend) |
| JOINs | LEFT JOIN reliable; use table aliases |
| Subqueries | Supported; keep simple |

### MSSQL → MindsDB translation

Halo dashboard queries use native MSSQL syntax; MindsDB's parser is MySQL-compatible. Translate:

| MSSQL | MindsDB equivalent |
|---|---|
| `[bracket aliases]` | `AS AliasName` |
| `CROSS APPLY (subquery)` | inline expression or subquery in SELECT |
| `FORMAT(date, 'pattern')` | return raw datetime; format in presentation |
| `GETDATE()` | `NOW()` |
| `ISNULL(a, b)` | `COALESCE(a, b)` |
| `CAST(x AS NVARCHAR(n))` | `CAST(x AS CHAR)` or omit |
| `DATEDIFF(minute, a, b)` | not supported — compare dates directly |
| `$AGENTID` / `$USERID` | replace with a concrete id or remove the filter |
| `TOP N` | `LIMIT N` |
| `+` (string concat) | `CONCAT()` |
| `TRY_CAST` | not supported |
| `WITH ... AS` (CTE) | not supported — rewrite as subqueries or multiple queries |
| `PIVOT` / `UNPIVOT` | not supported — `CASE WHEN` + `GROUP BY` |
| `ORDER BY` in subqueries | not supported — remove (see § 1) |

**Scope:** MindsDB is best for schema discovery and simple queries. Complex queries needing CTEs, PIVOT, or advanced T-SQL are written as raw T-SQL for Halo's report builder (drop the `haloitsm.` prefix when running inside Halo).

## 3. Report-builder T-SQL constraints

### `STRING_AGG` rules

1. **Return-type overflow.** `STRING_AGG` returns `nvarchar(8000)` unless its first argument is explicitly `nvarchar(MAX)`. Aggregating user content overflows with `Msg 8152: String or binary data would be truncated.` — wrap the column: `STRING_AGG(CAST(col AS NVARCHAR(MAX)), ' | ')`.
2. **No subquery (or nested aggregate) inside the argument** (`Msg 130`). Materialize the inner aggregation in `OUTER APPLY` and `STRING_AGG` the resulting column at the outer query:

```sql
SELECT rt.rtdesc,
       STRING_AGG(CAST(oa.field_list AS NVARCHAR(MAX)), ' | ') AS Fields
FROM REQUESTTYPE rt
OUTER APPLY (
  SELECT STRING_AGG(CAST(fi.fname AS NVARCHAR(MAX)), ', ') AS field_list
  FROM REQUESTTYPEFIELD rtf
  INNER JOIN FIELDINFO fi ON rtf.RTFFieldId = fi.FIid
  WHERE rtf.RTFRTid = rt.RTid
) oa
GROUP BY rt.rtdesc
```

(MindsDB does not support `STRING_AGG`; these rules apply to report-builder SQL only.)

### `STRING_SPLIT` / string functions on Memo (`ntext`) columns

`STRING_SPLIT`, `LTRIM`, and `RTRIM` all REJECT `ntext` arguments at compile time — and Halo stores Memo CFs as `ntext` (any CF holding values over the 255-char text cap, e.g. long framework-mapping columns). Apply the same explicit cast as the `STRING_AGG` rule: `STRING_SPLIT(CAST(col AS NVARCHAR(MAX)), ',')`.

**Statement-level binding defeats branch guards.** SQL Server binds and type-checks the WHOLE statement before evaluating any branch, so a `CASE WHEN` guard does NOT protect its other branches from a type error: a run that would never READ the offending `ntext` column on this input still fails at compile time because another branch references it uncast (live-diagnosed 2026-08-07 — a CMMC-only invocation failed on a FedRAMP column it would never have selected). Cast every `ntext`-backed column the statement MENTIONS, not just the branch you expect to run.

### Portable predicate pattern — filter on resolved `fvalue`, not `fcode`

When a `WHERE` clause depends on a custom-field value resolved via the LOOKUP table, compare against the resolved `fvalue` display string, not the raw `fcode` integer. `fcode` integers are tenant-specific and shift across Halo versions and lookup re-mints; `fvalue` strings are stable:

```sql
-- Portable:
WHERE (SELECT fvalue FROM Lookup WHERE fid = 237 AND fcode = k.CFValidationMethod) = 'Manual'
-- Fragile:
WHERE k.CFValidationMethod = 4
```

## 4. Probe-first rule for ticket custom fields (MANDATORY)

Newer ticket CFs are NOT columns on FAULTS — Halo spills them into overflow tables (`FaultsCustom1`, then `FaultsCustom2`, …) joined `LEFT JOIN FaultsCustom1 ON Faults.faultid = FaultsCustom1.pkid`. `FIELDINFO` / `ticket_type_fields` lists a CF without revealing its storage table, and bundled schema dumps are point-in-time floors, not truth. Before referencing any CF not already seen working in a deployed query, probe:

```sql
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'CF<Name>%'
```

**Never scope the probe to `TABLE_NAME = 'FAULTS'`** — a scoped probe cannot see the overflow tables. Read expression by DATA_TYPE: `nvarchar` → raw; `ntext`/`text` → `CAST(... AS NVARCHAR(MAX))`; `image` → use the `CF<Name>PLAIN` companion (direct CAST from `image` is illegal T-SQL). Failure signature of a wrong guess: the MCP tool/report returns "Failed to load data"; the report editor shows `Invalid column name 'CF...'`.

## 5. CF rich-text column variants — bare / HTML / PLAIN

Many rich-text custom fields are exposed as a triple of columns: bare `CFFieldName`, `CFFieldNameHTML`, and `CFFieldNamePLAIN`. The `HTML`/`PLAIN` suffixes are legacy and being phased out by Halo; `PLAIN` columns are frequently empty even when the field has data.

1. **Default to the bare `CFFieldName`** (e.g. `Site.CFDescription`, `Site.CFAuthorizationBoundary`).
2. If the bare column returns empty, try the `HTML` variant before `PLAIN`.
3. Treat `PLAIN` as expected-empty unless verified on a known-populated row in your tenant.
4. Before committing a column reference, run a `SELECT TOP 5` (report context) / `LIMIT 5` (MindsDB) against a row known to have the value populated.

If a consumer needs HTML-stripped text and the bare column is HTML, strip client-side — don't rely on `CFFieldNamePLAIN`.

## 6. Query-construction workflow

1. Identify tables — consult the data-model corpus file (`halo-platform/data-model.md`) and, where the dev-tooling reference set is present, `halo/halo-tables.json` for full column detail.
2. Resolve IDs — lookups live in the LOOKUP table (`fid`/`fcode`→`fvalue`); dev-tooling `halo/halo-lookups.json` catalogs the fid families where present.
3. Reuse a proven recipe where one exists (`halo/halo-query-patterns.json` in the dev-tooling set) before writing from scratch.
4. Write with the `haloitsm.` prefix, exact column casing, scalar subqueries for LOOKUP resolution.
5. Execute via `mcp__HaloSQL__query`; on a column error, `SHOW COLUMNS FROM haloitsm.TABLE`.

## 7. Anti-patterns

| Anti-pattern | Problem | Fix |
|---|---|---|
| `ORDER BY` in report/test SQL | Runtime failure under Halo's subquery wrap; framework owns ordering | Omit; sort downstream (§ 1) |
| `SELECT *` without LIMIT | FAULTS has 600+ columns | Select specific columns |
| Guessing column names | Case-sensitive MSSQL errors | Check the schema references first |
| `DESCRIBE table` | Unsupported in MindsDB | `SHOW COLUMNS FROM` |
| Missing `haloitsm.` prefix (incl. in JOINs) | Table-not-found | Prefix every table |
| `WHERE active = 1` on UNAME | Column is `Uisdisabled` (inverted) | `WHERE Uisdisabled = 0` |
| LEFT JOIN for LOOKUP resolution | Deviates from Halo's idiomatic pattern | Scalar subquery: `(SELECT fvalue FROM haloitsm.Lookup WHERE fid=N AND fcode=Column LIMIT 1)`; JOIN only to filter/GROUP BY on the resolved value |
| Rewriting a user's working SQL pattern | They already have an established idiom | Replicate, don't replace |
| Referencing an unproven CF as `Faults.CF<Name>` | Newer CFs live in overflow tables | Unscoped INFORMATION_SCHEMA probe first (§ 4) |
| Writing through a runbook Native SQL step | `auto_action: 18` is read-only by doctrine | Writes ride Halo API Action steps |
