SQL Test Reference — Test List
This page lists all 25 procedures in the "SQL Tests Hgd" codeunit and what each verifies. See the Overview for the harness, the seeded test tables, and the assertion helpers.
Queries and Filtering
| Test |
What it verifies |
BasicSelectTests |
SELECT *, equality/>/<>/>= in WHERE, TOP n, IS NULL/IS NOT NULL, and a column AS alias. |
WhereOrNotTests |
OR on the same field, OR across different fields (must not behave as AND), AND/OR with parentheses, and NOT (...) over a simple condition and over an OR. |
OrderByTests |
Numeric ordering (not lexicographic), ASC/DESC, multi-column ordering with tie-breaks, TOP applied after ordering, and date and text ordering. |
ExpressionAndPredicateTests |
Arithmetic (Amount + Quantity, Quantity * 2), CASE WHEN … THEN … ELSE … END, LIKE (prefix/suffix/contains), BETWEEN, IN (list), IN with a filter metacharacter, NOT IN, field-to-field comparison, and duplicate select columns. |
ResidualWhereAndTypeTests |
Typed filters resolved locale-independently: date and decimal =/>/BETWEEN, BETWEEN on a Code field, IN on an integer field, WHERE on a joined column (residual), an empty scalar subquery matching nothing, LIKE with a metacharacter (with and without a wildcard), the _ single-char wildcard, IS NULL/IS NOT NULL on non-text fields, and an escaped '' quote in a literal. |
ExpressionAndLexerTests |
Expression validation (unknown column in arithmetic or CASE, aggregate inside an expression, a bare column as a condition); numeric-looking text stays text while numeric CASE/literal expressions are Decimal and a numeric CASE without a match is null; double-quoted identifiers, a non-ASCII alias, unterminated string / bracket / quote errors; SELECT *, col; TOP validation, the Max Rows cap on TOP, TOP 0; COUNT(DISTINCT) and literal aggregate arguments rejected. |
PaginationAndEdgeTests |
OFFSET … ROWS FETCH NEXT … ROWS ONLY, OFFSET alone, error reporting for an unknown table / select column / where column, partial table and column names not binding, and that a no-match WHERE succeeds with zero rows. |
ResidualLiteralTypingTests |
Literals compared by the column's type in the residual path, matching the native path: booleans as 1/'TRUE' under OR, NOT and IN, options by ordinal and caption, dates (equality and ordering), a literal on the left side (200 < Amount), case-insensitive text equality, boolean IS NULL, the same conversion inside CASE WHEN, and in an UPDATE … WHERE. |
Joins
| Test |
What it verifies |
JoinTests |
INNER JOIN (resolved as a lookup — first matching line — dropping unmatched headers), LEFT JOIN (keeps all headers, nulls where no match), self join via alias, LEFT self join (blank links → null), chained joins (second hop reads the first join), table-qualified h.*, and that unsupported RIGHT JOIN fails loudly. |
CrossJoinTests |
CROSS JOIN full product (5 × 5 = 25), TOP over a cross join, WHERE on the cross-joined table, and a field-to-field WHERE turning the cross join into an inner join. |
JoinValidationTests |
ON clauses that cannot be executed are errors (unqualified columns, a literal, OR, a non-equality, a missing ON, an unknown alias or column, not referencing the joined table, CROSS JOIN with ON); valid joins with reversed sides or two AND-ed conditions still work; FROM A, B is a cross join (with and without aliases, with a WHERE); three cross-joined tables and aggregates over a cross join are rejected. |
Aggregation and Grouping
| Test |
What it verifies |
AggregateTests |
COUNT(*), COUNT with WHERE, COUNT(column) skipping blanks, SUM/AVG, MIN/MAX over numeric, text, and date columns, aggregates respecting an OR WHERE, and aggregates over a joined column and with an INNER JOIN. |
GroupByHavingTests |
GROUP BY with COUNT/SUM, HAVING by aggregate alias and by COUNT(*), WHERE applied before grouping, and GROUP BY a joined column. |
GroupedOutputAndHavingTests |
Grouped output follows the SELECT list with aliases; unselected GROUP BY columns are not output but still drive ORDER BY and HAVING; HAVING on aggregates that are not selected, matched by function and argument; HAVING with IN/BETWEEN/LIKE/IS NULL; errors for an ungrouped plain column, an expression over an aggregate, an ungrouped HAVING column, an unknown function, HAVING without grouping, and ORDER BY an ungrouped column; identical aggregates (COUNT(), COUNT()); GROUP BY 1 / ORDER BY 2 ordinals and an out-of-range ordinal. |
GroupingOrderingEdgeTests |
Multi-column GROUP BY, GROUP BY a dotted field name, ORDER BY an alias-qualified or select-alias column not in the list (hidden sort column stripped), grouped rows ordered by an aggregate alias, aggregates over an empty set (COUNT=0, MIN=null) versus GROUP BY over an empty set (no rows), DISTINCT with a hidden ORDER BY, cross join with ORDER BY/DISTINCT/WHERE, a computed expression over a joined column, division / unary minus / division-by-zero (yields 0), and HAVING with a non-COUNT aggregate and with AND. |
Subqueries and Set Operations
| Test |
What it verifies |
SubqueryTests |
Scalar subqueries (= (SELECT MAX …), <), IN (subquery), an empty IN subquery matching nothing (not everything), EXISTS/NOT EXISTS (uncorrelated), and EXISTS combined with another condition. |
DistinctSetOperationTests |
SELECT DISTINCT, and the UNION, UNION ALL, INTERSECT, and EXCEPT set operators. |
SetOperationOrderingTests |
ORDER BY (by name and by position) and OFFSET/FETCH after a UNION apply to the combined result; right-side rows take the left side's column names so UNION deduplicates across differently named columns and UNION ALL keeps both; a column-count mismatch is an error; Max Rows caps the combined result. |
ReviewFindingsTests |
Regression guards from the second review: IN without a list (also in DELETE) and a missing ) are parse errors; set operations reject DELETE/UPDATE operands without executing them; * and ? are literal in LIKE (WHERE and HAVING); INTERSECT, EXCEPT and a combined ORDER BY are computed on full inputs under a Max Rows of 1; two ON equalities on one joined field must agree; a qualified ORDER BY b.[No.] sorts by that table's column (plain and grouped); INSERT value-count mismatches write nothing; DISTINCT keys with ` |
in the data; DISTINCT` over grouped rows. |
SecondSweepTests |
Regression guards from the third review: literal-vs-literal comparisons by type; subquery and HAVING values containing " and &#;; LIKE '%' against data containing %; INSERT/UPDATE run field OnValidate (via the Validate Marker test field); duplicate result names (Name_2, SUM_2) in ORDER BY, ordinals and HAVING; an alias shadowing a keyed field; Option comparisons by ordinal with caption display; HAVING and MIN/MAX typed comparisons incl. boolean literals; COUNT(field) skipping 0 and false; grouped boolean values as JSON booleans and a LEFT JOIN miss as null; NULL into a Guid; sort keys containing the separator; malformed VALUES/SET; reserved aliases; aggregate argument counts. |
ThirdSweepTests |
Regression guards from the fourth review: DISTINCT keys use the invariant value format (dates a century apart stay distinct, Options dedupe by ordinal but display captions); HAVING compares by field type like WHERE (Option by ordinal or caption incl. IN, Code vs number literal textual, Integer vs text literal numeric); HAVING ... IS NULL matches 0, false and blank on grouped columns; MAX(Blocked) is a JSON boolean with Boolean column type and works in HAVING MAX(Blocked) = 1; MIN(date) value; case-only duplicate aliases (x, X) get suffixes so ORDER BY 2 sorts the right column; INTERSECT ALL / EXCEPT ALL keep duplicates while the plain forms dedupe; a ten-deep chain of dependent self-joins resolves; an INSERT with an unterminated column list is a parse error that writes nothing; DISTINCT over a numeric CASE yielding 1 and 1.0 is one row; LIKE on an Option matches the caption in WHERE (plain and inside OR) as in HAVING; an alias [h.Name] ordered by h.Name DESC; Codes 2, 10, 1A, A1 sort numbers-then-text in both directions. |
PagingAndSubqueryLimitTests |
OFFSET/FETCH paging past the Max Rows cap (native sort, in-memory sort, grouped rows), FETCH NEXT 0 ROWS, Max Rows capping the page; IN, scalar and EXISTS subqueries unaffected by a Max Rows of 1; IN and NOT IN over a 600-value subquery result on Code and Integer fields (hash-set path), also inside a residual OR. |
Data Modification (DML)
| Test |
What it verifies |
InsertUpdateDeleteTests |
INSERT with typed decimal/date/option/BigInteger values (invariant formats), multi-row INSERT, UPDATE, an UPDATE that modifies the field it filters on (iteration safety), UPDATE of an option field by name, UPDATE with OR, DELETE, DELETE with OR, and a BigInteger round-trip through SELECT. |
DmlAndErrorEdgeTests |
SetMaxRows capping a SELECT, DELETE with an IN subquery, UPDATE with a scalar subquery, UPDATE … SET NULL, UPDATE/DELETE without WHERE (all rows), and error reporting for a column+aggregate without GROUP BY, an unsupported function (UPPER), SUM(*), a derived table, an INSERT with an unknown column, and an unknown ORDER BY column. |
Parsing and Write Safety
| Test |
What it verifies |
ParserAndWriteSafetyTests |
Leftover tokens are a parse error (arithmetic in WHERE or SET, a stray word after ORDER BY, ORDER BY COUNT(*)), a trailing semicolon is accepted, UNION chains, negative literals in WHERE/IN/BETWEEN/VALUES/SET, and that SET/VALUES reject a column reference, an unquoted word and a subquery without touching the record while literal writes (including NULL) still work. |
| Test |
What it verifies |
ColumnMetadataTests |
Declared column types follow the field schema (Code/Decimal/Date/Boolean/Option/Integer/BigInteger), preserved column order, alias names and types, empty result sets still carrying column metadata, SELECT * column count, aggregate and computed-expression result types, GROUP BY column types, duplicate names suffixed (Name_2), joined-column types, a hidden ORDER BY column excluded from metadata, and set operations carrying the left query's column shape. |
