This documentation is part of the "Projects with Books" initiative at zenOSmosis.
The source code for this project is available on GitHub.
Platform-Specific Behaviors
Relevant source files
- test/evidence/in1.test
- test/evidence/in2.test
- test/evidence/slt_lang_createview.test
- test/evidence/slt_lang_reindex.test
Purpose and Scope
This page catalogs the known SQL implementation differences between SQLite, MySQL, MSSQL, Oracle, and PostgreSQL as revealed by the test corpus. These differences determine when tests must use conditional execution directives (skipif, onlyif, halt) to adapt to platform-specific SQL semantics.
For information about the conditional execution directives themselves, see Conditional Execution Directives. For practical examples of integrating these tests into CI/CD pipelines, see Integrating with CI/CD Systems.
Platform Compatibility Overview
The test corpus supports five major database platforms, each with varying levels of SQL standard compliance and proprietary extensions. The following diagram illustrates the primary compatibility categories:
Diagram: Platform Support Matrix
graph TB
subgraph "SQLite Features"
SQLITE_EMPTY["Empty RHS in IN: IN ()"]
SQLITE_TABLE["Table-as-operand: SELECT 1 IN t1"]
SQLITE_REINDEX["REINDEX command"]
SQLITE_TEMP["TEMP VIEW support"]
SQLITE_READONLY["Strict read-only views"]
end
subgraph "MySQL Compatibility"
MYSQL_NO_EMPTY["Rejects empty IN ()"]
MYSQL_UNIQUE_LEN["UNIQUE requires length on TEXT"]
MYSQL_NO_TABLE["Requires subquery syntax"]
MYSQL_REPAIR["Uses REPAIR TABLE instead"]
end
subgraph "MSSQL Compatibility"
MSSQL_NO_EMPTY["Rejects empty IN ()"]
MSSQL_VIEW_DML["Allows DELETE/UPDATE on views"]
MSSQL_NO_REINDEX["No REINDEX support"]
end
subgraph "Oracle Compatibility"
ORACLE_NO_EMPTY["Rejects empty IN ()"]
ORACLE_VARCHAR["VARCHAR vs TEXT differences"]
ORACLE_NO_REINDEX["No REINDEX support"]
end
subgraph "PostgreSQL Compatibility"
POSTGRES_REINDEX["REINDEX supported"]
POSTGRES_STANDARD["High SQL standard compliance"]
end
Sources: test/evidence/in1.test:1-20 test/evidence/slt_lang_reindex.test:18-32 test/evidence/slt_lang_createview.test:65-86
Empty RHS in IN Operator
SQLite Extension : SQLite allows the right-hand side of IN and NOT IN operators to be an empty list, returning deterministic results regardless of the left operand.
| Left Operand | Expression | SQLite Result | Standard Behavior |
|---|---|---|---|
| Any value | x IN () | false (0) | Syntax error |
| Any value | x NOT IN () | true (1) | Syntax error |
| NULL | NULL IN () | false (0) | Syntax error |
| NULL | NULL NOT IN () | true (1) | Syntax error |
Platform Behavior :
Test File Implementation :
The test corpus uses onlyif sqlite directives to restrict empty RHS tests to SQLite:
onlyif sqlite # empty RHS
query I nosort
SELECT 1 IN ()
----
0
Sources: test/evidence/in1.test:11-26 test/evidence/in1.test:38-64 test/evidence/in2.test:2-11 test/evidence/in2.test:76-144
Table-as-Operand in IN Clause
SQLite Extension : SQLite allows a table name to appear directly as the right operand in IN expressions, treating it as shorthand for a subquery selecting all rows.
Syntax Comparison :
| Platform | Direct Table Syntax | Subquery Syntax | Behavior |
|---|---|---|---|
| SQLite | SELECT 1 IN t1 | SELECT 1 IN (SELECT * FROM t1) | Both valid |
| MySQL | Syntax error | SELECT 1 IN (SELECT * FROM t1) | Subquery required |
| MSSQL | Syntax error | SELECT 1 IN (SELECT * FROM t1) | Subquery required |
| Oracle | Syntax error | SELECT 1 IN (SELECT * FROM t1) | Subquery required |
| PostgreSQL | Syntax error | SELECT 1 IN (SELECT * FROM t1) | Subquery required |
Diagram: IN Clause Syntax Patterns
Test Pattern :
The corpus uses dual-labeled tests where SQLite tests the direct syntax with onlyif sqlite, and all platforms test the subquery syntax:
onlyif sqlite
query I nosort label-1
SELECT 1 IN t1
----
0
query I nosort label-1
SELECT 1 IN (SELECT * FROM t1)
----
0
Sources: test/evidence/in1.test:69-158 test/evidence/in1.test:239-327 test/evidence/in1.test:441-549
REINDEX Command Support
Platform Differences : The REINDEX command for rebuilding indexes exists only in SQLite and PostgreSQL, with no equivalent in the SQL standard.
Diagram: REINDEX Command Routing
flowchart TD
CMD["REINDEX t1i1"]
CMD --> CHECK_PLATFORM{"Database\nPlatform?"}
CHECK_PLATFORM -->|SQLite| SQLITE_EXEC["Execute REINDEX\nRebuild index"]
CHECK_PLATFORM -->|PostgreSQL| POSTGRES_EXEC["Execute REINDEX\nRebuild index"]
CHECK_PLATFORM -->|MySQL| MYSQL_ALT["Use REPAIR TABLE instead\nDifferent semantics"]
CHECK_PLATFORM -->|MSSQL| MSSQL_HALT["halt directive\nTest not applicable"]
CHECK_PLATFORM -->|Oracle| ORACLE_HALT["halt directive\nTest not applicable"]
SQLITE_EXEC --> SUCCESS["Index rebuilt"]
POSTGRES_EXEC --> SUCCESS
MYSQL_ALT --> MYSQL_SUCCESS["Table repaired"]
MSSQL_HALT --> SKIP["Test skipped"]
ORACLE_HALT --> SKIP
Implementation Table :
| Platform | REINDEX Support | Alternative | Test Directive |
|---|---|---|---|
| SQLite | ✓ Full support | N/A | No directive needed |
| PostgreSQL | ✓ Full support | N/A | No directive needed |
| MySQL | ✗ Not supported | REPAIR TABLE [tbl_name] | onlyif mysql + halt |
| MSSQL | ✗ Not supported | Automatic maintenance | onlyif mssql + halt |
| Oracle | ✗ Not supported | Automatic maintenance | onlyif oracle + halt |
Test File Halt Pattern :
# 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 test/evidence/slt_lang_reindex.test:34-38
View Mutability
Critical Difference : MSSQL allows DML operations (DELETE, UPDATE) on views, while SQLite enforces strict read-only semantics. This represents a significant behavioral divergence.
Platform Comparison Table :
| Operation | SQLite | MySQL | MSSQL | Oracle | PostgreSQL | SQL Standard |
|---|---|---|---|---|---|---|
DELETE FROM view | Error | Error | Allowed | Error | Conditional | Disallowed |
INSERT INTO view | Error | Error | Error | Error | Conditional | Disallowed |
UPDATE view SET | Error | Error | Allowed | Error | Conditional | Disallowed |
| INSTEAD OF triggers | ✓ Supported | Limited | ✓ Supported | ✓ Supported | ✓ Supported | Optional |
Diagram: View DML Operation Flow
Test Implementation Pattern :
# MSSQL allows DELETE on views
skipif mssql # this is allowed
statement error
DELETE FROM view1 WHERE x>0
onlyif mssql # this is allowed
statement ok
DELETE FROM view1 WHERE x>0
# MSSQL allows UPDATE on views
skipif mssql # this is allowed
statement error
UPDATE view1 SET x=2
onlyif mssql # this is allowed
statement ok
UPDATE view1 SET x=2
Sources: test/evidence/slt_lang_createview.test:65-86 test/evidence/slt_lang_createview.test:87-103
Temporary View Support
SQLite Feature : SQLite supports the TEMP and TEMPORARY keywords for creating session-scoped views that are automatically dropped when the database connection closes.
Syntax Availability :
| Platform | CREATE TEMP VIEW | CREATE TEMPORARY VIEW | Notes |
|---|---|---|---|
| SQLite | ✓ Supported | ✓ Supported | Auto-deleted on close |
| MySQL | Limited | Limited | Different syntax/semantics |
| MSSQL | Different | Different | Uses temp tables instead |
| Oracle | Different | Different | Uses global temp tables |
| PostgreSQL | ✓ Supported | ✓ Supported | Similar to SQLite |
Test Pattern :
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
Sources: test/evidence/slt_lang_createview.test:42-53
NULL Handling in IN Operator
MySQL-Specific Issue : MySQL exhibits inconsistent behavior when evaluating NULL IN (SELECT ...) compared to other platforms.
Behavior Matrix :
| Expression | SQLite | MySQL | Expected Result |
|---|---|---|---|
NULL IN (SELECT * FROM t4) | Returns NULL | Fails/Different | NULL |
NULL NOT IN (SELECT * FROM t4) | Returns NULL | Fails/Different | NULL |
NULL IN (2,3,4) | Returns NULL | Returns NULL | NULL |
Test Compensation :
# mysql is failing this one
skipif mysql
query I nosort label-55
SELECT null IN (SELECT * FROM t4)
----
NULL
# mysql is failing this one
skipif mysql
query I nosort label-56
SELECT null NOT IN (SELECT * FROM t4)
----
NULL
Sources: test/evidence/in1.test:951-975 test/evidence/in1.test:1005-1023
UNIQUE Constraint on TEXT Columns
MySQL Limitation : MySQL requires an explicit length specification when creating UNIQUE constraints on TEXT columns, while other platforms infer appropriate defaults.
Platform-Specific CREATE TABLE Patterns :
Test Implementation :
skipif mysql
statement ok
CREATE TABLE t7(a TEXT UNIQUE)
onlyif mysql
statement ok
CREATE TABLE t7(a TEXT, UNIQUE (a(1)))
The MySQL-specific syntax UNIQUE (a(1)) creates a unique index on the first character of the TEXT column.
Sources: test/evidence/in1.test:398-405 test/evidence/in1.test:421-428
Data Type Compatibility
Oracle-Specific : Oracle uses VARCHAR for variable-length character data, while SQLite and other platforms commonly use TEXT.
Type Mapping Table :
| Logical Type | SQLite | MySQL | MSSQL | Oracle | PostgreSQL |
|---|---|---|---|---|---|
| Variable text | TEXT | TEXT | TEXT/VARCHAR | VARCHAR | TEXT |
| Fixed text | TEXT | CHAR | CHAR | CHAR | CHAR |
| Large text | TEXT | TEXT/LONGTEXT | TEXT/VARCHAR(MAX) | CLOB | TEXT |
Conditional Schema Creation :
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
stateDiagram-v2
[*] --> FileStart : Open test file
FileStart --> CheckDirective : Read directive
CheckDirective --> OnlyIfMSSQL : onlyif mssql
CheckDirective --> OnlyIfOracle : onlyif oracle
CheckDirective --> OnlyIfMySQL : onlyif mysql
CheckDirective --> ProceedTests : No platform halt
OnlyIfMSSQL --> IsMSSQL{Running\nMSSQL?}
OnlyIfOracle --> IsOracle{Running\nOracle?}
OnlyIfMySQL --> IsMySQL{Running\nMySQL?}
IsMSSQL -->|Yes| Halt : halt
IsMSSQL -->|No| ProceedTests
IsOracle -->|Yes| Halt
IsOracle -->|No| ProceedTests
IsMySQL -->|Yes| Halt
IsMySQL -->|No| ProceedTests
Halt --> [*] : Skip entire file
ProceedTests --> RunTests : Execute tests
Platform Halt Strategy
File-Level Exclusion : Some test files are entirely incompatible with certain platforms and use the halt directive at the file start to skip execution.
Diagram: File-Level Halt Decision Tree
Examples from Test Files :
in1.test excludes MSSQL and Oracle:
# skip this entire file if ms sql server
onlyif mssql
halt
# skip this entire file if oracle
onlyif oracle
halt
slt_lang_reindex.test excludes MSSQL, Oracle, and MySQL:
# 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/in1.test:3-9 test/evidence/slt_lang_reindex.test:22-32
Summary: Cross-Platform Testing Matrix
The following table summarizes key SQL feature support across platforms:
| Feature | SQLite | MySQL | MSSQL | Oracle | PostgreSQL |
|---|---|---|---|---|---|
| Empty IN () | ✓ | ✗ | ✗ | ✗ | ✗ |
| Table-as-operand | ✓ | ✗ | ✗ | ✗ | ✗ |
| REINDEX | ✓ | ✗ (REPAIR) | ✗ | ✗ | ✓ |
| TEMP VIEW | ✓ | Limited | Different | Different | ✓ |
| View DML | ✗ (strict) | ✗ | ✓ (allowed) | ✗ | Conditional |
| NULL IN SELECT | ✓ | Issues | ✓ | ✓ | ✓ |
| TEXT UNIQUE | ✓ | Length req'd | ✓ | VARCHAR | ✓ |
These platform differences necessitate extensive use of conditional directives throughout the test corpus to ensure accurate validation across all supported database engines.
Sources: test/evidence/in1.test:1-1156 test/evidence/in2.test:1-314 test/evidence/slt_lang_createview.test:1-129 test/evidence/slt_lang_reindex.test:1-59