CREATE Statement Tests

Relevant source files

• test/evidence/slt_lang_createtrigger.test
• test/evidence/slt_lang_createview.test

Purpose and Scope

This page documents the CREATE statement tests in the SQL Logic Test corpus, specifically covering CREATE TRIGGER and CREATE VIEW statements. These tests validate the behavior of database object creation commands according to SQL specifications and SQLite-specific extensions.

For tests covering DROP statements, see DROP Statement Tests. For broader DDL test coverage, see Data Definition Language (DDL) Tests-tests).

Test File Overview

The CREATE statement tests are organized into two primary test files located in the test/evidence/ directory:

Both test files follow the SQL Logic Test format and include evidence references to SQLite documentation sections, cross-database compatibility directives, and comprehensive behavioral validation.

Sources: test/evidence/slt_lang_createtrigger.test:1-220 test/evidence/slt_lang_createview.test:1-129

Test Execution Model

Diagram: Test Execution Flow for CREATE Statement Tests

This state machine illustrates the standard test pattern used across both slt_lang_createtrigger.test and slt_lang_createview.test. Each test file establishes a base table (t1), populates test data, and then executes CREATE statements with validation of both success and error conditions.

Sources: test/evidence/slt_lang_createtrigger.test:3-16 test/evidence/slt_lang_createview.test:3-16 test/evidence/slt_lang_createtrigger.test:18-19

CREATE TRIGGER Tests

Trigger Timing and Event Matrix

The slt_lang_createtrigger.test file validates all combinations of trigger timing (BEFORE/AFTER) and trigger events (INSERT/UPDATE/DELETE):

Diagram: Trigger Test Coverage Matrix

graph LR
    subgraph "Trigger Events"
        INSERT["INSERT"]
UPDATE["UPDATE"]
DELETE["DELETE"]
end
    
    subgraph "Trigger Timing"
        BEFORE["BEFORE"]
AFTER["AFTER"]
NONE["(no timing)"]
end
    
    subgraph "Test Coverage"
        t1r1["t1r1: UPDATE (no timing)"]
t1r2["t1r2: DELETE (no timing)"]
t1r3["t1r3: INSERT (no timing)"]
t1r4["t1r4: UPDATE (no timing)"]
t1r5["t1r5: AFTER DELETE"]
t1r6["t1r6: AFTER INSERT"]
t1r7["t1r7: AFTER UPDATE"]
t1r8["t1r8: BEFORE DELETE"]
t1r9["t1r9: BEFORE INSERT"]
t1r10["t1r10: BEFORE UPDATE"]
end
    
 
   UPDATE --> t1r1
 
   DELETE --> t1r2
 
   INSERT --> t1r3
 
   UPDATE --> t1r4
    
 
   AFTER --> t1r5
 
   AFTER --> t1r6
 
   AFTER --> t1r7
 
   BEFORE --> t1r8
 
   BEFORE --> t1r9
 
   BEFORE --> t1r10
    
 
   DELETE --> t1r5
 
   INSERT --> t1r6
 
   UPDATE --> t1r7
 
   DELETE --> t1r8
 
   INSERT --> t1r9
 
   UPDATE --> t1r10

This diagram maps the 10 trigger test cases (t1r1 through t1r10) to their respective timing and event combinations, demonstrating comprehensive coverage of the CREATE TRIGGER syntax.

Sources: test/evidence/slt_lang_createtrigger.test:25-96

Basic Trigger Creation Syntax

The test validates basic CREATE TRIGGER syntax with the following pattern:

CREATE TRIGGER trigger_name [BEFORE|AFTER] event ON table_name 
BEGIN 
  sql_statements; 
END;

Key test cases include:

Sources: test/evidence/slt_lang_createtrigger.test:25-96

Duplicate Trigger Validation

The test file validates that creating a trigger with an existing name produces an error:

statement ok
CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;

# already exists
statement error
CREATE TRIGGER t1r1 UPDATE ON t1 BEGIN SELECT 1; END;

This ensures enforcement of unique trigger names within the database schema.

Sources: test/evidence/slt_lang_createtrigger.test:24-29

OLD and NEW Reference Validation

Diagram: OLD and NEW Reference Validity Matrix

The test validates evidence reference R-63660-13730 which specifies: "OLD and NEW references may only be used in triggers on events for which they are relevant, as follows: INSERT NEW references are valid UPDATE NEW and OLD references are valid DELETE OLD references are valid"

Sources: test/evidence/slt_lang_createtrigger.test:65-68

Evidence References

The slt_lang_createtrigger.test file contains extensive evidence references linking test cases to specific sections of SQLite documentation:

The evidence references follow the format R-XXXXX-XXXXX and correspond to specific requirements in the SQLite documentation.

Sources: test/evidence/slt_lang_createtrigger.test:21-190

Platform Compatibility Directive

The test includes an early halt for Microsoft SQL Server:

onlyif mssql
halt

This directive causes the test to stop execution on MSSQL platforms at line 19, indicating that MSSQL has significant differences in trigger implementation that make the remaining tests incompatible.

Sources: test/evidence/slt_lang_createtrigger.test:18-19

Trigger Cleanup

All created triggers are explicitly dropped at the end of the test:

statement ok
DROP TRIGGER t1r1
...
statement ok
DROP TRIGGER t1r10

This cleanup ensures test isolation and verifies that the DROP TRIGGER statement functions correctly.

Sources: test/evidence/slt_lang_createtrigger.test:192-219

CREATE VIEW Tests

Basic View Creation

Diagram: View Creation and Query Equivalence

The test validates that a view acts as a "pre-packaged SELECT statement" by demonstrating query equivalence between direct table queries and view queries. Both SELECT x FROM t1 WHERE x>0 and SELECT x FROM view1 produce identical results.

Sources: test/evidence/slt_lang_createview.test:22-37

View Creation Test Cases

Sources: test/evidence/slt_lang_createview.test:22-53

Temporary View Creation (SQLite-Specific)

The test validates SQLite-specific TEMP and TEMPORARY view syntax:

onlyif sqlite
statement ok
CREATE TEMP VIEW view2 AS SELECT x FROM t1 WHERE x>0

onlyif sqlite
statement ok
CREATE TEMPORARY VIEW view3 AS SELECT x FROM t1 WHERE x>0

These tests are guarded by the onlyif sqlite directive, as temporary views are a SQLite-specific feature. Evidence reference R-48816-31606 specifies that temporary views are "only visible to the process that opened the database and is automatically deleted when the database is closed."

Sources: test/evidence/slt_lang_createview.test:42-53

Read-Only View Constraints

Diagram: Platform-Specific View Modification Behavior

The test validates evidence reference R-16775-34716: "You cannot DELETE, INSERT, or UPDATE a view" and R-05363-17893: "Views are read-only in SQLite."

However, the test demonstrates platform-specific behavior using conditional directives:

SQLite behavior (read-only):

onlyif sqlite
statement error
DELETE FROM view1 WHERE x>0

onlyif sqlite
statement error
UPDATE view1 SET x=2

MSSQL behavior (allows UPDATE/DELETE):

onlyif mssql  # this is allowed
statement ok
DELETE FROM view1 WHERE x>0

onlyif mssql  # this is allowed
statement ok
UPDATE view1 SET x=2

Universal behavior (INSERT always fails):

statement error
INSERT INTO view1 VALUES(2,'unknown')

Sources: test/evidence/slt_lang_createview.test:65-103

View Modification Operations Test Matrix

Sources: test/evidence/slt_lang_createview.test:65-103

View Cleanup and Error Validation

Diagram: View Cleanup and Error Condition Validation

The test validates proper error handling for DROP VIEW operations:

1. Successfully dropping existing views
2. Error when dropping already-dropped views
3. Error when dropping non-existent views

Evidence reference R-10484-47921 states: "Views are removed with the DROP VIEW command."

Sources: test/evidence/slt_lang_createview.test:111-128

Cross-Database Compatibility

Platform-Specific Directives Summary

These directives enable the same test file to validate both standard SQL behavior and platform-specific deviations. For comprehensive information on conditional execution, see Conditional Execution Directives.

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_createview.test:47-103

Known Platform Differences

Diagram: Platform-Specific CREATE Statement Behavior

For detailed platform behavior differences, see Platform-Specific Behaviors.

Sources: test/evidence/slt_lang_createtrigger.test:18-19 test/evidence/slt_lang_createview.test:68-103

Test Setup Pattern

Both test files follow a consistent setup pattern:

1. Hash threshold configuration : hash-threshold 8 enables result hashing for large result sets
2. Base table creation : CREATE TABLE t1( x INTEGER, y VARCHAR(8) )
3. Test data insertion : Three rows representing true, false, and NULL values
4. Index creation : CREATE INDEX t1i1 ON t1(x) for query optimization testing

This standardized setup provides a consistent foundation for testing CREATE statements across different scenarios.

Sources: test/evidence/slt_lang_createtrigger.test:1-16 test/evidence/slt_lang_createview.test:1-16