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

luliqwerty

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

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

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

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

JensonSung

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.

mrigger

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?

JensonSung

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!

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

JensonSung

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

mrigger

Agreed, lgtm, thanks a lot!

mrigger

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

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

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

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

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

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

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

luliqwerty mentioned this pull request Sep 2, 2025

feat(DQE): Add Extensibility to DQE #1272

Merged

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!

mrigger commented Jul 6, 2025

Uh oh!

mrigger left a comment

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!

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

Projects

Milestone

Development

Uh oh!

5 participants