Implement SQLancer (a end-to-end SQL fuzz testing library)

[2010YOUY01]

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, HAVING clause
• Time related data type functions
• Window functions
• Subquery
• Queries from parquet, csv
• Exploit different configurations (change config knobs like target_partition, prefer_hash_join etc.

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

1. It's a black box fuzzer, which will be implemented on SQLancer's starter code, and connect to DataFusion using JDBC to do SQL level testings
2. It will generate random chaotic SQL queries to stress the system, and make sure it won't crash
3. And do extra logical consistency checks using randomly generated SQLs, SQLancer has 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

[alamb]

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]

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 NoREC oracle explained in this issue's above example

[alamb]

Nice!

[2010YOUY01]

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]

This is really nice work @2010YOUY01 -- thank you so much.

[alamb]

Filed #11430 to note this on the docs

Also posted on twitter: https://twitter.com/andrewlamb1111/status/1811725290801963475

Thanks again @2010YOUY01

[rluvaton]

I would like to help with this, I'm big into tests

[2010YOUY01]

@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:

1. Run SQLancer against the latest DataFusion and report any newly discovered bugs (the last time I ran it was in October of last year).
2. Integrate it into the extended CI.
   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.
3. Add support for recently introduced built-in functions

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]

Integrate it into the extended CI.

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