specs/F004-VERIFICATION_SPEC.md

# Verification Specification

**Feature:** F004
**Generated from:** specs/F004-VERIFICATION_INPUT.json
**Generated:** 2026-03-24

---

## 1. Unit Tests

### EnrichedQuestionRecord (Type)

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_record_all_fields_present | All 9 required fields populated | Full valid record dict | All fields accessible, no missing keys | happy |
| test_record_question_id_format | question_id matches `{db_id}_{split}_{index:03d}` | `"concert_singer_train_007"` | Passes regex `^[a-z_]+_(train|eval)_\d{3}$` | happy |
| test_record_question_id_invalid | Rejects malformed question_id | `"bad-id"` | Validation error or detectable as invalid | error |
| test_record_answer_type_enum | answer_type is one of allowed values | `"integer"`, `"float"`, `"string"`, `"list"`, `"table"` | Each accepted | happy |
| test_record_answer_type_invalid | Rejects unknown answer_type | `"boolean"` | Rejected or flagged | error |
| test_record_difficulty_enum | difficulty is one of allowed values | `"easy"`, `"medium"`, `"hard"` | Each accepted | happy |
| test_record_difficulty_invalid | Rejects unknown difficulty | `"extreme"` | Rejected or flagged | error |
| test_record_split_enum | split is one of allowed values | `"train"`, `"eval"` | Each accepted | happy |
| test_record_split_invalid | Rejects unknown split | `"test"` | Rejected or flagged | error |
| test_record_tables_involved_nonempty | tables_involved has at least one entry | `["students"]` | Accepted | happy |
| test_record_tables_involved_empty | Empty tables_involved is rejected | `[]` | Rejected or flagged by validation | edge |
| test_record_gold_sql_nonempty | gold_sql is a non-empty string | `"SELECT COUNT(*) FROM students"` | Accepted | happy |
| test_record_gold_sql_empty | Empty gold_sql is rejected | `""` | Rejected or flagged | edge |
| test_record_gold_answer_types | gold_answer can hold int, float, str, list, list-of-lists | `42`, `3.14`, `"Alice"`, `[1,2]`, `[[1,"a"]]` | Each stored and retrievable | happy |

**Run:** `pytest tests/test_f004_dataset.py::TestEnrichedQuestionRecord -v`

---

### classify_answer_type

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_classify_integer | Single integer answer | `42` | `"integer"` | happy |
| test_classify_float | Single float answer | `3.14` | `"float"` | happy |
| test_classify_string | Single string answer | `"Alice"` | `"string"` | happy |
| test_classify_list | Flat list (single column, multiple rows) | `[1, 2, 3]` | `"list"` | happy |
| test_classify_table | List of tuples/lists (multi-column result) | `[(1, "a"), (2, "b")]` | `"table"` | happy |
| test_classify_none | None/null answer | `None` | Defined behavior (error or specific type) | edge |
| test_classify_empty_list | Empty list | `[]` | `"list"` or defined behavior | edge |
| test_classify_single_row_tuple | Single-element tuple | `(42,)` | `"integer"` (unwrapped) or `"list"` | edge |
| test_classify_nested_single | Single-row multi-column | `[(1, "a")]` | `"table"` | edge |
| test_classify_boolean | Boolean answer | `True` | Defined fallback behavior | edge |

**Run:** `pytest tests/test_f004_dataset.py::TestClassifyAnswerType -v`

---

### extract_tables_involved

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_extract_single_table | Simple SELECT from one table | `"SELECT * FROM students"` | `["students"]` | happy |
| test_extract_join | JOIN with two tables | `"SELECT s.name FROM students s JOIN courses c ON s.id = c.student_id"` | `["courses", "students"]` (sorted) | happy |
| test_extract_subquery | Subquery referencing different table | `"SELECT * FROM students WHERE id IN (SELECT student_id FROM enrollments)"` | `["enrollments", "students"]` (sorted) | happy |
| test_extract_deduplication | Same table referenced multiple times | `"SELECT a.x, b.y FROM t1 a JOIN t1 b ON a.id = b.id"` | `["t1"]` (deduplicated) | happy |
| test_extract_case_insensitive | Mixed case SQL keywords | `"select * FROM Students"` | `["Students"]` or `["students"]` (consistent) | edge |
| test_extract_with_alias | Table alias should resolve to table name | `"SELECT s.name FROM students AS s"` | `["students"]` | edge |
| test_extract_multiple_joins | Three or more tables joined | `"SELECT * FROM a JOIN b ON a.id=b.id JOIN c ON b.id=c.id"` | `["a", "b", "c"]` (sorted) | happy |
| test_extract_empty_sql | Empty SQL string | `""` | `[]` or error | edge |
| test_extract_no_from | SQL without FROM clause | `"SELECT 1+1"` | `[]` | edge |

**Run:** `pytest tests/test_f004_dataset.py::TestExtractTablesInvolved -v`

---

### classify_difficulty

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_difficulty_easy_1_table | 1 table involved | `["students"]` | `"easy"` | happy |
| test_difficulty_easy_2_tables | 2 tables involved | `["students", "courses"]` | `"easy"` | happy |
| test_difficulty_medium_3_tables | 3 tables involved | `["a", "b", "c"]` | `"medium"` | happy |
| test_difficulty_hard_4_tables | 4 tables involved | `["a", "b", "c", "d"]` | `"hard"` | happy |
| test_difficulty_hard_many_tables | 6+ tables involved | `["a", "b", "c", "d", "e", "f"]` | `"hard"` | happy |
| test_difficulty_empty_tables | 0 tables (edge case) | `[]` | Defined behavior (error or `"easy"`) | edge |

**Run:** `pytest tests/test_f004_dataset.py::TestClassifyDifficulty -v`

---

### compute_gold_answer

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_compute_valid_select | Valid SELECT on real SQLite DB | `"SELECT COUNT(*) FROM singer"`, valid db_path | Integer result | happy |
| test_compute_multirow | Query returning multiple rows | `"SELECT * FROM singer LIMIT 3"`, valid db_path | List/table result | happy |
| test_compute_invalid_sql | Syntactically invalid SQL | `"SELCT * FORM x"`, valid db_path | Raises `sqlite3.Error` | error |
| test_compute_missing_table | SQL references non-existent table | `"SELECT * FROM nonexistent"`, valid db_path | Raises `sqlite3.Error` | error |
| test_compute_missing_db | Database file does not exist | `"SELECT 1"`, `/tmp/nonexistent.sqlite` | Raises `sqlite3.Error` or `FileNotFoundError` | error |
| test_compute_empty_result | Query returns no rows | `"SELECT * FROM singer WHERE 1=0"`, valid db_path | Empty result (e.g., `[]`) | edge |
| test_compute_null_result | Query returning NULL | `"SELECT NULL"`, valid db_path | `None` or defined null handling | edge |

**Run:** `pytest tests/test_f004_dataset.py::TestComputeGoldAnswer -v`

---

### assign_splits

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_assign_train_from_spider_train | Spider train split maps to train | Questions with `spider_split="train"` | `split="train"` | happy |
| test_assign_eval_from_spider_validation | Spider validation split maps to eval | Questions with `spider_split="validation"` | `split="eval"` | happy |
| test_assign_preserves_all_questions | No questions are dropped | 10 input questions | 10 output questions | happy |
| test_assign_mixed_splits | Mix of train and validation | 7 train + 3 validation | 7 train + 3 eval | happy |
| test_assign_all_train | All questions from train split | 5 train questions | All `split="train"` | edge |
| test_assign_all_eval | All questions from validation split | 5 validation questions | All `split="eval"` | edge |

**Run:** `pytest tests/test_f004_dataset.py::TestAssignSplits -v`

---

### download_spider_databases

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_download_creates_files | Download for known db_ids produces SQLite files | `["concert_singer"]`, temp dir | Dict mapping db_id to valid Path, file exists | happy |
| test_download_skips_existing | Existing database is not re-downloaded | Pre-existing file, same db_id | File unchanged, no download attempt | happy |
| test_download_unknown_db | Unknown db_id | `["nonexistent_db_xyz"]` | Raises `FileNotFoundError` | error |
| test_download_empty_list | Empty db_ids list | `[]` | Returns empty dict | edge |
| test_download_returns_correct_paths | Paths follow `{output_dir}/{db_id}/{db_id}.sqlite` | `["concert_singer"]` | Path matches expected pattern | happy |

**Run:** `pytest tests/test_f004_dataset.py::TestDownloadSpiderDatabases -v`

---

### load_spider_questions

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_load_returns_questions | Valid db_ids produce question dicts | `["concert_singer"]` | Non-empty list of dicts with `query` and `db_id` fields | happy |
| test_load_multiple_dbs | Multiple db_ids returns questions from all | `["concert_singer", "pets_1"]` | Questions from both databases present | happy |
| test_load_includes_both_splits | Both train and validation splits loaded | `["concert_singer"]` | Questions with both spider splits present | happy |
| test_load_connection_failure | Network unavailable (mocked) | Any db_ids, no network | Raises `ConnectionError` | error |
| test_load_empty_list | Empty db_ids list | `[]` | Returns empty list | edge |

**Run:** `pytest tests/test_f004_dataset.py::TestLoadSpiderQuestions -v`

---

### validate_dataset

| Test | Description | Input | Expected | Category |
|------|-------------|-------|----------|----------|
| test_validate_clean_dataset | Valid dataset passes all checks | Well-formed dataset | Empty error list | happy |
| test_validate_missing_field | Record missing required field | Record without `gold_sql` | Error list includes missing field message | error |
| test_validate_duplicate_ids | Two records share same question_id | Duplicate `"concert_singer_train_001"` | Error list includes duplicate message | error |
| test_validate_gold_sql_fails | gold_sql that does not execute | Record with broken SQL | Error list includes SQL execution message | error |
| test_validate_gold_answer_mismatch | gold_answer does not match re-execution | Record with wrong gold_answer | Error list includes mismatch message | error |
| test_validate_difficulty_distribution | Distribution check ~40/40/20 | Dataset with extreme skew (100% easy) | Warning or error about distribution | edge |
| test_validate_clean_splits | No question appears in both splits | Dataset with clean splits | No split errors | happy |
| test_validate_cross_split_leak | Same question in train and eval | Duplicate across splits | Error detected | error |

**Run:** `pytest tests/test_f004_dataset.py::TestValidateDataset -v`

---

## 2. Integration Tests

### Flow: Primary Curation Pipeline

| Step | Action | Expected | Verification |
|------|--------|----------|--------------|
| 1 | Read db_list.json for target database IDs | Returns list of 10 db_ids | `len(db_ids) >= 10` |
| 2 | Download Spider SQLite databases | All db_ids have corresponding .sqlite files | All paths exist, files > 0 bytes |
| 3 | Load raw Spider questions from HuggingFace | Questions loaded for all target db_ids | `len(questions) > 0`, each has `db_id` and `query` |
| 4 | Compute gold_answer for each question | gold_answer populated, failed queries skipped | No None gold_answers in output; skip count logged |
| 5 | Classify answer_type for each question | answer_type is one of 5 valid values | All values in `{"integer","float","string","list","table"}` |
| 6 | Extract tables_involved from gold_sql | Each question has non-empty tables_involved | All lists non-empty, all entries are strings |
| 7 | Classify difficulty from tables_involved | difficulty is one of 3 valid values | All values in `{"easy","medium","hard"}` |
| 8 | Assign train/eval split | Each question has valid split | All values in `{"train","eval"}` |
| 9 | Generate question_id | All IDs unique and match format | Regex match, `len(set(ids)) == len(ids)` |
| 10 | Validate full dataset | validate_dataset returns empty error list | `len(errors) == 0` |
| 11 | Write output JSON files | questions_train.json and questions_eval.json exist | Files parseable as JSON, combined count >= 100 |

**Run:** `pytest tests/test_f004_integration.py::TestCurationPipeline -v`

---

### Flow: Gold SQL Execution Failure (Alternative)

| Step | Action | Expected | Verification |
|------|--------|----------|--------------|
| 1 | Provide question with broken gold_sql | gold_sql raises sqlite3.Error | Exception caught, not propagated |
| 2 | Check warning logged | Log contains db_id and error details | Log output includes warning |
| 3 | Question excluded from final dataset | Output does not contain the broken question | question_id absent from output |
| 4 | Remaining questions processed | Pipeline continues without interruption | Other questions have valid gold_answer |

**Run:** `pytest tests/test_f004_integration.py::TestGoldSqlFailure -v`

---

### Flow: Validate-Only Mode (Alternative)

| Step | Action | Expected | Verification |
|------|--------|----------|--------------|
| 1 | Invoke script with `--validate` flag | Does not download or regenerate data | No network calls made |
| 2 | Load existing questions_train.json and questions_eval.json | Files read successfully | No FileNotFoundError |
| 3 | Locate SQLite databases in data/databases/ | All referenced databases found | All db_paths valid |
| 4 | Run validate_dataset() | Returns list of errors (may be empty) | Return type is list[str] |
| 5 | Exit code reflects validation result | 0 if valid, 1 if invalid | Process exit code matches |

**Run:** `pytest tests/test_f004_integration.py::TestValidateOnlyMode -v`

---

## 3. API Tests

No API endpoints defined for F004. This feature is a standalone curation script.

---

## 4. E2E Tests

### Scenario: Full Dataset Generation

**Setup:** Clean environment with no existing output files. Network access available. `data/questions/db_list.json` contains 10 target database IDs.

**Actions:**
1. Run the curation script end-to-end (no flags)
2. Wait for completion

**Expected:**
- `data/questions/questions_train.json` exists and contains valid JSON array
- `data/questions/questions_eval.json` exists and contains valid JSON array
- Combined question count >= 100
- All questions pass `validate_dataset()` with zero errors
- At least 8 distinct `database_name` values represented
- Train/eval split approximately 70/30 (+/- 10%)
- All three difficulty levels present
- All five answer_type values present (or at least 3)

**Run:** `python scripts/curate_dataset.py && python scripts/curate_dataset.py --validate`

---

### Scenario: Validate-Only on Pre-Generated Data

**Setup:** Output JSON files already exist from a prior run. SQLite databases present in `data/databases/`.

**Actions:**
1. Run the curation script with `--validate` flag only

**Expected:**
- No new files created or modified
- Validation output printed to stdout
- Exit code 0 if data is valid

**Run:** `python scripts/curate_dataset.py --validate`

---

### Scenario: Idempotent Re-Run

**Setup:** Output JSON files already exist from a prior run.

**Actions:**
1. Run the curation script again (full mode)
2. Compare output files

**Expected:**
- Output files are regenerated
- Same question count (deterministic for same input)
- Database files not re-downloaded (skip existing)

**Run:** `python scripts/curate_dataset.py`

---

## 5. Edge Cases Checklist

- [ ] Null/None gold_answer values handled gracefully
- [ ] Empty string gold_sql skipped or rejected
- [ ] SQL with unicode characters in table/column names
- [ ] Very large query results (1000+ rows) handled by compute_gold_answer
- [ ] Database file that exists but is corrupt (0 bytes or invalid SQLite)
- [ ] db_list.json missing or empty
- [ ] db_list.json with duplicate db_ids
- [ ] Question with gold_sql containing multiple statements (semicolons)
- [ ] Question where gold_sql returns different results on re-execution (non-deterministic)
- [ ] Tables_involved extraction with SQL using CTEs (WITH clause)
- [ ] Tables_involved extraction with SQL using UNION across different tables
- [ ] Extremely long gold_sql (> 1000 chars)
- [ ] Database with no tables (empty schema)
- [ ] Retry behavior on transient network failure during HuggingFace download
- [ ] Retry behavior on transient network failure during database download
- [ ] Concurrent access to same SQLite file (if parallelized)
- [ ] Output JSON file encoding (UTF-8) with special characters in question_text

---

## 6. Evidence Requirements

| Category | Evidence Type | Example |
|----------|---------------|---------|
| Unit tests | pytest output | `X passed, Y skipped` |
| Integration | pytest output | `X passed` |
| E2E | Script output + file inspection | `Generated 105 questions`, `Validation passed` |
| Output files | JSON structure inspection | `jq length questions_train.json` returns count |
| Skip handling | Log output | Warning messages for skipped questions |
| Validation | Exit code | `echo $?` returns `0` |