Feat: Integrating DQE Testing Approaches into SQLancer first in MySQL #1251

luliqwerty · 2025-06-29T11:55:04Z

This PR is to Integrating DQE (Differential Query Execution) Testing Approaches into SQLancer. More information see DQE paper.

Take MySQL as the first step.
The code is divided into three main parts:

DQEBase.class: defines the core logic of DQE, including the addition and deletion of auxiliary columns, and the comparison of query results.
SQLQueryError.java: provide standardized error representation and comparison functionality for SQL query operations in DQE.
MySQLDQEOracle.class: Inherits from DQEBase and implements the specific logic of executing DQE in MySQL, including generating queries, executing queries, comparing results, etc.

mrigger · 2025-06-30T14:33:57Z

Thanks, that's great! Some high-level comments first:

for the SQLQueryError, this looks quite similar to ExpectedErrors. Do we really need this class or can we combine these classes?
for statements like UPDATE, DELETE, and ALTER TABLE, do you think we can use and extend the generator classes rather than implementing the logic in the DQE oracle?

luliqwerty · 2025-07-01T02:36:39Z

Thanks, that's great! Some high-level comments first:

for the SQLQueryError, this looks quite similar to ExpectedErrors. Do we really need this class or can we combine these classes?

for statements like UPDATE, DELETE, and ALTER TABLE, do you think we can use and extend the generator classes rather than implementing the logic in the DQE oracle?

Regarding the SQLQueryError and ExpectedErrors classes, while they do share some surface-level similarities, I think it's important to highlight that their purposes and usage patterns are quite different in practice:

SQLQueryError is specifically designed to represent actual errors returned by the database, with structured fields like error level, code, and message. It plays a crucial role in DQE logic to distinguish between different types of errors returned by statements such as UPDATE, SELECT, or DELETE.
On the other hand, ExpectedErrors serves as a declarative mechanism for defining acceptable errors during testing, primarily relying on substring or regex-based matching. It does not require the same level of structural detail as SQLQueryError.

Given these differences, merging them into a single class could lead to confusion, increased coupling, and reduced clarity in terms of responsibility separation. I believe keeping them separate helps maintain a clean architecture and supports better extensibility in the future.

That said, if you have any alternative suggestions or design ideas on how we can better organize this part of the code, I’d be more than happy to discuss them further. Thanks again for your thoughtful feedback! @mrigger @JensonSung

JensonSung · 2025-07-01T02:43:02Z

I think current implementation is good but specialized for DQE method.

JensonSung · 2025-07-01T02:50:28Z

Thanks, that's great! Some high-level comments first:

for the SQLQueryError, this looks quite similar to ExpectedErrors. Do we really need this class or can we combine these classes?

for statements like UPDATE, DELETE, and ALTER TABLE, do you think we can use and extend the generator classes rather than implementing the logic in the DQE oracle?

First, revising ExepctedErrors like SQLQueryError to provide a structured information may help ease to identify errors compared to a string-formed information. While DQE does not distinguish which error is expected but compare whether errors are consistent. A clear name could be required.
Second, it seems that every kind of statements like update and delete deserve a proper generator, preparing for new testing methods.
These two improvements can be put on the agenda.

src/sqlancer/common/query/SQLQueryAdapter.java

src/sqlancer/common/query/SQLQueryError.java

mrigger · 2025-07-06T03:06:55Z

src/sqlancer/mysql/oracle/MySQLDQEOracle.java

+        boolean generateLimit = false;
+        int limit = 0;
+        if (operateOnSingleTable) {
+            if (Randomly.getBooleanWithSmallProbability()) {


I think the expression generator already has methods for generating ORDER BYs, so we should try to re-use those. Could we perhaps also re-use or factor out components from the random query generator?

I have add some tests for your mentioned method generateOrderBys() but I got some unexpected errors my repo commit. Sometimes test can't pass with many errors, sometimes this method generated statement like EXISTS (SELECT 1 WHERE FALSE) DESC, which never return rows in SQL statement. I don't know if the methed have some logic errors, or maybe my test have some wrongs, can you give me some suggestions? Thank you very much.

Maybe only generating columns in order by expressions is enough for applying DQE, which can identify the correctness of complex expressions through WHERE clauses.

src/sqlancer/mysql/oracle/MySQLDQEOracle.java

mrigger · 2025-07-06T03:10:35Z

src/sqlancer/mysql/oracle/MySQLDQEOracle.java

+                String selectRowId = String.format("SELECT %s FROM %s", rowId, tableName);
+                SQLancerResultSet resultSet = new SQLQueryAdapter(selectRowId).executeAndGet(state);
+                HashSet<String> rows = new HashSet<>();
+                if (resultSet != null) {


I think we have some auxiliary methods for fetching rows (used by other test oracles). Can we perhaps re-use any of these here?

@luliqwerty Try to find if we can re-use some auxiliary methods for fetching rows in other test oracles.

Hello @mrigger @JensonSung
I can't find a suitable method to get rows you mentioned. The confusing method is CERTOracle.getRow(), which is used to get the count of rows. Can you give me some advice or maybe we can just use the way I used to do.

I think there could be one in https://github.com/sqlancer/sqlancer/blob/main/src/sqlancer/ComparatorHelper.java. If not, we could stick to the current implementation.

Current implementation can reuse the method getResultSetFirstColumnAsString.

src/sqlancer/common/oracle/DQEBase.java

src/sqlancer/mysql/oracle/MySQLDQEOracle.java

mrigger · 2025-07-06T03:18:29Z

Given these differences, merging them into a single class could lead to confusion, increased coupling, and reduced clarity in terms of responsibility separation. I believe keeping them separate helps maintain a clean architecture and supports better extensibility in the future.

Okay, then let's leave them separate for now. I guess there is a conceptual commonality (both are about errors returned by the database system), but if that does not (yet) make sense at an implementation level, we could try to address this later.

mrigger

This looks close to being merge ready. Could you have another look at my comments? @JensonSung, perhaps you could also do another review then?

src/sqlancer/common/query/SQLancerResultSet.java

src/sqlancer/mysql/oracle/MySQLDQEOracle.java

mrigger · 2025-08-07T15:14:40Z

src/sqlancer/mysql/oracle/MySQLDQEOracle.java

+                String selectRowId = String.format("SELECT %s FROM %s", rowId, tableName);
+                SQLancerResultSet resultSet = new SQLQueryAdapter(selectRowId).executeAndGet(state);
+                HashSet<String> rows = new HashSet<>();
+                if (resultSet != null) {


I think there could be one in https://github.com/sqlancer/sqlancer/blob/main/src/sqlancer/ComparatorHelper.java. If not, we could stick to the current implementation.

mrigger · 2025-08-07T15:16:23Z

src/sqlancer/mysql/oracle/MySQLDQEOracle.java

+                String rowId = tableName + "." + COLUMN_ROWID;
+                String selectRowId = String.format("SELECT %s FROM %s", rowId, tableName);
+                SQLancerResultSet resultSet = new SQLQueryAdapter(selectRowId).executeAndGet(state, false);
+                HashSet<String> rows = new HashSet<>();


Some of the code seems identical to the one above for the update case. Can we factor those out?

Yes, some methods could be extracted for fetching these helper columns, like fetching rowID. @luliqwerty

@mrigger you are right, thanks a lot, I've reused the existing method.

JensonSung · 2025-08-08T02:37:21Z

Sorry for possible confusing. @sayJason is my another account. I try to use the same one. Any problem, @JensonSung

mrigger

Just did another quick and noticed some final small requests. Could you please have a last look at them before we merge the PR? Other than that, @JensonSung, do the changes look good to you?

Excited to finally have DQE supported in SQLancer! Thanks a lot, @luliqwerty!

src/sqlancer/common/oracle/DQEBase.java

src/sqlancer/mysql/oracle/MySQLDQEOracle.java

- DQEBase: remove unnecessary method - MySQLDQEOracle: simplify adding expectedErrors - MySQLErrors: add a expectedErrors

JensonSung · 2025-08-11T14:04:02Z

Good jobs @luliqwerty . LGTM. Happy to merge @mrigger .

mrigger · 2025-08-11T16:44:19Z

Agreed, lgtm, thanks a lot!

mrigger · 2025-08-11T16:45:54Z

I just noticed that when executing the tests, there seems to be an issue:

[INFO] -------------------------------------------------------
[INFO]  T E S T S
[INFO] -------------------------------------------------------
[INFO] Running sqlancer.dbms.TestMySQLDQE
java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)

Could you check whether you can reproduce this locally? This subsequently also results in a crash.

luliqwerty · 2025-08-13T12:12:47Z

I just noticed that when executing the tests, there seems to be an issue:

[INFO] -------------------------------------------------------
[INFO]  T E S T S
[INFO] -------------------------------------------------------
[INFO] Running sqlancer.dbms.TestMySQLDQE
java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)

Could you check whether you can reproduce this locally? This subsequently also results in a crash.

This is an unexpected error, as the CI for the merge commit passed. The error indicates that the code is attempting to execute an SQL operation on a database connection that has already been closed. However, it seems that DQE did not perform this action. @mrigger @JensonSung

JensonSung · 2025-08-13T12:31:33Z

I just noticed that when executing the tests, there seems to be an issue:
[INFO] -------------------------------------------------------
[INFO]  T E S T S
[INFO] -------------------------------------------------------
[INFO] Running sqlancer.dbms.TestMySQLDQE
java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
Could you check whether you can reproduce this locally? This subsequently also results in a crash.
This is an unexpected error, as the CI for the merge commit passed. The error indicates that the code is attempting to execute an SQL operation on a database connection that has already been closed. However, it seems that DQE did not perform this action. @mrigger @JensonSung

It seems to be a crash bug. I run DQE locally in SQLancer, which executes without such errors.

mrigger · 2025-08-14T09:06:56Z

Could it be that the local version of MySQL and the one in the CI are different? If they are the same, perhaps it could be helpful to run for DQE a bit longer. It could be that it triggered a crash bug, as pointed out by @JensonSung.

mrigger · 2025-08-14T09:07:42Z

Btw, it would also be great to add DQE as part of the documentation of SQLancer in the table of the main README.md.

JensonSung · 2025-08-22T09:31:23Z

Could it be that the local version of MySQL and the one in the CI are different? If they are the same, perhaps it could be helpful to run for DQE a bit longer. It could be that it triggered a crash bug, as pointed out by @JensonSung.

Sorry for the late message. The local MySQL has a different version. I try a bit longer time to run DQE, which runs well and does not report errors.

luliqwerty deleted the branch sqlancer:main July 4, 2025 12:56

luliqwerty closed this Jul 4, 2025

luliqwerty deleted the main branch July 4, 2025 12:56

luliqwerty restored the main branch July 4, 2025 12:57

luliqwerty reopened this Jul 4, 2025

mrigger reviewed Jul 6, 2025

View reviewed changes

src/sqlancer/common/query/SQLQueryAdapter.java Show resolved Hide resolved

mrigger reviewed Jul 6, 2025

View reviewed changes

src/sqlancer/common/query/SQLQueryError.java Show resolved Hide resolved

mrigger reviewed Jul 6, 2025

View reviewed changes

src/sqlancer/mysql/oracle/MySQLDQEOracle.java Outdated Show resolved Hide resolved

mrigger reviewed Jul 6, 2025

View reviewed changes

src/sqlancer/common/oracle/DQEBase.java Outdated Show resolved Hide resolved

mrigger reviewed Jul 6, 2025

View reviewed changes

src/sqlancer/mysql/oracle/MySQLDQEOracle.java Outdated Show resolved Hide resolved

luliqwerty mentioned this pull request Jul 9, 2025

Add the SQLQueryError class for DQE and its test #1257

Merged

luliqwerty force-pushed the main branch from 73507fe to 470a5d7 Compare August 3, 2025 10:10

Introduce DQE for MySQL in SQLancer and add test to workflow

6c8166b

luliqwerty force-pushed the main branch from 470a5d7 to 6c8166b Compare August 3, 2025 11:35

mrigger reviewed Aug 7, 2025

View reviewed changes

luliqwert added 3 commits August 8, 2025 14:01

Fix: remove MySQL specific logic and reuse existing method

1f64426

refactor(MySQLDQEOracle): extract duplicated code as method

83cf899

refactor(MySQLDQEOracle): run mvn formatter:format

e1b5861

mrigger reviewed Aug 10, 2025

View reviewed changes

refactor: fix some code style

131febb

- DQEBase: remove unnecessary method - MySQLDQEOracle: simplify adding expectedErrors - MySQLErrors: add a expectedErrors

mrigger merged commit 061a337 into sqlancer:main Aug 11, 2025
13 of 26 checks passed

luliqwerty mentioned this pull request Sep 2, 2025

feat(DQE): Add Extensibility to DQE #1272

Merged

Feat: Integrating DQE Testing Approaches into SQLancer first in MySQL #1251

Feat: Integrating DQE Testing Approaches into SQLancer first in MySQL #1251

Uh oh!

Conversation

luliqwerty commented Jun 29, 2025

Uh oh!

mrigger commented Jun 30, 2025

Uh oh!

luliqwerty commented Jul 1, 2025

Uh oh!

JensonSung commented Jul 1, 2025

Uh oh!

JensonSung commented Jul 1, 2025

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

mrigger commented Jul 6, 2025

Uh oh!

mrigger left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

JensonSung commented Aug 8, 2025

Uh oh!

mrigger left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

JensonSung commented Aug 11, 2025

Uh oh!

mrigger commented Aug 11, 2025

Uh oh!

Uh oh!

mrigger commented Aug 11, 2025

Uh oh!

luliqwerty commented Aug 13, 2025

Uh oh!

JensonSung commented Aug 13, 2025

Uh oh!

mrigger commented Aug 14, 2025

Uh oh!

mrigger commented Aug 14, 2025

Uh oh!

JensonSung commented Aug 22, 2025

Uh oh!

Reviewers

Assignees

Labels