DROP Statement Tests

Relevant source files

• test/evidence/slt_lang_dropindex.test
• test/evidence/slt_lang_droptable.test
• test/evidence/slt_lang_droptrigger.test
• test/evidence/slt_lang_dropview.test

Purpose and Scope

This document details the DROP statement tests within the SQL Logic Test corpus, which validate the behavior of SQL DROP commands for removing database objects. These tests are located in the test/evidence/ directory and cover four primary DROP statement types: DROP INDEX, DROP TABLE, DROP TRIGGER, and DROP VIEW.

The tests verify SQL specification compliance through evidence markers that reference specific requirements from SQL language documentation. Each test file follows a consistent pattern of creating objects, dropping them successfully, and validating error conditions when attempting to drop non-existent or already-dropped objects.

For information about CREATE statement tests, see CREATE Statement Tests. For broader DDL test coverage, see Data Definition Language (DDL) Tests-tests).

Test File Organization

The DROP statement tests are organized into four evidence test files, each targeting a specific database object type:

Sources: test/evidence/slt_lang_dropindex.test:1-35 test/evidence/slt_lang_droptable.test:1-56 test/evidence/slt_lang_droptrigger.test:1-53 test/evidence/slt_lang_dropview.test:1-60

graph TB
    DROP_ROOT["DROP Statement Tests\ntest/evidence/"]
DROP_INDEX["slt_lang_dropindex.test\nDROP INDEX validation"]
DROP_TABLE["slt_lang_droptable.test\nDROP TABLE validation"]
DROP_TRIGGER["slt_lang_droptrigger.test\nDROP TRIGGER validation"]
DROP_VIEW["slt_lang_dropview.test\nDROP VIEW validation"]
DROP_ROOT --> DROP_INDEX
 
   DROP_ROOT --> DROP_TABLE
 
   DROP_ROOT --> DROP_TRIGGER
 
   DROP_ROOT --> DROP_VIEW
    
 
   DROP_INDEX --> IDX_EV["Evidence: R-42037-15614\nIndex removal behavior"]
DROP_TABLE --> TBL_EV1["Evidence: R-01463-03846\nTable removal behavior"]
DROP_TABLE --> TBL_EV2["Evidence: R-33950-57093\nCascading deletion"]
DROP_TABLE --> TBL_EV3["Evidence: R-57089-01510\nIF EXISTS clause"]
DROP_TRIGGER --> TRG_EV1["Evidence: R-61172-15671\nTrigger removal behavior"]
DROP_TRIGGER --> TRG_EV2["Evidence: R-37808-62273\nAuto-drop with table"]
DROP_VIEW --> VW_EV1["Evidence: R-27002-52307\nView removal behavior"]
DROP_VIEW --> VW_EV2["Evidence: R-00359-41639\nData preservation"]

Evidence Markers

Each test file contains evidence markers that reference specific requirements from SQL language specifications:

Sources: test/evidence/slt_lang_dropindex.test:19-20 test/evidence/slt_lang_droptable.test:19-20 test/evidence/slt_lang_droptable.test:36-37 test/evidence/slt_lang_droptable.test:43-44 test/evidence/slt_lang_droptrigger.test:21-22 test/evidence/slt_lang_droptrigger.test:41-42 test/evidence/slt_lang_dropview.test:19-20 test/evidence/slt_lang_dropview.test:44-46

Common Test Pattern

All DROP statement tests follow a consistent execution pattern:

Sources: test/evidence/slt_lang_dropindex.test:3-34 test/evidence/slt_lang_droptable.test:3-31 test/evidence/slt_lang_droptrigger.test:3-36 test/evidence/slt_lang_dropview.test:3-39

Standard Test Setup

Each DROP statement test file begins with identical setup code:

hash-threshold 8

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

statement ok
INSERT INTO t1 VALUES(1,'true')

statement ok
INSERT INTO t1 VALUES(0,'false')

statement ok
INSERT INTO t1 VALUES(NULL,'NULL')

statement ok
CREATE INDEX t1i1 ON t1(x)

This creates a baseline table t1 with three rows and an index t1i1 on column x.

Sources: test/evidence/slt_lang_dropindex.test:1-16 test/evidence/slt_lang_droptable.test:1-16 test/evidence/slt_lang_droptrigger.test:1-16 test/evidence/slt_lang_dropview.test:1-16

DROP INDEX Tests

The DROP INDEX tests validate index removal behavior and are located in test/evidence/slt_lang_dropindex.test

Basic DROP INDEX Behavior

Sources: test/evidence/slt_lang_dropindex.test:19-34

Cross-Database Compatibility

DROP INDEX syntax differs significantly across database platforms. The tests use skipif mssql directives because Microsoft SQL Server requires different syntax:

skipif mssql
statement ok
DROP INDEX t1i1;

In MSSQL, the syntax would be DROP INDEX t1.t1i1 (table.index format), which is incompatible with the standard SQLite syntax tested here.

Sources: test/evidence/slt_lang_dropindex.test:22-34

DROP TABLE Tests

The DROP TABLE tests validate table removal and cascading deletion behavior, located in test/evidence/slt_lang_droptable.test

graph TB
    DROP_TABLE["DROP TABLE t1"]
subgraph "Affected Objects"
        TABLE["Table t1\nRemoved from schema"]
INDICES["All indices on t1\n(e.g., t1i1)"]
TRIGGERS["All triggers on t1\n(e.g., t1r1)"]
DATA["Table data\nRemoved from disk"]
end
    
 
   DROP_TABLE --> TABLE
 
   DROP_TABLE --> INDICES
 
   DROP_TABLE --> TRIGGERS
 
   DROP_TABLE --> DATA
    
 
   INDICES -.-> IDX_ERROR["Subsequent DROP INDEX t1i1\nproduces error"]
TRIGGERS -.-> TRG_ERROR["Subsequent DROP TRIGGER t1r1\nproduces error"]

Cascading Deletion

A critical aspect of DROP TABLE is the automatic deletion of associated database objects:

Sources: test/evidence/slt_lang_droptable.test:36-41

The test validates cascading deletion by attempting to drop the index after the table:

statement ok
DROP TABLE t1

# this should error, as was dropped with table
statement error
DROP INDEX t1i1;

This verifies evidence marker R-33950-57093: "All indices and triggers associated with the table are also deleted."

Sources: test/evidence/slt_lang_droptable.test:36-41

IF EXISTS Clause

The DROP TABLE tests include validation of the IF EXISTS clause:

statement ok
CREATE TABLE t1( x INTEGER, y VARCHAR(8) )

skipif mssql
statement ok
DROP TABLE IF EXISTS t1

skipif mssql
statement ok
DROP TABLE IF EXISTS t1

The second DROP TABLE IF EXISTS t1 succeeds without error because the IF EXISTS clause suppresses the error that would normally occur when dropping a non-existent table.

Sources: test/evidence/slt_lang_droptable.test:43-55

Error Conditions

The tests validate three error scenarios:

1. Already dropped table : Attempting to drop a table that was previously dropped in the same test session
2. Non-existent table : Attempting to drop a table name that was never created
3. Cascaded object : Attempting to drop an index that was automatically removed when its parent table was dropped

Sources: test/evidence/slt_lang_droptable.test:25-31 test/evidence/slt_lang_droptable.test:39-41

DROP TRIGGER Tests

The DROP TRIGGER tests validate trigger removal and automatic deletion behavior, located in test/evidence/slt_lang_droptrigger.test

Platform Compatibility

DROP TRIGGER tests are not compatible with Microsoft SQL Server:

onlyif mssql
halt

This halts execution on MSSQL platforms because the trigger syntax is significantly different from SQLite.

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

Trigger Creation and Deletion

Sources: test/evidence/slt_lang_droptrigger.test:24-36 test/evidence/slt_lang_droptrigger.test:44-52

Automatic Trigger Deletion

The tests validate that triggers are automatically deleted when their associated table is dropped:

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

statement ok
DROP TABLE t1

# already deleted when table dropped
statement error
DROP TRIGGER t1r1

This verifies evidence marker R-37808-62273: "triggers are automatically dropped when the associated table is dropped."

Sources: test/evidence/slt_lang_droptrigger.test:41-52

DROP VIEW Tests

The DROP VIEW tests validate view removal while ensuring underlying data integrity, located in test/evidence/slt_lang_dropview.test

View Definition Removal

Sources: test/evidence/slt_lang_dropview.test:27-31

Data Preservation Validation

The tests explicitly verify that dropping a view does not affect data in the underlying base tables:

statement ok
CREATE VIEW view2 AS SELECT x FROM t1 WHERE x=0

query I rowsort label-0
SELECT x FROM view2
----
0

statement ok
DROP VIEW view2

query I rowsort label-0
SELECT x FROM t1 WHERE x=0
----
0

This sequence:

1. Creates view view2 that filters t1 for rows where x=0
2. Queries through the view, confirming result is 0
3. Drops the view
4. Directly queries the base table with the same filter, confirming data is still 0

This verifies evidence marker R-00359-41639: "no actual data in the underlying base tables is modified."

Sources: test/evidence/slt_lang_dropview.test:44-59

Object Resolution

The tests validate standard object resolution for view names:

statement ok
CREATE VIEW view1 AS SELECT x FROM t1 WHERE x>0

statement ok
DROP VIEW view1

This verifies evidence marker R-18673-21346 regarding view-name and optional schema-name resolution using standard object resolution procedures.

Sources: test/evidence/slt_lang_dropview.test:22-31

Error Testing Matrix

All DROP statement tests validate a consistent set of error conditions:

Sources: test/evidence/slt_lang_dropindex.test:26-34 test/evidence/slt_lang_droptable.test:25-41 test/evidence/slt_lang_droptrigger.test:30-36 test/evidence/slt_lang_dropview.test:33-39

flowchart TD
 
   START["Execute DROP test"] --> CHECK_PLATFORM{"Platform?"}
CHECK_PLATFORM -->|SQLite| RUN_ALL["Run all tests\nStandard DROP syntax"]
CHECK_PLATFORM -->|MSSQL| SKIP_SOME["skipif mssql directives\nSkip incompatible tests"]
SKIP_SOME --> DROP_INDEX_SKIP["DROP INDEX skipped\nDifferent syntax required"]
SKIP_SOME --> IF_EXISTS_SKIP["IF EXISTS clause skipped\nNot supported in MSSQL"]
SKIP_SOME --> DROP_TRIGGER_HALT["DROP TRIGGER halted\nIncompatible syntax"]
RUN_ALL --> COMPLETE["All tests executed"]
DROP_INDEX_SKIP --> COMPLETE
 
   IF_EXISTS_SKIP --> COMPLETE
 
   DROP_TRIGGER_HALT --> HALT["Test execution stopped"]

Cross-Database Compatibility Summary

The DROP statement tests use conditional directives to handle platform-specific differences:

Sources: test/evidence/slt_lang_dropindex.test:22-34 test/evidence/slt_lang_droptable.test:49-55 test/evidence/slt_lang_droptrigger.test:18-19

Platform-Specific Behaviors

Sources: test/evidence/slt_lang_dropindex.test22 test/evidence/slt_lang_droptable.test49 test/evidence/slt_lang_droptrigger.test:18-19

graph TB
    subgraph "Test Coverage"
        BASIC["Basic DROP operations\nSuccess cases"]
ERRORS["Error conditions\nDouble-drop, non-existent"]
CASCADE["Cascading deletion\nIndices, triggers"]
IF_EXISTS["IF EXISTS clause\nError suppression"]
DATA_INTEGRITY["Data integrity\nView removal validation"]
end
    
    subgraph "Test Files"
        DROP_INDEX_FILE["slt_lang_dropindex.test"]
DROP_TABLE_FILE["slt_lang_droptable.test"]
DROP_TRIGGER_FILE["slt_lang_droptrigger.test"]
DROP_VIEW_FILE["slt_lang_dropview.test"]
end
    
 
   DROP_INDEX_FILE --> BASIC
 
   DROP_INDEX_FILE --> ERRORS
    
 
   DROP_TABLE_FILE --> BASIC
 
   DROP_TABLE_FILE --> ERRORS
 
   DROP_TABLE_FILE --> CASCADE
 
   DROP_TABLE_FILE --> IF_EXISTS
    
 
   DROP_TRIGGER_FILE --> BASIC
 
   DROP_TRIGGER_FILE --> ERRORS
 
   DROP_TRIGGER_FILE --> CASCADE
    
 
   DROP_VIEW_FILE --> BASIC
 
   DROP_VIEW_FILE --> ERRORS
 
   DROP_VIEW_FILE --> DATA_INTEGRITY

Test Execution Summary

The DROP statement tests validate the complete lifecycle of database object removal:

Sources: test/evidence/slt_lang_dropindex.test:1-35 test/evidence/slt_lang_droptable.test:1-56 test/evidence/slt_lang_droptrigger.test:1-53 test/evidence/slt_lang_dropview.test:1-60