Data Manipulation Language (DML) Tests

Relevant source files

• test/evidence/slt_lang_replace.test
• test/evidence/slt_lang_update.test

Purpose and Scope

This document describes the Data Manipulation Language (DML) test suite within the SQL Language Evidence Tests category. DML tests validate the behavior of SQL statements that modify data within existing database tables, specifically:

• UPDATE statements for modifying existing rows
• REPLACE and INSERT OR REPLACE statements for insert-or-update operations

These tests ensure compliance with SQL standards and SQLite-specific behaviors for data manipulation operations. For information about creating or dropping database objects, see Data Definition Language (DDL) Tests-tests). For query validation, see Query Operator Tests.

Sources: test/evidence/slt_lang_update.test:1-198 test/evidence/slt_lang_replace.test:1-76

Test File Organization

The DML test suite consists of two primary test files located in the test/evidence/ directory:

Both files employ the SQL Logic Test format with evidence markers that reference specific requirements from the SQLite documentation.

Sources: test/evidence/slt_lang_update.test1 test/evidence/slt_lang_replace.test1

UPDATE Statement Test Coverage

Core UPDATE Functionality

The slt_lang_update.test file validates fundamental UPDATE statement behavior through a series of progressive tests that build upon a simple two-column table schema:

Sources: test/evidence/slt_lang_update.test:3-16

graph TD
    Setup["Table Setup\nCREATE TABLE t1(x INTEGER, y VARCHAR(8))\nCREATE INDEX t1i1 ON t1(x)"]
Setup --> InitData["Initial Data\nINSERT INTO t1 VALUES(1,'true')\nINSERT INTO t1 VALUES(0,'false')\nINSERT INTO t1 VALUES(NULL,'NULL')"]
InitData --> BasicUpdate["Basic UPDATE Tests\nEvidence: R-38515-45264"]
InitData --> WhereClause["WHERE Clause Tests\nEvidence: R-58095-46013, R-58129-20729"]
InitData --> MultiAssign["Multiple Assignment Tests\nEvidence: R-34751-18293"]
InitData --> SelfRef["Self-Referencing Tests\nEvidence: R-36239-04077, R-04558-24451"]
BasicUpdate --> Validate["Query and Validate Results"]
WhereClause --> Validate
 
   MultiAssign --> Validate
 
   SelfRef --> Validate

WHERE Clause Behavior

The tests validate three key WHERE clause scenarios:

1. Unconditional UPDATE (no WHERE clause) - All rows affected
2. Conditional UPDATE - Only matching rows affected
3. Non-matching WHERE - Zero rows affected (not an error)

Sources: test/evidence/slt_lang_update.test:43-70

Multiple Column Assignment

Test case at test/evidence/slt_lang_update.test:83-108 validates the behavior when the same column appears multiple times in the assignment list. According to Evidence R-34751-18293, only the rightmost assignment is used:

Note: This test is skipped on MSSQL platforms using the skipif mssql directive, as MSSQL treats multiple assignments to the same column as an error.

Sources: test/evidence/slt_lang_update.test:83-108

Self-Referencing Expressions

Tests validate that scalar expressions in UPDATE statements can reference columns from the row being updated, and that all expressions are evaluated before any assignments are made:

Sources: test/evidence/slt_lang_update.test:127-138

sequenceDiagram
    participant SQL as "UPDATE t1 SET x=x+2"
    participant Eval as "Expression Evaluator"
    participant Store as "Row Storage"
    
    Note over SQL,Store: Evidence: R-36239-04077, R-04558-24451
    
    SQL->>Eval: For each row
    Eval->>Store: Read current x value
    Store-->>Eval: x = 2
    Eval->>Eval: Evaluate x+2 = 4
    Note over Eval: All expressions evaluated\nBEFORE assignments
    Eval->>Store: Write x = 4
    Store-->>SQL: Row updated

Unmodified Columns

Evidence R-40472-60438 validates that columns not mentioned in the assignment list remain unchanged. Test test/evidence/slt_lang_update.test:109-124 verifies this by updating column x while monitoring that column y retains its original value.

Sources: test/evidence/slt_lang_update.test:109-124

REPLACE Statement Test Coverage

REPLACE as INSERT OR REPLACE Alias

The slt_lang_replace.test file validates Evidence R-03421-22330, which states that "The REPLACE command is an alias for the 'INSERT OR REPLACE' variant of the INSERT command."

Sources: test/evidence/slt_lang_replace.test:21-56

graph LR
    subgraph "Test Table Schema"
        T1["t1(x INTEGER PRIMARY KEY, y VARCHAR(16))"]
end
    
    subgraph "Test Progression"
        Insert["INSERT INTO t1 VALUES(2, 'insert')"]
InsertOrReplace["INSERT OR REPLACE INTO t1 VALUES(2, 'insert or replace')"]
Replace["REPLACE INTO t1 VALUES(2, 'replace')"]
Insert -->|Verify: x=2, y='insert'| V1["Query: SELECT x,y FROM t1 WHERE x=2"]
InsertOrReplace -->|Verify: x=2, y='insert or replace'| V2["Query: SELECT x,y FROM t1 WHERE x=2"]
Replace -->|Verify: x=2, y='replace'| V3["Query: SELECT x,y FROM t1 WHERE x=2"]
end
    
 
   T1 --> Insert

Test Execution Pattern

The REPLACE tests follow a consistent pattern validating both update and insert semantics:

Sources: test/evidence/slt_lang_replace.test:24-76

Cross-Platform Compatibility

flowchart TD
    Start["Test File Execution Begins"]
Start --> CheckMSSQL{"Platform\nMSSQL?"}
CheckMSSQL -->|Yes| HaltMSSQL["halt\n(slt_lang_replace.test:1-3)"]
CheckMSSQL -->|No| CheckOracle{"Platform\nOracle?"}
CheckOracle -->|Yes| HaltOracle["halt\n(slt_lang_replace.test:5-7)"]
CheckOracle -->|No| RunTests["Execute Test Cases"]
RunTests --> MultiAssignCheck{"Test: Multiple\ncolumn assignment?"}
MultiAssignCheck -->|Yes| SkipMSSQL["skipif mssql\n(slt_lang_update.test:87)"]
MultiAssignCheck -->|No| Execute["Execute Test"]
SkipMSSQL -->|MSSQL| Skip["Skip Test"]
SkipMSSQL -->|Other DB| Execute
    
 
   RunTests --> ReplaceCheck{"Test: INSERT OR\nREPLACE syntax?"}
ReplaceCheck -->|Yes| SkipMySQL["skipif mysql\n(slt_lang_replace.test:37,57)"]
ReplaceCheck -->|No| Execute
    
 
   SkipMySQL -->|MySQL| Skip
 
   SkipMySQL -->|Other DB| Execute

Platform Exclusions

Both DML test files include conditional execution directives to handle database platform differences:

Sources: test/evidence/slt_lang_replace.test:1-7 test/evidence/slt_lang_update.test87 test/evidence/slt_lang_replace.test:37-57

Platform-Specific Behavior Matrix

Sources: test/evidence/slt_lang_update.test:87-108 test/evidence/slt_lang_replace.test:1-66

Evidence Markers and Validation

UPDATE Statement Evidence Coverage

The UPDATE tests validate the following evidence requirements from SQLite documentation:

Sources: test/evidence/slt_lang_update.test:19-138

REPLACE Statement Evidence Coverage

Sources: test/evidence/slt_lang_replace.test:21-76

erDiagram
    t1 {INTEGER x PK\nVARCHAR_8 y}
    
    t1_index["t1i1"] {INDEX_ON x}
    
    t1 ||--o{ t1_index : indexed by

Test Data Patterns

UPDATE Test Dataset

The UPDATE tests operate on a minimal dataset designed to test NULL handling, boolean-like values, and indexed columns:

Initial data:

• Row 1: x=1, y='true'
• Row 2: x=0, y='false'
• Row 3: x=NULL, y='NULL'

Sources: test/evidence/slt_lang_update.test:3-16

erDiagram
    t1 {INTEGER x PK "PRIMARY KEY"\nVARCHAR_16 y}

REPLACE Test Dataset

The REPLACE tests use a simpler schema with a primary key constraint to test upsert behavior:

Initial data:

• Row 1: x=1, y='true'
• Row 2: x=0, y='false'

Sources: test/evidence/slt_lang_replace.test:11-18

Hash Threshold Configuration

Both DML test files specify hash-threshold 8 at the beginning:

• test/evidence/slt_lang_update.test1
• test/evidence/slt_lang_replace.test9

This directive controls when the test harness switches from storing individual result rows to using hash-based comparison for large result sets. A threshold of 8 means result sets with more than 8 rows will be validated using hash comparison instead of row-by-row comparison.

Sources: test/evidence/slt_lang_update.test1 test/evidence/slt_lang_replace.test9

Unimplemented Evidence (TBD)

The UPDATE test file contains several evidence markers prefixed with TBD-EVIDENCE-OF, indicating requirements that are documented but not yet tested:

• Conflict clause handling (R-12619-24112)
• Trigger-specific UPDATE restrictions (R-12123-54095, R-09690-36749, R-06085-13761, R-29512-54644)
• INDEXED BY/NOT INDEXED in triggers (R-19619-42762)
• LIMIT and ORDER BY clauses (R-57359-59558, R-59581-44104, R-58862-44169, R-63582-45120, R-18628-11938, R-30955-38324, R-19486-35828, R-10927-26133)

These represent advanced UPDATE features that require additional test infrastructure or are only available with specific compile-time options like SQLITE_ENABLE_UPDATE_DELETE_LIMIT.

Sources: test/evidence/slt_lang_update.test:139-198