sqlancer · mrigger · Aug 11, 2025 · Aug 3, 2025 · Aug 8, 2025 · Aug 8, 2025
diff --git a/.github/workflows/main.yml b/.github/workflows/main.yml
@@ -467,6 +467,7 @@ jobs:
           MYSQL_AVAILABLE=true mvn test -Dtest=TestMySQLPQS
           MYSQL_AVAILABLE=true mvn test -Dtest=TestMySQLTLP
           MYSQL_AVAILABLE=true mvn test -Dtest=TestMySQLCERT
+          MYSQL_AVAILABLE=true mvn test -Dtest=TestMySQLDQE
 
   oceanbase:
     name: DBMS Tests (OceanBase)

diff --git a/src/sqlancer/common/oracle/DQEBase.java b/src/sqlancer/common/oracle/DQEBase.java
@@ -0,0 +1,149 @@
+package sqlancer.common.oracle;
+
+import java.sql.SQLException;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import sqlancer.Main;
+import sqlancer.MainOptions;
+import sqlancer.SQLConnection;
+import sqlancer.SQLGlobalState;
+import sqlancer.common.query.ExpectedErrors;
+import sqlancer.common.query.SQLQueryAdapter;
+import sqlancer.common.query.SQLQueryError;
+import sqlancer.common.schema.AbstractRelationalTable;
+import sqlancer.mysql.MySQLSchema.MySQLTables;
+
+/*
+ * In DBMSs, SELECT, UPDATE and DELETE queries utilize predicates (i.e., WHERE clauses) to specify which rows to retrieve, update or delete, respectively.
+ * If they use the same predicate φ, they should access the same rows in a database.
+ * Ideally, DBMSs can adopt the same implementations for predicate evaluation in SELECT, UPDATE and DELETE queries.
+ * However, a DBMS usually adopts different implementations for predicate evaluation in SELECT, UPDATE and DELETE queries due to various optimization choices.
+ * Inconsistent implementations for predicate evaluation among these queries can cause SELECT, UPDATE and DELETE queries with the same predicate φ to access different rows.
+ *
+ *
+ * Inspired by this key observation, we propose Differential Query Execution(DQE), a novel and general approach to detect logic bugs in SELECT, UPDATE and DELETE queries.
+ * DQE solves the test oracle problem by executing SELECT, UPDATE and DELETE queries with the same predicate φ, and observing inconsistencies among their execution results.
+ * For example, if a row that is updated by an UPDATE query with a predicate φ does not appear in the query result of a SELECT query with the same predicate φ, a logic bug is detected in the target DBMS.
+ * The key challenge of DQE is to automatically obtain the accessed rows for a given SELECT, UPDATE or DELETE query.
+ * To address this challenge, we append two extra columns to each table in a database, to uniquely identify each row and track whether a row has been modified, respectively.
+ * We further rewrite SELECT and UPDATE queries to identify their accessed rows.
+ *
+ * more information see [DQE paper](https://ieeexplore.ieee.org/document/10172736)
+ */
+
+public abstract class DQEBase<S extends SQLGlobalState<?, ?>> {
+
+    public static final String COLUMN_ROWID = "rowId";
+    public static final String COLUMN_UPDATED = "updated";
+
+    protected final S state;
+    protected final ExpectedErrors selectExpectedErrors = new ExpectedErrors();
+    protected final ExpectedErrors updateExpectedErrors = new ExpectedErrors();
+    protected final ExpectedErrors deleteExpectedErrors = new ExpectedErrors();
+
+    protected final Main.StateLogger logger;
+    protected final MainOptions options;
+    protected final SQLConnection con;
+
+    public DQEBase(S state) {
+        this.state = state;
+        this.con = state.getConnection();
+        this.logger = state.getLogger();
+        this.options = state.getOptions();
+    }
+
+    public abstract String generateSelectStatement(MySQLTables tables, String tableName, String whereClauseStr);
+
+    public abstract String generateUpdateStatement(MySQLTables tables, String tableName, String whereClauseStr);
+
+    public abstract String generateDeleteStatement(MySQLTables tables, String tableName, String whereClauseStr);
+
+    /**
+     * Add auxiliary columns to the database A abstract method, subclasses need to implement it.
+     *
+     * @param table
+     *
+     * @throws SQLException
+     */
+    public abstract void addAuxiliaryColumns(AbstractRelationalTable<?, ?, ?> table) throws SQLException;
+
+    public void dropAuxiliaryColumns(AbstractRelationalTable<?, ?, ?> table) throws SQLException {
+        String tableName = table.getName();
+        String dropColumnRowId = String.format("ALTER TABLE %s DROP COLUMN %s", tableName, COLUMN_ROWID);
+        new SQLQueryAdapter(dropColumnRowId).execute(state);
+        String dropColumnUpdated = String.format("ALTER TABLE %s DROP COLUMN %s", tableName, COLUMN_UPDATED);
+        new SQLQueryAdapter(dropColumnUpdated).execute(state);
+    }
+
+    // This interface is to record Error code
+    public interface UpdateErrorCodes {
+
+    }
+
+    public interface ErrorCodeStrategy {
+        Set<Integer> getUpdateSpecificErrorCodes();
+
+        Set<Integer> getDeleteSpecificErrorCodes();
+
+    }
+
+    /**
+     * The core idea of DQE is that the SELECT, UPDATE and DELETE queries with the same predicate φ should access the
+     * same rows. If these queries access different rows, DQE reveals a potential logic bug in the target DBMS.
+     */
+    public static class SQLQueryResult {
+
+        private final Map<AbstractRelationalTable<?, ?, ?>, Set<String>> accessedRows; // Table name with respect rows
+        private final List<SQLQueryError> queryErrors;
+
+        public SQLQueryResult(Map<AbstractRelationalTable<?, ?, ?>, Set<String>> accessedRows,
+                List<SQLQueryError> queryErrors) {
+            this.accessedRows = accessedRows;
+            this.queryErrors = queryErrors;
+        }
+
+        public Map<AbstractRelationalTable<?, ?, ?>, Set<String>> getAccessedRows() {
+            return accessedRows;
+        }
+
+        public List<SQLQueryError> getQueryErrors() {
+            return queryErrors;
+        }
+
+        public boolean hasEmptyErrors() {
+            return queryErrors.isEmpty();
+        }
+
+        public boolean hasSameErrors(SQLQueryResult that) {
+            if (queryErrors.size() != that.getQueryErrors().size()) {
+                return false;
+            } else {
+                for (int i = 0; i < queryErrors.size(); i++) {
+                    if (!queryErrors.get(i).equals(that.getQueryErrors().get(i))) {
+                        return false;
+                    }
+                }
+            }
+            return true;
+        }
+
+        public boolean hasAccessedRows() {
+            if (accessedRows.isEmpty()) {
+                return false;
+            }
+            for (Set<String> accessedRow : accessedRows.values()) {
+                if (!accessedRow.isEmpty()) {
+                    return true;
+                }
+            }
+            return false;
+        }
+
+        public boolean hasSameAccessedRows(SQLQueryResult that) {
+            return accessedRows.equals(that.getAccessedRows());
+        }
+
+    }
+}
diff --git a/src/sqlancer/common/query/SQLQueryAdapter.java b/src/sqlancer/common/query/SQLQueryAdapter.java
@@ -81,17 +81,55 @@ public String getUnterminatedQueryString() {
         return result;
     }
 
+    /**
+     * This method is used to mostly oracles, which need to report exceptions. We set the reportException parameter to
+     * true by default meaning that exceptions are reported.
+     *
+     * @param globalState
+     * @param fills
+     *
+     * @return whether the query was executed successfully
+     *
+     * @param <G>
+     *
+     * @throws SQLException
+     */
     @Override
     public <G extends GlobalState<?, ?, SQLConnection>> boolean execute(G globalState, String... fills)
             throws SQLException {
+        return execute(globalState, true, fills);
+    }
+
+    /**
+     * This method is used to DQE oracles, DQE does not check exception separately, while other testing methods may
+     * need. We use reportException to control this behavior. For a specific DBMS used DQE oracle, we call this method
+     * and pass a boolean value of false as an argument.
+     *
+     * @param globalState
+     * @param reportException
+     * @param fills
+     *
+     * @return whether the query was executed successfully
+     *
+     * @param <G>
+     *
+     * @throws SQLException
+     */
+    public <G extends GlobalState<?, ?, SQLConnection>> boolean execute(G globalState, boolean reportException,
+            String... fills) throws SQLException {
+        return internalExecute(globalState.getConnection(), reportException, fills);
+    }
+
+    protected <G extends GlobalState<?, ?, SQLConnection>> boolean internalExecute(SQLConnection connection,
+            boolean reportException, String... fills) throws SQLException {
         Statement s;
         if (fills.length > 0) {
-            s = globalState.getConnection().prepareStatement(fills[0]);
+            s = connection.prepareStatement(fills[0]);
             for (int i = 1; i < fills.length; i++) {
                 ((PreparedStatement) s).setString(i, fills[i]);
             }
         } else {
-            s = globalState.getConnection().createStatement();
+            s = connection.createStatement();
         }
         try {
             if (fills.length > 0) {
@@ -103,7 +141,9 @@ public String getUnterminatedQueryString() {
             return true;
         } catch (Exception e) {
             Main.nrUnsuccessfulActions.addAndGet(1);
-            checkException(e);
+            if (reportException) {
+                checkException(e);
+            }
             return false;
         } finally {
             s.close();
@@ -127,14 +167,24 @@ public void checkException(Exception e) throws AssertionError {
     @Override
     public <G extends GlobalState<?, ?, SQLConnection>> SQLancerResultSet executeAndGet(G globalState, String... fills)
             throws SQLException {
+        return executeAndGet(globalState, true, fills);
+    }
+
+    public <G extends GlobalState<?, ?, SQLConnection>> SQLancerResultSet executeAndGet(G globalState,
+            boolean reportException, String... fills) throws SQLException {
+        return internalExecuteAndGet(globalState.getConnection(), reportException, fills);
+    }
+
+    protected <G extends GlobalState<?, ?, SQLConnection>> SQLancerResultSet internalExecuteAndGet(
+            SQLConnection connection, boolean reportException, String... fills) throws SQLException {
         Statement s;
         if (fills.length > 0) {
-            s = globalState.getConnection().prepareStatement(fills[0]);
+            s = connection.prepareStatement(fills[0]);
             for (int i = 1; i < fills.length; i++) {
                 ((PreparedStatement) s).setString(i, fills[i]);
             }
         } else {
-            s = globalState.getConnection().createStatement();
+            s = connection.createStatement();
         }
         ResultSet result;
         try {
@@ -151,9 +201,11 @@ public void checkException(Exception e) throws AssertionError {
         } catch (Exception e) {
             s.close();
             Main.nrUnsuccessfulActions.addAndGet(1);
-            checkException(e);
+            if (reportException) {
+                checkException(e);
+            }
+            return null;
         }
-        return null;
     }
 
     @Override

diff --git a/src/sqlancer/common/query/SQLancerResultSet.java b/src/sqlancer/common/query/SQLancerResultSet.java
@@ -42,6 +42,14 @@ public String getString(int i) throws SQLException {
         }
     }
 
+    public String getString(String colName) throws SQLException {
+        return rs.getString(colName);
+    }
+
+    public int getInt(String colName) throws SQLException {
+        return rs.getInt(colName);
+    }
+
     public boolean isClosed() throws SQLException {
         return rs.isClosed();
     }

diff --git a/src/sqlancer/mysql/MySQLErrors.java b/src/sqlancer/mysql/MySQLErrors.java
@@ -53,6 +53,7 @@ public static List<String> getInsertUpdateErrors() {
         errors.add("Data truncated for functional index");
         errors.add("cannot be null");
         errors.add("Incorrect decimal value");
+        errors.add("The value specified for generated column");
 
         return errors;
     }

diff --git a/src/sqlancer/mysql/MySQLOracleFactory.java b/src/sqlancer/mysql/MySQLOracleFactory.java
@@ -10,6 +10,7 @@
 import sqlancer.common.query.ExpectedErrors;
 import sqlancer.common.query.SQLancerResultSet;
 import sqlancer.mysql.gen.MySQLExpressionGenerator;
+import sqlancer.mysql.oracle.MySQLDQEOracle;
 import sqlancer.mysql.oracle.MySQLDQPOracle;
 import sqlancer.mysql.oracle.MySQLFuzzer;
 import sqlancer.mysql.oracle.MySQLPivotedQuerySynthesisOracle;
@@ -75,5 +76,11 @@ public TestOracle<MySQLGlobalState> create(MySQLGlobalState globalState) throws
         public TestOracle<MySQLGlobalState> create(MySQLGlobalState globalState) throws SQLException {
             return new MySQLDQPOracle(globalState);
         }
+    },
+    DQE {
+        @Override
+        public TestOracle<MySQLGlobalState> create(MySQLGlobalState globalState) throws SQLException {
+            return new MySQLDQEOracle(globalState);
+        }
     };
 }