SQL Test Reference — Overview
The ToolBox SQL engine (lexer → parser → runner) has its own intensive test suite. This chapter is a reference to that suite: it explains the harness and lists every test with what it verifies, so you can see which SQL behaviors are guaranteed and read the tests as worked examples of the supported dialect.
The tests live in the codeunit "SQL Tests Hgd" (object 70310439) in the ToolBox app (src/Tests/SQL Tests.al). There are 26 test procedures covering SELECT, WHERE/OR/ NOT, ORDER BY, joins, aggregates, GROUP BY/HAVING, subqueries, set operations, DML (INSERT/UPDATE/DELETE), expressions and predicates, column metadata, typed filters, pagination, and error reporting.
How the Tests Run
Unlike the interpreter tests, the SQL tests are not [Test]-attributed. Each is a plain public procedure that seeds its own data and raises Error() on the first failed assertion, so they can be wrapped in TryFunctions. They are registered in the "Run All Tests Hgd" codeunit (each as a TrySelectTests()-style wrapper, e.g. TryBasicSelectTests), which runs the whole suite and reports pass/fail per procedure.
Every test runs its SQL through the engine's entry point:
// The Exec helper the tests use:
R := SQL.ExecuteSQL(sqlText); // SQL is Codeunit "SQL Hgd"; R is a JsonObject resultExecuteSQL returns a JsonObject describing the result — its columns (with declared types), its rows, the affected-row count for DML, and any error. The assertion helpers read that object.
Test Data
Each test calls Seed() first, which clears and repopulates two dedicated tables. Seeding its own data makes every test independent and order-free.
SQL Test Header Hgd (table 70310439)
| Field | Type | Field | Type |
|---|---|---|---|
| No. | Code[20] | Blocked | Boolean |
| Name | Text[100] | Category | Code[20] |
| Amount | Decimal | Big Value | BigInteger |
| Quantity | Integer | Status | Option |
| Posting Date | Date | Linked No. | Code[20] |
Five headers are seeded (ORD001–ORD005) with a spread of categories, amounts, quantities, dates, blocked flags, an out-of-range BigInteger, option statuses, and self-referencing Linked No. values (for self-join tests). ORD005 has a blank Category (for IS NULL/COUNT tests).
SQL Test Line Hgd (table 70310440)
| Field | Type |
|---|---|
| Header No. | Code[20] |
| Line No. | Integer |
| Description | Text[100] |
| Line Amount | Decimal |
Five lines are seeded under ORD001–ORD003; ORD004 and ORD005 intentionally have no lines, so INNER JOIN vs LEFT JOIN behavior can be verified.
Assertion Helpers
| Helper | What it checks |
|---|---|
AssertSuccess(R, name) / AssertFailure(R, name) |
The statement succeeded / reported an error |
AssertRowCount(R, n, name) |
The result has n rows |
AssertAffected(R, n, name) |
A DML statement affected n rows |
AssertCell(R, rowIdx, col, expected, name) |
A cell's text value (rows 0-based) |
AssertCellWhere(R, keyCol, keyVal, valCol, expected, name) |
A cell located by a key column's value |
AssertColumnCount(R, n, name) |
The result exposes n columns |
AssertColumnType(R, col, expected, name) |
A column's declared type (from the field schema) |
Chapter Map
- SQL Test List — every test and what it verifies