Cross-Database Compatibility

Relevant source files

• test/evidence/in1.test
• test/evidence/in2.test
• test/evidence/slt_lang_dropindex.test
• test/evidence/slt_lang_reindex.test
• test/evidence/slt_lang_replace.test

Purpose and Scope

This document explains how the sqllogictest corpus supports multiple database platforms through conditional test execution. The test files use directives (skipif, onlyif, halt) to control whether specific tests run on different SQL engines, allowing a single test corpus to validate behavior across SQLite, MySQL, MSSQL, Oracle, and PostgreSQL while accommodating platform-specific SQL implementation differences.

For information about the test file format and directive syntax, see Test File Format Specification. For details on how to integrate the corpus with CI/CD systems for multi-database testing, see Integrating with CI/CD Systems.

Cross-Database Testing Model

The corpus employs a conditional execution strategy where test directives control execution flow based on the target database platform. This allows the same test file to validate SQL behavior across different engines while handling platform-specific limitations and non-standard features.

Supported Database Platforms

The following table summarizes the database platforms supported by the test corpus and their key characteristics:

Sources: test/evidence/in1.test:1-10 test/evidence/slt_lang_reindex.test:18-32 test/evidence/slt_lang_replace.test:1-7

Conditional Execution Architecture

Execution Control Flow

Diagram: Conditional Execution State Machine

The test runner processes directives line-by-line to determine whether subsequent SQL statements should execute. The onlyif directive allows execution only on matching platforms, while skipif prevents execution on matching platforms. The halt directive immediately terminates file processing for the current platform.

Sources: test/evidence/in1.test:4-9 test/evidence/slt_lang_reindex.test:22-32

Platform-Specific Test Isolation Patterns

Pattern 1: Complete File Exclusion

Tests that rely on platform-specific features can exclude entire files using the halt directive immediately after platform detection:

# Skip this entire file if MS SQL Server
onlyif mssql
halt

# Skip this entire file if Oracle
onlyif oracle
halt

This pattern appears in tests for SQLite-specific features like the REINDEX command and REPLACE syntax.

Sources: test/evidence/slt_lang_reindex.test:22-28 test/evidence/slt_lang_replace.test:1-7 test/evidence/in1.test:3-9

Pattern 2: Selective Statement Skipping

For tests that are mostly portable but have specific incompatibilities, directives precede individual statements:

# Only run on SQLite (empty RHS not standard SQL)
onlyif sqlite
query I nosort
SELECT 1 IN ()
----
0

This allows a single test file to run on multiple platforms while skipping non-portable statements.

Sources: test/evidence/in1.test:22-26 test/evidence/in2.test:78-84

Pattern 3: Platform-Specific Alternatives

Some tests provide alternative implementations for different platforms:

skipif mysql
statement ok
CREATE TABLE t7(a TEXT UNIQUE)

onlyif mysql
statement ok
CREATE TABLE t7(a TEXT, UNIQUE (a(1)))

MySQL requires a key length specification for TEXT columns in unique indexes, while other databases do not.

Sources: test/evidence/in1.test:398-405 test/evidence/in1.test:421-427

Platform-Specific Behaviors

SQLite-Specific Extensions

SQLite implements several non-standard SQL features that are explicitly excluded from other platforms:

Empty Right-Hand Side in IN Operator

SQLite allows empty parentheses in IN/NOT IN expressions, which is non-standard:

These tests are marked onlyif sqlite # empty RHS and skipped on MySQL, MSSQL, and Oracle.

Sources: test/evidence/in1.test:16-20 test/evidence/in1.test:22-64 test/evidence/in2.test:7-11 test/evidence/in2.test:78-144

Table-as-Operand Syntax

SQLite allows using a table name directly as the right operand of IN:

This SQLite-specific shorthand is marked onlyif sqlite throughout the test corpus.

Sources: test/evidence/in1.test:69-74 test/evidence/in1.test:80-90

REINDEX Command

SQLite and PostgreSQL support the REINDEX command for rebuilding indexes, while MySQL uses REPAIR TABLE and MSSQL/Oracle have no direct equivalent:

# skip this entire file if ms sql server
onlyif mssql
halt

# skip this entire file if oracle
onlyif oracle
halt

# skip this entire file if mysql
onlyif mysql
halt

Sources: test/evidence/slt_lang_reindex.test:18-32

REPLACE Statement

SQLite's REPLACE INTO syntax (alias for INSERT OR REPLACE) is not supported by MSSQL and Oracle:

# skip this file if ms sql server
onlyif mssql
halt

# skip this file if oracle
onlyif oracle
halt

Sources: test/evidence/slt_lang_replace.test:1-7

MySQL-Specific Limitations and Workarounds

NULL Handling in Subqueries

MySQL has known issues with NULL handling in certain IN subquery contexts:

# mysql is failing this one
skipif mysql
query I nosort label-55
SELECT null IN (SELECT * FROM t4)
----
NULL

These incompatibilities are documented inline and the tests are skipped for MySQL.

Sources: test/evidence/in1.test:951-956 test/evidence/in1.test:970-975

UNIQUE Index Key Length Requirement

MySQL requires explicit key length specifications for TEXT columns in UNIQUE indexes:

skipif mysql
statement ok
CREATE TABLE t7(a TEXT UNIQUE)

onlyif mysql
statement ok
CREATE TABLE t7(a TEXT, UNIQUE (a(1)))

Sources: test/evidence/in1.test:398-405

INSERT OR REPLACE Syntax

MySQL does not support SQLite's INSERT OR REPLACE syntax:

skipif mysql
statement ok
INSERT OR REPLACE INTO t1 VALUES(2, 'insert or replace')

Sources: test/evidence/slt_lang_replace.test:37-46

MSSQL-Specific Differences

DROP INDEX Syntax

MSSQL uses a different syntax for DROP INDEX that requires the table name:

skipif mssql
statement ok
DROP INDEX t1i1;

The entire DROP INDEX test file skips MSSQL-specific statements.

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

Empty RHS in IN Operator

MSSQL does not support empty parentheses in IN expressions:

skipif mysql # empty RHS
skipif mssql # empty RHS
skipif oracle # empty RHS
query I nosort
SELECT 1 FROM t1 WHERE 1 IN ()

Sources: test/evidence/in2.test:78-84

Oracle-Specific Considerations

VARCHAR vs TEXT Data Type

Oracle uses VARCHAR instead of TEXT for character data:

skipif oracle
statement ok
CREATE TABLE t1( x INTEGER, y TEXT )

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

Sources: test/evidence/in2.test:23-29

REINDEX and REPLACE Not Supported

Oracle lacks support for both REINDEX and REPLACE commands, resulting in complete file exclusion for these test suites.

Sources: test/evidence/slt_lang_reindex.test:26-28 test/evidence/slt_lang_replace.test:5-7

graph TB
    subgraph Tests["Test Categories"]
CoreSQL["Core SQL\n(SELECT, INSERT, UPDATE)"]
DDL["DDL Commands\n(CREATE, DROP)"]
INNULL["IN/NOT IN with NULL"]
EmptyRHS["Empty RHS in IN"]
TableAsOp["Table-as-operand"]
REINDEX_T["REINDEX Command"]
REPLACE_T["REPLACE Statement"]
end
    
    subgraph Platforms["Database Platforms"]
SQLite["SQLite"]
MySQL["MySQL"]
PostgreSQL["PostgreSQL"]
MSSQL["MSSQL"]
Oracle["Oracle"]
end
    
 
   CoreSQL -->|Full support| SQLite
 
   CoreSQL -->|Full support| MySQL
 
   CoreSQL -->|Full support| PostgreSQL
 
   CoreSQL -->|Full support| MSSQL
 
   CoreSQL -->|Full support| Oracle
    
 
   DDL -->|Full support| SQLite
 
   DDL -->|Full support| MySQL
 
   DDL -->|Full support| PostgreSQL
 
   DDL -->|Syntax differences| MSSQL
 
   DDL -->|Type differences| Oracle
    
 
   INNULL -->|Full support| SQLite
 
   INNULL -->|Partial subquery issues| MySQL
 
   INNULL -->|Full support| PostgreSQL
 
   INNULL -->|Full support| MSSQL
 
   INNULL -->|Full support| Oracle
    
 
   EmptyRHS -->|Supported non-standard| SQLite
 
   EmptyRHS -->|Not supported| MySQL
 
   EmptyRHS -->|Not supported| PostgreSQL
 
   EmptyRHS -->|Not supported| MSSQL
 
   EmptyRHS -->|Not supported| Oracle
    
 
   TableAsOp -->|Supported SQLite-specific| SQLite
 
   TableAsOp -->|Not supported| MySQL
 
   TableAsOp -->|Not supported| PostgreSQL
 
   TableAsOp -->|Not supported| MSSQL
 
   TableAsOp -->|Not supported| Oracle
    
 
   REINDEX_T -->|Full support| SQLite
 
   REINDEX_T -->|REPAIR TABLE instead| MySQL
 
   REINDEX_T -->|Full support| PostgreSQL
 
   REINDEX_T -->|Not supported| MSSQL
 
   REINDEX_T -->|Not supported| Oracle
    
 
   REPLACE_T -->|Full support| SQLite
 
   REPLACE_T -->|Limited support| MySQL
 
   REPLACE_T -->|Not supported| PostgreSQL
 
   REPLACE_T -->|Not supported| MSSQL
 
   REPLACE_T -->|Not supported| Oracle
    
    style SQLite fill:#f0f0f0
    style MySQL fill:#f0f0f0
    style PostgreSQL fill:#f0f0f0
    style MSSQL fill:#f0f0f0
    style Oracle fill:#f0f0f0

Multi-Platform Testing Strategy Matrix

The following diagram illustrates how different test categories map to platform support:

Diagram: Platform Support Matrix for Test Categories

Sources: test/evidence/in1.test:1-1156 test/evidence/slt_lang_reindex.test:18-32 test/evidence/slt_lang_replace.test:1-76

Directive Usage Patterns in Test Files

Common Directive Combinations

The test corpus uses specific directive patterns to handle different compatibility scenarios:

Sources: test/evidence/in1.test:1-1156 test/evidence/slt_lang_reindex.test:1-59 test/evidence/slt_lang_replace.test:1-76

Inline Documentation Comments

Test files include inline comments explaining platform-specific behaviors:

# EVIDENCE-OF: R-64309-54027 Note that SQLite allows the parenthesized
# list of scalar values on the right-hand side of an IN or NOT IN
# operator to be an empty list but most other SQL database engines and
# the SQL92 standard require the list to contain at least one element.

onlyif sqlite # empty RHS
query I nosort
SELECT 1 IN ()
----
0

The # empty RHS suffix provides context for why the directive is needed.

Sources: test/evidence/in1.test:16-26 test/evidence/in2.test:7-11

graph LR
    subgraph TestFiles["Test Files"]
in1["in1.test\n(IN operator edge cases)"]
in2["in2.test\n(IN operator validation)"]
dropindex["slt_lang_dropindex.test\n(DROP INDEX)"]
reindex["slt_lang_reindex.test\n(REINDEX)"]
replace["slt_lang_replace.test\n(REPLACE)"]
end
    
    subgraph SQLiteExec["SQLite: Full Execution"]
S1["All tests run"]
S2["Including non-standard features"]
end
    
    subgraph MySQLExec["MySQL: Partial Execution"]
M1["Skip empty RHS tests"]
M2["Skip table-as-operand tests"]
M3["Skip REINDEX tests"]
M4["Skip INSERT OR REPLACE"]
M5["NULL subquery issues"]
end
    
    subgraph MSSQLExec["MSSQL: Partial Execution"]
MS1["File halted: in1.test"]
MS2["File halted: reindex.test"]
MS3["File halted: replace.test"]
MS4["Modified DROP INDEX syntax"]
end
    
    subgraph OracleExec["Oracle: Partial Execution"]
O1["File halted: in1.test"]
O2["File halted: reindex.test"]
O3["File halted: replace.test"]
O4["VARCHAR vs TEXT substitution"]
end
    
 
   in1 -->|Full| SQLiteExec
 
   in2 -->|Full| SQLiteExec
 
   dropindex -->|Full| SQLiteExec
 
   reindex -->|Full| SQLiteExec
 
   replace -->|Full| SQLiteExec
    
 
   in1 -->|Partial| MySQLExec
 
   in2 -->|Partial| MySQLExec
 
   reindex -.->|Halted| MySQLExec
 
   replace -->|Partial| MySQLExec
    
 
   in1 -.->|Halted| MSSQLExec
 
   dropindex -->|Skipped statements| MSSQLExec
 
   reindex -.->|Halted| MSSQLExec
 
   replace -.->|Halted| MSSQLExec
    
 
   in1 -.->|Halted| OracleExec
 
   in2 -->|With type changes| OracleExec
 
   reindex -.->|Halted| OracleExec
 
   replace -.->|Halted| OracleExec

Test Execution Compatibility Map

The following diagram maps specific test files to their platform compatibility:

Diagram: Test File Execution by Platform

Sources: test/evidence/in1.test:3-9 test/evidence/slt_lang_reindex.test:22-32 test/evidence/slt_lang_replace.test:1-7 test/evidence/slt_lang_dropindex.test:22-28

Best Practices for Cross-Platform Test Design

When adding or modifying tests in the corpus, follow these patterns:

1. Assume SQLite as the Primary Target : All tests should run cleanly on SQLite without directives unless testing non-standard features.

2. Document Non-Standard Features : Use inline comments to explain why platform-specific directives are necessary, citing relevant SQL standard documentation when applicable.

3. Preferskipif Over onlyif: When excluding a single platform, use skipif <platform> rather than listing all other platforms with onlyif. This makes tests more maintainable as new platforms are added.

4. Usehalt for Wholesale Incompatibility: If most of a test file relies on platform-specific features, use the halt pattern at the file beginning rather than marking individual statements.

5. Provide Alternative Implementations When Possible : For syntax differences (like MySQL's UNIQUE index requirements), provide platform-specific alternatives rather than skipping functionality entirely.

6. Label Related Test Groups : Use the label-N syntax to group related tests that have different implementations across platforms but validate the same behavior.

Sources: test/evidence/in1.test:1-1156 test/evidence/in2.test:1-314

Summary

The sqllogictest corpus achieves cross-database compatibility through a directive-based conditional execution model. SQLite serves as the primary test target with full feature coverage, while MySQL, PostgreSQL, MSSQL, and Oracle have varying levels of support based on their SQL implementation conformance. The skipif, onlyif, and halt directives enable a single test corpus to validate behavior across platforms while gracefully handling platform-specific limitations and non-standard extensions.

For details on how these directives are parsed and executed, see Conditional Execution Directives. For a comprehensive catalog of platform-specific behaviors, see Platform-Specific Behaviors.