Modern SQL engines are enormously complex pieces of software. Even when they appear stable and mature, subtle bugs can hide in their optimizers, type systems, predicate evaluation, or execution layers. These bugs rarely announce themselves loudly. Instead, they quietly produce incorrect results, behave inconsistently, or fail abruptly under specific combinations of SQL constructs.

This is precisely why tools like SQLancer exist. SQLancer automatically generates SQL queries and uses logical “oracles” to detect when a database behaves incorrectly. It has revealed hundreds of real bugs in widely used systems such as MySQL, PostgreSQL, SQLite, and DuckDB.

With this in mind, I attempted to bring SQLancer to InterSystems IRIS, starting with the NOREC oracle — a powerful method for detecting optimizer correctness issues. The journey, however, uncovered not just potential SQL correctness problems, but also a surprising number of driver-level and server-level failures that prevented SQLancer from running at full strength.

This article summarizes why SQLancer is important, how the NOREC oracle works, and what unexpected findings appeared while testing IRIS.

What is SQLancer?

SQLancer (SQL Testing via Differential Oracles) is a framework designed to uncover hidden correctness bugs in database systems — specifically bugs that cause a database to return wrong results without crashing or reporting an error. Instead of relying on handcrafted test cases or predefined expected outputs, SQLancer uses a powerful idea:

Generate random and syntactically valid SQL queries, run them against the database, and check whether different logically equivalent forms of the query produce the same results.

If they don’t, it indicates that the database engine evaluated the query incorrectly.

SQLancer has been remarkably successful in practice. It has found correctness bugs in virtually every major SQL system, including:

• MySQL
• PostgreSQL
• SQLite
• MariaDB
• DuckDB
• CockroachDB
• TiDB
• and many others

These bugs often involved query optimizers rewriting expressions incorrectly, mishandling NULLs, evaluating predicates inconsistently, or performing illegal simplifications.

Unlike typical fuzzers, SQLancer is not interested in simply causing crashes. Its primary goal is to reveal semantic defects — the hardest kind of bug to detect, and the most dangerous for real applications.

Bringing SQLancer to InterSystems IRIS means giving IRIS the same level of scrutiny that other major engines have already received.

How the NOREC Oracle Works

SQLancer supports multiple “oracles,” each representing a different strategy for detecting incorrect behavior. For the IRIS integration, the focus is on the NOREC oracle.

NOREC (short for “No Rewriting”) is based on a simple and elegant insight:

If you take a query and rewrite it into a form that prevents the optimizer from applying any complex transformations, then both versions should always return the same result.

In other words, NOREC compares:

1. The original query, which the optimizer is free to rewrite, reorder, simplify, and transform.
2. A rewritten version of the query, where all filtering is performed explicitly using a CASE expression inside the aggregate function.
   This version deliberately avoids giving the optimizer opportunities to change semantics.

For example:

Original query:

SELECT SUM(x) FROM t WHERE a > 10 AND (b IS NULL OR b < 5);

NOREC-rewritten query:

SELECT SUM(
         CASE WHEN a > 10 AND (b IS NULL OR b < 5)
              THEN x
              ELSE 0
         END
       )
FROM t;

Because the rewritten query forces evaluation to occur row-by-row without shortcuts, it serves as a correctness baseline.

If the results differ between the original and rewritten form, several things may be wrong:

• the optimizer incorrectly simplified or reordered the predicate
• NULL handling differed between expressions
• type conversions were inconsistent
• the executor incorrectly evaluated the condition
• the optimizer applied an illegal rewrite

The power of NOREC is that it does not require any knowledge of the expected value. The comparison is enough.

For decades, the hardest bugs in SQL engines have come not from syntax or crashes, but from optimizers making incorrect assumptions. NOREC is specifically designed to expose such issues.

Why Correctness Testing Matters for IRIS

InterSystems IRIS is often deployed in environments where correctness is not optional:

• healthcare
• banking and payments
• supply chain and logistics
• government services
• high-reliability global systems

In such domains, a silent incorrect query result is far more dangerous than a crash.

A crash is disruptive, but it is visible. Someone can notice, investigate, and repair the situation.

A silent wrong answer, however:

• corrupts analytics
• misleads business logic
• propagates into reports
• influences decisions
• hides undetected for years

Even small inconsistencies — one row missing due to an incorrect rewrite, a predicate evaluated incorrectly, or a NULL handled in a non-standard way — can have large downstream effects.

This is why tools like SQLancer matter.

By generating a wide variety of SQL queries — including strange, unexpected combinations of predicates — it forces the database engine into corners that normal workloads rarely reach. If IRIS behaves inconsistently or incorrectly, SQLancer can reveal the problem long before it affects a real production system.

During the early experiments for this project, the unexpected driver and server errors that surfaced indicate that IRIS under random SQL generation may expose robustness issues even before correctness oracles come into play. This finding alone reinforces the importance of deeper testing.

Correctness testing isn’t about proving that the database is broken.
It’s about building confidence that it behaves correctly even in the most obscure corner cases — a goal that every SQL engine benefits from.

Hot to run it

It requires Java 17, and InterSystems IRIS Enterprise (it uses many connections).

Clone github repository

https://github.com/caretdev/sqlancer-iris.git

Build the project

cd sqlancer-iris
mvn package -DskipTests

Run with oracle NOREC

cd target
java -jar sqlancer-iris*.jar iris --oracle norec --connection-url IRIS://_SYSTEM:SYS@localhost:1972/USER

It is possible to use other oracles as well:

• WHERE
• Fuzzer

When running it, tool will print all the SQL queries executed, and issues found during the run

Run Tests using testcontainers

This way it will create container with IRIS will start it and run tests against it.

mvn -Dtest=TestIRISNOREC clean test 
mvn -Dtest=TestIRISWHERE clean test 
mvn -Dtest=TestIRISFuzzer clean test 

Findings so far

• Invalid Message Count. Happening quite often and for any query.

  java.sql.SQLException: Invalid Message Count (452); expected: 5 got: -1. Server returned: 406. Closing Connection
          at com.intersystems.jdbc.InStream.checkHeader(InStream.java:123)
          at com.intersystems.jdbc.InStream.readMessageInternal(InStream.java:241)
          at com.intersystems.jdbc.InStream.readMessage(InStream.java:173)
          at com.intersystems.jdbc.InStream.readMessage(InStream.java:148)
          at com.intersystems.jdbc.IRISStatement.sendDirectUpdateRequest(IRISStatement.java:458)
          at com.intersystems.jdbc.IRISStatement.Update(IRISStatement.java:439)
          at com.intersystems.jdbc.IRISStatement.execute(IRISStatement.java:740)
          at sqlancer.iris.IRISProvider.createDatabase(IRISProvider.java:123)
          at sqlancer.iris.IRISProvider.createDatabase(IRISProvider.java:1)
          at sqlancer.Main$DBMSExecutor.run(Main.java:450)
          at sqlancer.Main$2.run(Main.java:684)
          at sqlancer.Main$2.runThread(Main.java:666)
          at sqlancer.Main$2.run(Main.java:657)
          at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
          at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
          at java.base/java.lang.Thread.run(Thread.java:829)

   
• Server closed communication device

  Caused by: java.sql.SQLException: Communication error:  Server closed communication device
          at com.intersystems.jdbc.InStream.read(InStream.java:62)
          at com.intersystems.jdbc.InStream.readBuffer(InStream.java:74)
          at com.intersystems.jdbc.InStream.checkHeader(InStream.java:101)
          at com.intersystems.jdbc.InStream.readMessageInternal(InStream.java:241)
          at com.intersystems.jdbc.InStream.readMessage(InStream.java:173)
          at com.intersystems.jdbc.InStream.readMessage(InStream.java:148)
          at com.intersystems.jdbc.IRISStatement.sendDirectQueryRequest(IRISStatement.java:351)
          at com.intersystems.jdbc.IRISStatement.Query(IRISStatement.java:269)
          at com.intersystems.jdbc.IRISStatement.executeQuery(IRISStatement.java:170)

• Unable to obtain group command lock. Usually happens during table creation

  Caused by: java.sql.SQLException: [SQLCODE: <-400>:<Fatal error occurred>]
  [Location: <ServerLoop>]
  [%msg: <ERROR #7808: Unable to obtain group '8777833385654' command lock.>]
          at com.intersystems.jdbc.IRISConnection.getServerError(IRISConnection.java:1001)
          at com.intersystems.jdbc.IRISConnection.processError(IRISConnection.java:1146)
          at com.intersystems.jdbc.InStream.readMessageInternal(InStream.java:284)
          at com.intersystems.jdbc.InStream.readMessage(InStream.java:173)
          at com.intersystems.jdbc.InStream.readMessage(InStream.java:148)
          at com.intersystems.jdbc.IRISStatement.sendDirectUpdateRequest(IRISStatement.java:458)
          at com.intersystems.jdbc.IRISStatement.Update(IRISStatement.java:439)
          at com.intersystems.jdbc.IRISStatement.execute(IRISStatement.java:740)
          at sqlancer.common.query.SQLQueryAdapter.internalExecute(SQLQueryAdapter.java:140)
  

• SQL: Property does not exists. looks like something unicode related

  SELECT 1 WHERE ('') = ('_㉸^');

  Totally fine SQL query, which gives strange error

  SQL Error [400] [S1000]: [SQLCODE: <-400>:<Fatal error occurred>]
  [Location: <ServerLoop - Query Open()>]
  [%msg: <Unexpected error occurred:  <PROPERTY DOES NOT EXIST>%C0o+7^%sqlcq.USER.cls592.1 *i%c㉸4,%sqlcq.USER.cls592>]

  How come, that literal value became a property

Unfortunately due to, too many errors that I have no control of quite difficult to continue any further development. 

If you think that this tool worth of existing, and we need all already found bugs fixing as well as a new ones, please vote on the contest.