Query Result Type Mismatch Between Test Definition and Database Schema

Schema-Query Type Conflict in SqlLogicTest Framework

The core issue revolves around discrepancies between declared result types in SqlLogicTest queries and actual column data types defined in database schemas. This creates runtime failures in test drivers that strictly enforce type expectations, particularly affecting JDBC-based implementations. A concrete example involves a test case where:

1. A table tab0 is created with a TEXT-type column col5
2. A query labeled label-220 selects col5 while declaring an INTEGER result type via query I
3. The test driver attempts to read TEXT values as integers using sqlite3_column_int() in ODBC or ResultSet.getInt() in JDBC
4. ODBC silently converts non-integer strings to 0 (matching the test’s hash expectation), while JDBC throws fatal exceptions

This exposes a fundamental tension between SQLite’s dynamic typing system and the rigid type enforcement mechanisms employed by database connectivity APIs. The SqlLogicTest framework implicitly relies on specific type coercion behaviors that are not universally consistent across all data access layers, leading to test suite failures when drivers enforce strict type checking.

Key Technical Components

1. SqlLogicTest Type Declarations
Test cases use prefixes like query I, query R, or query T to define expected result types for each column:

• I: Integer (calls sqlite3_column_int() in ODBC)
• R: Real (calls sqlite3_column_double())
• T: Text (calls sqlite3_column_text())

2. SQLite Type Affinity vs Storage Classes
SQLite columns have type affinity (schema-declared type hints) but store data in five storage classes (INTEGER, REAL, TEXT, BLOB, NULL). This allows inserting strings into integer-affinity columns and vice versa.

3. Driver-Specific Type Coercion

• ODBC (slt_sqlite.c): Converts non-integer text to 0 via sqlite3_column_int()
• JDBC: Throws SQLException when ResultSet.getInt() encounters non-numeric text

4. Test Validation Mechanism
Result sets are hashed after applying type-specific formatting. The hash 2ed57cb9c408b954ec52c7a2da59153d in the test case matches 1000 zeroes – the consequence of ODBC’s silent conversion rather than actual text values.

Driver Behavior Divergence and Implicit Type Conversion Assumptions

Root Cause 1: Test Suite’s Dependency on ODBC-Specific Type Coercion

The SqlLogicTest suite was originally designed against SQLite’s C API (via ODBC), which implements lenient type conversions:

• sqlite3_column_int() returns 0 for TEXT values lacking numeric prefixes
• Non-printable characters are replaced with @ during hashing

This creates false positives when tests:

1. Select text columns with query I type declarations
2. Rely on ODBC’s silent 0 conversions to produce hashable integer outputs

JDBC drivers that reject non-integer text in getInt() calls expose this dependency as a test suite defect rather than a database engine error.

Root Cause 2: Ambiguous Column Type Declarations in Test Queries

The CREATE TABLE statement defines col5 as TEXT, but label-220’s SELECT ALL col5 col0 projects this column with an alias col0 – a name that might suggest integer affinity if interpreted literally. However, column aliases do not influence type affinity or storage class in SQLite.

The test erroneously assumes that:

• The result set’s declared type (query I) overrides the underlying column’s schema-defined type
• All test drivers will apply ODBC-style type coercion when reading values

Root Cause 3: Hash Collision Masking Logical Inconsistencies

The expected hash 2ed57cb9c408b954ec52c7a2da59153d corresponds to 1000 rows of zero-valued integers. This matches ODBC’s conversion of non-numeric col5 text to 0 but hides two critical issues:

1. Data Integrity Violation: Actual col5 values are text, not integers
2. Test Oracle Problem: The test validates driver coercion behavior rather than database logic

Resolving Type Mismatches in SqlLogicTest Implementations

Step 1: Audit Test Cases for Schema-Query Type Consistency

Action:
Review all query [IRT] declarations against underlying table schemas. Flag queries where:

• query I selects TEXT/REAL columns without explicit CAST
• query T selects INTEGER/REAL columns with possible numeric formatting

Example Fix for label-220:

-- Original problematic query
SELECT ALL col5 col0 FROM tab0 WHERE + col3 IS NOT NULL  

-- Corrected version with explicit cast
SELECT CAST(col5 AS INTEGER) col0 FROM tab0 WHERE col3 IS NOT NULL  

Validation:
Run the modified query through both ODBC and JDBC drivers. Confirm that:

• ODBC produces identical hashes (due to existing 0 coercion)
• JDBC no longer throws exceptions during getInt() calls

Step 2: Implement Type-Safe Wrappers in Test Drivers

For JDBC-Based Drivers:
Modify result set processing to handle type mismatches gracefully:

// Original crash-prone code
int val = rs.getInt(1);  

// Revised type-safe implementation
Object rawVal = rs.getObject(1);  
String strVal = (rawVal != null) ? rawVal.toString() : "NULL";  

// Apply ODBC-style coercion for backward compatibility
if (expectedType == 'I') {
    try {
        strVal = String.valueOf(Integer.parseInt(strVal));  
    } catch (NumberFormatException e) {  
        strVal = "0"; // Mirror ODBC behavior  
    }  
}  
appendToHash(strVal);  

Rationale:
This mimics ODBC’s silent conversion while allowing JDBC drivers to execute existing SqlLogicTest cases without modification.

Step 3: Introduce Test Suite Metadata Validation

Augment the test runner with schema-aware validation:

1. Parse CREATE TABLE statements to build column type maps
2. Cross-reference SELECT columns against their schema-defined types
3. Flag mismatches between query [IRT] declarations and schema types

Implementation Pseudocode:

def validate_test_case(test_case):
    schema = parse_schema(test_case.create_table)  
    for query in test_case.queries:  
        selected_cols = parse_selected_columns(query.sql)  
        for i, col in enumerate(selected_cols):  
            schema_type = schema.get(col.name, 'TEXT')  
            declared_type = query.result_types[i]  
            if not is_compatible(schema_type, declared_type):  
                log_warning(f"Type mismatch: {col.name} is {schema_type} "
                            f"but queried as {declared_type}")  

Compatibility Rules:

• query I: Allow INTEGER schema types; warn on TEXT/REAL without CAST
• query T: Always allowed (all types can be stringified)
• query R: Allow REAL/INTEGER schema types; warn on TEXT without CAST

Step 4: Enhance Test Documentation with Type Handling Policies

Update SqlLogicTest documentation to clarify:

1. Type Declaration Semantics:

   • query I/R/T define coercion rules, not schema expectations
   • Tests may intentionally include type mismatches to validate driver behavior

2. Driver Implementation Guidelines:

   • Require ODBC-style silent conversions for backward compatibility
   • Provide opt-in flags for strict type checking (e.g., --strict-types)

3. Test Case Tags:

   • Add coercion tags to tests relying on implicit type conversions
   • Allow test runners to exclude such tests when validating strict type systems

Step 5: Establish Cross-Driver Result Normalization

Define a canonical result formatting spec that all drivers must follow:

Original Type	Declared Type	Normalization Rule
TEXT	I	Parse as integer; 0 if invalid
TEXT	R	Parse as double; 0.0 if invalid
INTEGER	T	Stringify with %d format
REAL	I	Convert to integer via truncation

Driver Compliance Checklist:

• [ ] Implement normalization before hashing
• [ ] Apply ASCII substitution for non-printables (e.g., \x01 → @)
• [ ] Handle NULL as literal string NULL

Step 6: Propose Test Suite Amendments to Upstream

For long-term resolution, submit patches to the SqlLogicTest suite:

Patch 1: Explicit Casts in Ambiguous Queries
Modify label-220 and similar tests to use explicit type conversions:

-- Before
SELECT col5 FROM tab0 ...  

-- After  
SELECT CAST(col5 AS INTEGER) FROM tab0 ...  

Patch 2: Add Type Coercion Test Category
Introduce a new test directory coercion containing intentional type mismatch cases, clearly separated from core SQL logic tests.

Patch 3: Enhance Result Formatting Utilities
Provide reference implementations in multiple languages (C, Java, Python) for consistent result normalization across drivers.

Outcome Validation and Regression Prevention

Verification Metrics:

1. JDBC Driver: Execute entire SqlLogicTest suite without SQLException crashes
2. Hash Consistency: Ensure all tests produce identical hashes across ODBC/JDBC after normalization
3. Type Warning Logs: Confirm schema-query type mismatch warnings are emitted during test runs

Continuous Integration Checks:

• Add schema-query type validation as a pre-commit hook
• Run tests with both --strict-types and default coercion modes
• Compare hashes against a golden set generated with reference normalization

By methodically addressing the interplay between SQLite’s type flexibility, driver-specific coercion behaviors, and test suite assumptions, implementers can achieve cross-driver compatibility while preserving the integrity of SQL logic validation.