Repository navigation
Implement SQLancer (a end-to-end SQL fuzz testing library) #11030
Description
Is your feature request related to a problem or challenge?
I noticed an awesome SQL fuzzing framework SQLancer can be implemented on DataFusion, and it is able to detect many bugs even in PostgreSQL and SQLite
Update:
Implementation is now at datafusion-sqlancer
Supported SQL Features
JOINs,ORDER BY,WHERE- Numeric scalar functions/expression operators
- String scalar functions/expression operators
- Aggregate functions,
HAVINGclause - Time related data type functions
- Window functions
- Subquery
- Queries from parquet, csv
- Exploit different configurations (change config knobs like
target_partition,prefer_hash_joinetc.
Supported Test Oracles
Note: most oracles only apply to a subset of available query types, for advanced SQL features like window functions we can only generate random queries and report crashes.
More context for below test oracles at https://github.com/sqlancer/sqlancer/tree/main
- NoREC
- TLP
- PQS
- DQP for logical bugs in joins
- EET for logic bugs in joins and subqueries
How SQLancer works in short
- It's a black box fuzzer, which will be implemented on SQLancer's starter code, and connect to DataFusion using
JDBCto do SQL level testings - It will generate random chaotic SQL queries to stress the system, and make sure it won't crash
- And do extra logical consistency checks using randomly generated SQLs,
SQLancerhas 5 logic check oracles, one of them works like:
NoREC consistency check oracle
Randomly generated query(Q1):
select * from t1 where v1 > 0;
Mutated query(Q2):
select v1 > 0 from t1;
Consistency check:
result size of Q1 should be equal to the number of `True` in Q2's output
Above showed consistency check generated Q1 (very likely to be optimized by predicate pushdown), and Q2(hard to be optimized), such test suit focus on correctness of the optimizer. There are 5 similar test oracles available to be implemented, those carefully designed checks make this testing framework really powerful.
Describe the solution you'd like
I plan to implement SQLancer on DataFusion(starting with a specific test oralcle NoREC which requires less engineering effort).
For now, a minimal subset of SQL features is implemented: it hasn't detected any logical bug yet, just 2 bad-input bugs for some scalar functions showed up
(Will share the code once it is cleaned up)
If you have any features (SQL clauses / data types / specific functions) would like to be further tested, I can implement them first :)
Describe alternatives you've considered
SQLsmith looks like another popular choice, I haven't looked into it carefully yet.
But if it's only generating random SQL to test if the system will crash, then SQLancer should be a more comprehensive tool.
Additional context
SQLancer's page have several papers/YouTube talk video recordings available
Activity
alamb commentedon Jun 20, 2024
Thank you @2010YOUY01 Sounds like a great idea to me -- I have created a datafusion_contrib repo for this work in case you would like to put it there: https://github.com/datafusion-contrib/datafusion-sqllancer
2010YOUY01 commentedon Jul 3, 2024
This is the first interesting bug found: #11248:
It did not crash the DataFusion engine, instead it silently returned an incorrect result.
This logic bug is detected by
NoRECoracle explained in this issue's above examplealamb commentedon Jul 3, 2024
Nice!
2010YOUY01 commentedon Jul 8, 2024
The initial implementation is done (with ~10 bugs found 👀 )
The code is now at https://github.com/datafusion-contrib/datafusion-sqllancer, also with a more detailed description.
There is a lot of work can be done to find more bugs, any contributions are welcomed!
alamb commentedon Jul 10, 2024
This is really nice work @2010YOUY01 -- thank you so much.
alamb commentedon Jul 12, 2024
Filed #11430 to note this on the docs
Also posted on twitter: https://twitter.com/andrewlamb1111/status/1811725290801963475
Thanks again @2010YOUY01
rluvaton commentedon Jan 3, 2025
I would like to help with this, I'm big into tests
2010YOUY01 commentedon Jan 3, 2025
@rluvaton Thank you! I'm still interested in this project (though I haven’t been working on it for a few months 😅) and I'm happy to help with any contributions.
I think we can start with a few easier tasks:
A good approach might be to generate some static random queries and test for no panics. If anything fails, we can comment out the failing query. More advanced property checks should probably be run outside of CI because they are hard to skip, require some manual effort to triage, and might produce false positives.
I still have some local changes that haven’t been pushed to https://github.com/datafusion-contrib/datafusion-sqlancer. I will update it and ensure it works with the latest version of DataFusion next week.
Update: Done, it's now up-to-date
alamb commentedon Jan 5, 2025
This would be great. We currently have some tests running only on commits to main that we could potentially extend
https://github.com/apache/datafusion/blob/main/.github/workflows/extended.yml
datafusion-sqlancerin Rust #14535