Integrate InterSystems IRIS with SQLancer for Automated SQL Testing and Validation

SQLancer is a tool to automatically test Database Management Systems (DBMSs) in order to find bugs in their implementation. That is, it finds bugs in the code of the DBMS implementation, rather than in queries written by the user. SQLancer has found hundreds of bugs in mature and widely-known DBMSs.

1️⃣ Description of the Idea

Support SQLancer as a fuzz testing framework for InterSystems IRIS SQL engine. SQLancer (SQL Testing via Stochastic Generation) is an automated testing tool developed to uncover logic bugs in SQL database management systems by generating random SQL queries and checking for inconsistencies across execution paths.

This idea proposes:

• Developing a SQLancer backend for InterSystems IRIS

• Contributing it either to the upstream SQLancer repo or as a standalone extension

• Using it as part of IRIS internal CI pipelines, especially during query planner and optimizer testing

2️⃣ Who is the Target Audience?

• InterSystems core product engineering and QA teams

• Advanced developers and partners building tools on top of the IRIS SQL engine

• Researchers and database enthusiasts experimenting with IRIS

• Hackathon participants who want to test edge cases of IRIS query behavior

3️⃣ What Problem Does It Solve?

Testing SQL engines is notoriously hard due to:

• Complex combinations of query structures, indexes, joins, constraints

• Rare edge cases that only appear in deep optimizer logic or query plans

SQLancer solves this by generating thousands of complex random queries that systematically reveal bugs, such as:

• Incorrect result sets

• Crashes

• Inconsistent plans for logically equivalent queries

By integrating IRIS into SQLancer:

• Bugs are caught early and reproducibly

• Confidence in the SQL optimizer, query planner, and execution engine increases

• Helps benchmark IRIS behavior against other databases in open testing environments

4️⃣ How Does This Impact Efficiency, Stability, Reliability, etc.?

• Stability: Automated regression tests reduce chance of bugs escaping to production

• Reliability: Ensures the SQL engine behaves correctly under complex, unpredictable conditions

• Efficiency: Speeds up validation during optimizer or SQL engine changes

• Security: Detects cases that could cause resource exhaustion or crashes

5️⃣ Use Case / Scenario

Suppose the InterSystems engineering team is working on a new SQL optimizer enhancement that rewrites certain joins. Normally, verifying correctness would require large, manually curated test sets.

By plugging IRIS into SQLancer:

• Thousands of valid, random SQL queries are generated against synthetic schemas

• The engine’s results are automatically validated for logical consistency

• Any unexpected error, mismatch, or crash is logged with a minimal reproduction case

• The team can run this continuously during development or CI/CD

This leads to faster innovation, safer releases, and higher trust in the SQL engine’s correctness.