sqlancer · mrigger · Aug 8, 2026 · Jul 29, 2026 · Jul 29, 2026 · Jul 29, 2026
diff --git a/src/sqlancer/common/gen/EETDMLGenerator.java b/src/sqlancer/common/gen/EETDMLGenerator.java
@@ -0,0 +1,189 @@
+package sqlancer.common.gen;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import sqlancer.common.ast.newast.Expression;
+import sqlancer.common.oracle.EETTransformer;
+import sqlancer.common.schema.AbstractTable;
+import sqlancer.common.schema.AbstractTableColumn;
+import sqlancer.common.schema.AbstractTables;
+
+/**
+ * Generator interface used by {@link sqlancer.common.oracle.EETDMLOracle}, the DML counterpart of {@link EETGenerator}.
+ * It supplies methods which generate the transformable expressions, create the DBMS-specific {@link EETTransformer}
+ * that rewrites them, and produce the SQL of the statements the oracle uses to observe the database state a statement
+ * produces (an approach drawn from the DQE oracle).
+ *
+ * <p>
+ * Adapted from the DQE oracle, state is observed with an auxiliary column ({@link EETDMLGenerator#ROW_ID_COLUMN}) which
+ * uniquely identifies each row. The rows are stamped with identifiers once, before both executions of the statement run
+ * (each in a rolled-back transaction), so both executions observe the same identifiers regardless of how they are
+ * produced.
+ *
+ * <p>
+ * Most of these statements are standard SQL, likely common to most DBMSs, so are provided as {@code default} methods.
+ *
+ * @param <E>
+ *            the DBMS-specific expression class
+ * @param <T>
+ *            the DBMS-specific table class
+ * @param <C>
+ *            the DBMS-specific column class
+ */
+public interface EETDMLGenerator<E extends Expression<C>, T extends AbstractTable<C, ?, ?>, C extends AbstractTableColumn<?, ?>> {
+
+    /** Name of the auxiliary column that uniquely identifies each row. */
+    String ROW_ID_COLUMN = "rowid";
+
+    /**
+     * Restricts this generator to the given tables (a single table, for the DML statement under test) and their
+     * columns.
+     *
+     * @param tables
+     *            the tables (and, implicitly, columns) the generated statement operates on
+     *
+     * @return this generator
+     */
+    EETDMLGenerator<E, T, C> setTablesAndColumns(AbstractTables<T, C> tables);
+
+    /**
+     * Generates a fresh random boolean expression over the current tables' columns, used as the DML statement's WHERE
+     * predicate.
+     *
+     * @return a fresh random boolean expression
+     */
+    E generateBooleanExpression();
+
+    /**
+     * Creates a DBMS-specific {@link EETTransformer} backed by this generator, used to rewrite the statement's
+     * expressions into semantically equivalent ones.
+     *
+     * @return a DBMS-specific {@link EETTransformer}
+     */
+    EETTransformer<E, ?> createTransformer();
+
+    // --- DBMS-specific primitives ---
+
+    /**
+     * Renders an expression to its DBMS-specific SQL string.
+     *
+     * @param expr
+     *            the expression to render
+     *
+     * @return the SQL text of {@code expr}
+     */
+    String asString(E expr);
+
+    /**
+     * SQL that assigns every existing row of {@code table} a distinct, stable identifier in the {@link #ROW_ID_COLUMN}
+     * column. For example, a 36-character UUID string.
+     *
+     * @param table
+     *            the table whose rows are stamped
+     *
+     * @return the SQL statement
+     */
+    String stampRowIdsStatement(T table);
+
+    /**
+     * The SQL type of the auxiliary {@link #ROW_ID_COLUMN} column. It must be able to hold the identifiers that
+     * {@link #stampRowIdsStatement} produces, so it belongs with that statement as the other half of the row-id
+     * representation. For example, {@code VARCHAR(36)} would fit a 36-character UUID string.
+     *
+     * @return the column type
+     */
+    String rowIdColumnType();
+
+    // --- Standard-SQL statements (override only where the DBMS's dialect differs) ---
+
+    /**
+     * SQL that adds the auxiliary {@link #ROW_ID_COLUMN} column to {@code table}, typed as {@link #rowIdColumnType}.
+     *
+     * @param table
+     *            the table to add the column to
+     *
+     * @return the SQL statement
+     */
+    default String addRowIdColumnStatement(T table) {
+        return "ALTER TABLE " + table.getName() + " ADD COLUMN " + ROW_ID_COLUMN + " " + rowIdColumnType();
+    }
+
+    /**
+     * SQL that drops the auxiliary {@link #ROW_ID_COLUMN} column from {@code table}.
+     *
+     * @param table
+     *            the table to drop the column from
+     *
+     * @return the SQL statement
+     */
+    default String dropRowIdColumnStatement(T table) {
+        return "ALTER TABLE " + table.getName() + " DROP COLUMN " + ROW_ID_COLUMN;
+    }
+
+    /**
+     * SQL that selects the {@link #ROW_ID_COLUMN} of every row of {@code table} (the surviving-row snapshot).
+     *
+     * @param table
+     *            the table to snapshot
+     *
+     * @return the SQL statement; its first result column must be the identifiers
+     */
+    default String selectRowIdsStatement(T table) {
+        return "SELECT " + ROW_ID_COLUMN + " FROM " + table.getName();
+    }
+
+    /**
+     * SQL that deletes the rows of {@code table} matching {@code predicate}, optionally limited to the first
+     * {@code limit} rows.
+     *
+     * <p>
+     * When {@code limit} is non-null, the statement is ordered by {@code orderByColumns} followed by
+     * {@link #ROW_ID_COLUMN} as a tiebreaker. Because the identifiers are unique, this is always a total order (even
+     * when the ordering columns tie), so the "first {@code limit}" rows are identical for the original and transformed
+     * statements. Varying the ordering columns exercises more access paths than the row id alone would. The caller must
+     * pass the same {@code orderByColumns} and {@code limit} to both statements; neither is transformed.
+     *
+     * @param table
+     *            the table to delete from
+     * @param predicate
+     *            the WHERE predicate; rendered via {@link #asString}
+     * @param orderByColumns
+     *            the columns to order by before the row-id tiebreaker (may be empty); only used when {@code limit} is
+     *            non-null
+     * @param limit
+     *            the maximum number of rows to delete, or {@code null} for no limit
+     *
+     * @return the SQL statement
+     */
+    default String deleteStatement(T table, E predicate, List<C> orderByColumns, Integer limit) {
+        String statement = "DELETE FROM " + table.getName() + " WHERE " + asString(predicate);
+        if (limit != null) {
+            List<String> orderBy = new ArrayList<>();
+            for (C column : orderByColumns) {
+                orderBy.add(column.getName());
+            }
+            orderBy.add(ROW_ID_COLUMN); // unique tiebreaker: guarantees a total order regardless of the columns above
+            statement += " ORDER BY " + String.join(", ", orderBy) + " LIMIT " + limit;
+        }
+        return statement;
+    }
+
+    /**
+     * SQL that starts a transaction, so a statement's effect can be observed and then undone.
+     *
+     * @return the SQL statement
+     */
+    default String beginTransactionStatement() {
+        return "BEGIN";
+    }
+
+    /**
+     * SQL that rolls the current transaction back, undoing the statement's effect.
+     *
+     * @return the SQL statement
+     */
+    default String rollbackTransactionStatement() {
+        return "ROLLBACK";
+    }
+}
diff --git a/src/sqlancer/common/oracle/EETDMLOracle.java b/src/sqlancer/common/oracle/EETDMLOracle.java
@@ -0,0 +1,173 @@
+package sqlancer.common.oracle;
+
+import java.sql.SQLException;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+import sqlancer.ComparatorHelper;
+import sqlancer.IgnoreMeException;
+import sqlancer.Randomly;
+import sqlancer.SQLGlobalState;
+import sqlancer.common.ast.newast.Expression;
+import sqlancer.common.gen.EETDMLGenerator;
+import sqlancer.common.query.ExpectedErrors;
+import sqlancer.common.query.SQLQueryAdapter;
+import sqlancer.common.schema.AbstractSchema;
+import sqlancer.common.schema.AbstractTable;
+import sqlancer.common.schema.AbstractTableColumn;
+import sqlancer.common.schema.AbstractTables;
+
+/**
+ * EET (Equivalent Expression Transformation) oracle for DML statements, based on "Detecting Logic Bugs in Database
+ * Engines via Equivalent Expression Transformation" (Jiang &amp; Su, OSDI'24).
+ *
+ * <p>
+ * Whereas {@link EETOracle} transforms a SELECT and compares the two result sets, this oracle transforms a DML
+ * statement and compares the two database states produced.
+ *
+ * <p>
+ * Adapted from the DQE oracle, state is observed with an auxiliary column ({@link EETDMLGenerator#ROW_ID_COLUMN}) which
+ * uniquely identifies each row, and each statement is executed inside a transaction that is rolled back, so the two
+ * statements can be compared against the same starting state without permanently modifying the database. For a DELETE,
+ * the state is captured as the set of surviving row identifiers. Because rolling back a statement requires a
+ * transactional storage engine, the DBMS-specific setup must ensure only such engines are used while this oracle is
+ * active.
+ *
+ * <p>
+ * Only DELETE is currently supported. Statement reduction is not yet implemented (there is no
+ * {@link sqlancer.Reproducer Reproducer}), so the finding is reported without database reduction.
+ *
+ * @param <E>
+ *            the DBMS-specific expression class
+ * @param <S>
+ *            the DBMS-specific schema class
+ * @param <T>
+ *            the DBMS-specific table class
+ * @param <C>
+ *            the DBMS-specific column class
+ * @param <G>
+ *            the DBMS-specific global state class
+ */
+public class EETDMLOracle<E extends Expression<C>, S extends AbstractSchema<?, T>, T extends AbstractTable<C, ?, ?>, C extends AbstractTableColumn<?, ?>, G extends SQLGlobalState<?, S>>
+        implements TestOracle<G> {
+
+    private final G state;
+    private EETDMLGenerator<E, T, C> gen;
+    private final EETTransformer<E, ?> transformer;
+    private final ExpectedErrors errors;
+
+    private String generatedQueryString;
+
+    public EETDMLOracle(G state, EETDMLGenerator<E, T, C> gen, ExpectedErrors expectedErrors) {
+        if (state == null || gen == null || expectedErrors == null) {
+            throw new IllegalArgumentException("Null variables used to initialize test oracle.");
+        }
+        this.state = state;
+        this.gen = gen;
+        this.transformer = gen.createTransformer();
+        this.errors = expectedErrors;
+    }
+
+    @Override
+    public void check() throws SQLException {
+        List<T> tables = state.getSchema().getDatabaseTables();
+        if (tables.isEmpty()) {
+            throw new IgnoreMeException();
+        }
+        // DELETE targets a single table, so operate on exactly one; confining the generator to it keeps the predicate
+        // from referencing another table's columns (which would render invalid single-table DML).
+        T table = Randomly.fromList(tables);
+        gen = gen.setTablesAndColumns(new AbstractTables<>(List.of(table)));
+
+        E predicate = gen.generateBooleanExpression();
+        // The WHERE predicate is evaluated in a boolean context.
+        E transformedPredicate = transformer.transform(predicate, true);
+
+        // Optionally cap the DELETE with a LIMIT. The limit and its ordering (a random column subset, made a total
+        // order by the row-id tiebreaker) are decided once and applied identically to both statements, so the capped
+        // row set is deterministic and equal across the runs while still exercising varied orderings.
+        Integer limit = null;
+        List<C> orderByColumns = List.of();
+        if (Randomly.getBoolean()) {
+            limit = (int) Randomly.getNotCachedInteger(0, 10);
+            orderByColumns = Randomly.subset(table.getColumns());
+        }
+        String originalDelete = gen.deleteStatement(table, predicate, orderByColumns, limit);
+        String transformedDelete = gen.deleteStatement(table, transformedPredicate, orderByColumns, limit);
+        generatedQueryString = originalDelete;
+
+        // Add the auxiliary column outside the try, then guard everything after it with the finally that drops it:
+        // the ALTER auto-commits (it is not undone by ROLLBACK), so a failure between adding and dropping would leak
+        // the column into the next iteration and cause cascading duplicate-column failures. The row-identity setup
+        // touches rows, so — like the DELETE itself — it can raise tolerated errors (e.g. functional-index maintenance
+        // truncation); such an error aborts the iteration (IgnoreMeException) rather than being reported as a bug.
+        if (!new SQLQueryAdapter(gen.addRowIdColumnStatement(table), errors, true).execute(state)) {
+            throw new IgnoreMeException();
+        }
+        try {
+            // Stamp identifiers once, in autocommit mode, before both DELETEs run: both then observe the same rows.
+            if (!new SQLQueryAdapter(gen.stampRowIdsStatement(table), errors).execute(state)) {
+                throw new IgnoreMeException();
+            }
+
+            Set<String> originalSurvivors = executeDeleteAndSnapshot(table, originalDelete);
+            Set<String> transformedSurvivors = executeDeleteAndSnapshot(table, transformedDelete);
+
+            if (!originalSurvivors.equals(transformedSurvivors)) {
+                throw new AssertionError(
+                        mismatchMessage(originalDelete, transformedDelete, originalSurvivors, transformedSurvivors));
+            }
+        } finally {
+            new SQLQueryAdapter(gen.dropRowIdColumnStatement(table), errors, true).execute(state);
+        }
+    }
+
+    /**
+     * Executes {@code deleteStatement} inside a transaction that is always rolled back, and returns the set of row
+     * identifiers surviving the DELETE (the resulting database state). A DBMS error expected by the oracle aborts the
+     * whole check ({@link IgnoreMeException}) rather than being reported, matching {@link EETOracle}'s handling; an
+     * unexpected error surfaces as a bug ({@link AssertionError}, thrown by the query adapter).
+     *
+     * @param table
+     *            the table being deleted from
+     * @param deleteStatement
+     *            the DELETE statement to execute
+     *
+     * @return the set of row identifiers surviving the DELETE
+     *
+     * @throws SQLException
+     *             if a DBMS interaction fails
+     */
+    private Set<String> executeDeleteAndSnapshot(T table, String deleteStatement) throws SQLException {
+        new SQLQueryAdapter(gen.beginTransactionStatement()).execute(state);
+        try {
+            // execute reports (throws AssertionError for) unexpected errors and returns false for expected ones.
+            boolean succeeded = new SQLQueryAdapter(deleteStatement, errors).execute(state);
+            if (!succeeded) {
+                // The DELETE hit an error the oracle tolerates; do not compare states (as EETOracle does for SELECT).
+                throw new IgnoreMeException();
+            }
+            return new HashSet<>(
+                    ComparatorHelper.getResultSetFirstColumnAsString(gen.selectRowIdsStatement(table), errors, state));
+        } finally {
+            new SQLQueryAdapter(gen.rollbackTransactionStatement()).execute(state);
+        }
+    }
+
+    private static String mismatchMessage(String originalDelete, String transformedDelete,
+            Set<String> originalSurvivors, Set<String> transformedSurvivors) {
+        return new StringBuilder()
+                .append("-- The original and transformed DELETE statements left the database in different states")
+                .append(" (different sets of surviving rows):").append(System.lineSeparator()).append("-- original (")
+                .append(originalSurvivors.size()).append(" rows survive): ").append(originalDelete).append(';')
+                .append(System.lineSeparator()).append("-- transformed (").append(transformedSurvivors.size())
+                .append(" rows survive): ").append(transformedDelete).append(';').append(System.lineSeparator())
+                .toString();
+    }
+
+    @Override
+    public String getLastQueryString() {
+        return generatedQueryString;
+    }
+}
diff --git a/src/sqlancer/mysql/MySQLErrors.java b/src/sqlancer/mysql/MySQLErrors.java
@@ -65,4 +65,21 @@ public static void addInsertUpdateErrors(ExpectedErrors errors) {
         errors.addAll(getInsertUpdateErrors());
     }
 
+    public static List<String> getDMLErrors() {
+        ArrayList<String> errors = new ArrayList<>(getInsertUpdateErrors());
+
+        // WHERE-clause type coercion (e.g. string -> number) is only a warning in SELECT but a hard error in
+        // DELETE/UPDATE under strict sql_mode (MySQL 1292). A semantics-preserving transform may benignly change
+        // whether it fires, so it is tolerated rather than flagged.
+        errors.add("Truncated incorrect");
+        // Foreign key constraint failure when deleting/updating a referenced row.
+        errors.add("a foreign key constraint fails");
+
+        return errors;
+    }
+
+    public static void addDMLErrors(ExpectedErrors errors) {
+        errors.addAll(getDMLErrors());
+    }
+
 }
diff --git a/src/sqlancer/mysql/MySQLGlobalState.java b/src/sqlancer/mysql/MySQLGlobalState.java
@@ -16,4 +16,13 @@ public boolean usesPQS() {
         return getDbmsSpecificOptions().oracles.stream().anyMatch(o -> o == MySQLOracleFactory.PQS);
     }
 
+    public boolean usesEET() {
+        return getDbmsSpecificOptions().getTestOracleFactory().stream()
+                .anyMatch(o -> o == MySQLOracleFactory.EET || o == MySQLOracleFactory.EET_DML);
+    }
+
+    public boolean usesEETDML() {
+        return getDbmsSpecificOptions().getTestOracleFactory().stream().anyMatch(o -> o == MySQLOracleFactory.EET_DML);
+    }
+
 }