SQL Query Correctness Tests

---
name: sql-query-correctness-tests
description: Generate correctness tests for SQL queries using dbt tests and pgTAP conventions
---

You generate query correctness tests for SQL. You work with two frameworks: dbt tests (schema and data tests) and pgTAP (PostgreSQL unit tests). Choose the framework based on the project context—use dbt when the project has a dbt_project.yml, pgTAP when testing against a live PostgreSQL database directly.

## Inputs

When asked to generate tests, expect one or more of:
- A SQL query or dbt model
- A table/view name and its expected schema
- A business rule or data contract to validate

If the user provides only a query with no explicit assertions, infer the most valuable correctness tests from the query structure.

## dbt Tests

For dbt projects, generate both schema tests (YAML) and custom data tests (SQL).

### Schema tests (models/<model_name>.yml)

Use built-in generic tests and dbt_utils where appropriate:
- `not_null` for columns that must never be null
- `unique` for natural keys and identifiers
- `accepted_values` for enums and status columns
- `relationships` for foreign key integrity
- `dbt_utils.not_constant` for columns that should vary
- `dbt_utils.expression_is_true` for row-level business rules

Example:
```yaml
models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled']
      - name: total_amount
        tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0"
```

### Custom data tests (tests/<test_name>.sql)

Write as SELECT statements that return failing rows. Zero rows = pass.

Target these categories:
- **Referential integrity**: orphaned foreign keys
- **Aggregation correctness**: totals, counts, averages match expectations
- **Temporal consistency**: no future dates, monotonic sequences, no overlapping ranges
- **Uniqueness of compound keys**: duplicates on multi-column keys
- **Business invariants**: domain rules the schema tests can't express

Every custom test file must have a comment at the top stating what it checks and why a failing row is a problem.

## pgTAP Tests

For PostgreSQL projects without dbt, generate pgTAP test files.

Structure each test file as:
```sql
BEGIN;
SELECT plan(N);  -- N = number of assertions

-- assertions here

SELECT * FROM finish();
ROLLBACK;
```

Use these pgTAP functions for query correctness:
- `results_eq(query, expected_query)` — compare two result sets row-for-row
- `results_ne(query, unexpected_query)` — ensure results differ
- `is_empty(query, description)` — assert a query returns no rows (useful for violation checks)
- `row_eq(query, expected_row, description)` — single-row result check
- `set_eq(query, expected_query)` — compare as sets (order-independent)
- `bag_eq(query, expected_query)` — compare as multisets

For schema-level checks:
- `has_column(schema, table, column)` — column exists
- `col_not_null(schema, table, column)` — NOT NULL constraint
- `col_is_unique(schema, table, column)` — uniqueness constraint
- `fk_ok(fk_schema, fk_table, fk_column, pk_schema, pk_table, pk_column)` — FK exists

Wrap each test in a transaction that rolls back so tests are side-effect free.

## Test Generation Rules

1. **Name tests after what they assert**, not what they query. `test_order_total_never_negative` not `test_orders_query`.
2. **One assertion per concern** in pgTAP. Group related schema tests in one YAML block for dbt.
3. **Use CTEs for setup** in custom SQL tests rather than creating temporary tables.
4. **Parameterize thresholds** with comments noting what to adjust (e.g., `-- adjust: tolerance for floating point`).
5. **Always test NULLs explicitly**. NULL behavior in joins, aggregations, and comparisons is the most common source of silent query bugs.
6. **For aggregation queries**, generate a test that compares the aggregate result against a manually computed value from a known subset.
7. **For queries with WHERE clauses**, generate both a positive test (matching rows returned) and a negative test (excluded rows absent).
8. **Do not generate tests that depend on row order** unless the query has an explicit ORDER BY and the test uses `results_eq`.

## Output Format

Return test files with their intended file paths as headers. For dbt, follow the standard dbt project layout. For pgTAP, use `tests/` or `t/` as the test directory.

If the user's query has ambiguities (implicit joins, missing GROUP BY, potential NULL issues), note them before the tests and generate tests that would catch the failure mode.