Great start! For test case 2, do you think we could still report it and then disable its generation until the bug is resolved, despite being deprecated? It is a bug after all. If you prefer not to, that's also fine.

For test case 3, I guess you could go ahead and report it?

For test case 4, disabling indices altogether would significantly restrict the search space as you pointed out. Perhaps, could we instead insert only integer values or so for now?

Hi Manuel, I have pushed updates to this branch and updated the PR description. This PR is now ready for full review and merging once any remaining issues have been addressed. I have organised it as multiple commits which can be squashed as desired.

Regarding your comments:

• For test case 2, I did further reading and found that the behaviour is expected (see update to the PR description), so I will not be reporting this as a bug. I have made adjustments so that the oracles that were flagging the behaviour as an error are no longer affected.
• For test case 4, in the end I have achieved an even tighter workaround than the one you suggested, by specifically avoiding the insertion of numbers in the range [0.5,1.5) into INT columns.
• I have also achieved a tighter workaround for test case 3 (avoid insertion of numbers that round to 0 into DECIMAL columns).

It seems that the general tests are failing due to a formatting error. You can run mvn -B verify -DskipTests=true locally to check and then fix the formatting using mvn formatter:format.

I rebased to remove the initial edits to .settings/org.eclipse.jdt.core.prefs, did a new commit to add the loop counter, and reformatted to pass the general tests

I will investigate why the MySQL CI is failing

After my latest commit, the MySQL CI is now passing. Summary of the failing test cases identified and fixed:

Test case 7

Reduced input (indicative):

CREATE TABLE t0(c0 DECIMAL ZEROFILL UNIQUE);
INSERT INTO t0(c0) VALUES(NULL), (NULL), (1284856216), ...;
SELECT rowId FROM t0 WHERE <cond> ORDER BY t0.c0 LIMIT 7;
UPDATE t0 SET updated=1 WHERE <cond> ORDER BY t0.c0 LIMIT 7;
DELETE FROM t0 WHERE <cond> ORDER BY t0.c0 LIMIT 7;

Error:

SELECT accessed different rows from UPDATE. UPDATE accessed different rows from DELETE.

Conclusion:

This is not a DBMS bug. When ORDER BY contains columns with duplicate values (here, many NULLs in c0) and LIMIT is applied, the tie-breaking between equally-sorted rows is non-deterministic in MySQL. The SELECT and UPDATE may traverse rows in a different physical order, selecting a different subset of NULLs. The fix is to append the rowId auxiliary column (unique per row) to the end of every generated ORDER BY clause, making the sort fully deterministic across all three statements.

Test case 8

Reduced input (indicative):

CREATE TABLE t0(c0 DECIMAL);
INSERT INTO t0(c0) VALUES(...);
SELECT rowId FROM t0 WHERE (t0.c0) NOT IN ("yS", NULL, ...);
UPDATE t0 SET updated=1 WHERE (t0.c0) NOT IN ("yS", NULL, ...);
DELETE FROM t0 WHERE (t0.c0) NOT IN ("yS", NULL, ...);

Related errors:

• SELECT has different errors from UPDATE — UPDATE/DELETE raise 1292 (ERROR) + 1366 (WARNING) from type coercion, SELECT does not.
• SELECT has errors, but UPDATE does not — the mirror case: SELECT raises 1292 (WARNING) from the same coercion, UPDATE/DELETE do not.
• UPDATE accessed non-empty rows when specific errors happen — a false positive introduced by an initial overly-broad fix that added 1292/1366 to getUpdateSpecificErrorCodes().

Conclusion:

This is not a DBMS bug. MySQL evaluates type-incompatible literals in WHERE clauses (e.g. string "yS" against a DECIMAL column) differently depending on statement type: SELECT may short-circuit on a NULL in the IN list and not coerce the literal at all, while UPDATE/DELETE evaluate it against each row raising 1292 and 1366. The reverse can also occur. In all cases the accessed rows are identical across the three statements.

The initial fix of adding 1292 and 1366 to getUpdateSpecificErrorCodes() was wrong: those codes can appear in all three statements consistently (with rows accessed), causing a false positive. The correct fix introduces a helper isKnownSelectDMLDiscrepancy that skips these codes only in the "DML errors must appear in SELECT errors" check, leaving the row comparison intact. The same helper is also applied in the updateResult.hasEmptyErrors() / deleteResult.hasEmptyErrors() branches to handle the SELECT-raises-but-DML-doesn't direction.

Test case 9

Reduced input (indicative):

CREATE TABLE t0(c0 INT, c1 DECIMAL UNSIGNED ZEROFILL);
CREATE INDEX i2 ON t0(LEAST(t0.c1, ...), (... XOR ...));
INSERT INTO t0 VALUES(...);
SELECT rowId FROM t0 WHERE <cond>;
UPDATE t0 SET updated=1 WHERE <cond>;
DELETE FROM t0 WHERE <cond>;

Error:

SELECT has different errors from UPDATE. SELECT has different errors from DELETE.

Conclusion:

This is not a DBMS bug. The table has a functional index whose expression uses && (deprecated AND syntax). UPDATE and DELETE must maintain this index during writes, which triggers error 1030 (Got error 1 - 'Operation not permitted' from storage engine) and error 3751 (Data truncated for functional index). SELECT is read-only and never writes to the index, so it cannot produce these errors. Both 1030 and 3751 are added to isKnownSelectDMLDiscrepancy.

Test case 10

Reduced input (indicative):

CREATE TABLE t0(c0 MEDIUMTEXT);
CREATE INDEX i72 ON t0(c0(4));
INSERT INTO t0(c0) VALUES(...);
SELECT rowId FROM t0 WHERE t0.c0;
UPDATE t0 SET updated=1 WHERE t0.c0;
DELETE FROM t0 WHERE t0.c0;

Error:

SELECT has different errors from UPDATE. SELECT has different errors from DELETE.

Conclusion:

This is not a DBMS bug. MySQL's range optimiser has a configurable memory budget (range_optimizer_max_mem_size). When the budget is exceeded during DML, MySQL raises warning 3170 (Memory capacity exceeded. Range optimization was not done) and falls back to a full table scan. The same budget may not be exceeded during SELECT (which has a lighter memory footprint). The fallback plan still evaluates the WHERE predicate correctly, so all three statements access the same rows. Error 3170 is added to isKnownSelectDMLDiscrepancy.

Thanks a lot! Could you please take a look at the small nitpick? After that, we can merge the PR.

I have made the improvement in MySQLDQEOracle by refactoring into an enum. Also discovered an unrelated issue with MySQLIndexGenerator which was rare enough to have not surfaced until now, and fixed that (detailed below). CI is passing again (apart from the tests for some other DBMSs, which were already failing before this PR).

Test case 11

Reduced input (indicative):

CREATE TABLE t0(c0 VARCHAR(500)) ENGINE = InnoDB PARTITION BY KEY (c0);
CREATE INDEX i1 USING BTREE ON t0(c0(3)) VISIBLE;

Error:

java.sql.SQLException: Column 'pqsdb0.t0.c0' having prefix key part 'c0(3)' in the PARTITION BY KEY() clause is not supported.

Conclusion:

This is not a DBMS bug. MySQL does not permit prefix key parts (e.g. c0(3)) on columns that participate in a PARTITION BY KEY() clause. MySQLIndexGenerator generates prefix lengths for VARCHAR columns without checking whether the target table is partitioned by key on that column, and this error was not included in ExpectedErrors. The fix is to add "in the PARTITION BY KEY() clause is not supported" to the expected errors list in MySQLIndexGenerator.create().