Tracking Issue: Port sqlsmith to rust for sql fuzzy testing

[neverchanje]

SQLsmith is a random SQL query generator. It has found many bugs across the database industry.

The original Sqlsmith was licensed under gplv2 which is unfriendly to use. https://github.com/anse1/sqlsmith
CockroachDB has implemented a golang version of sqlsmith:

• https://sourcegraph.com/github.com/cockroachdb/cockroach@0782befa48d54a67c3830963280838137542539e/-/blob/pkg/internal/sqlsmith/sqlsmith.go
• https://www.cockroachlabs.com/blog/sqlsmith-randomized-sql-testing/

The remaining work of Sqlsmith:

• Sqlsmith: Generate unary functions #3365
• Sqlsmith: Generate triple/variadic functions #3995
• Sqlsmith: Generate With Clause (CTE) #3959
• Sqlsmith: Generate EquiJoin #3369
• Sqlsmith: test mview more #4173
• Sqlsmith: Generate Group By Clause #3366
• Sqlsmith: Generate Having Clause #3367
• Sqlsmith: Generate time window function #3962
• sqlsmith: Generate table function #4540
• Sqlsmith: Generate INSERT statement. #3844
• Sqlsmith: Generate Aggregate #3858
• Sqlsmith: Generate Type Castings #3878
• Sqlsmith: Generate ORDER BY #4462

Test suite:

• Sqlsmith: handle panics for tests executing in parallel #4086
• ci: run frontend and e2e as cron #4139
• Sqlsmith: Improve design of context #4056
• ci: enable frontend tests when submitting PR which changes sqlsmith
• Sqlsmith: test mview more #4173
• Sqlsmith: dump setup sql (create table, create materialized view)
• Sqlsmith: add developer docs

RisingWave bugs reported:

• sqlsmith bug hunt: panic on btrim #3356
• sqlsmith bug hunt: Type mismatch when substituting Int32 with Varchar #3342
• sqlsmith bug hunt: Panics on temporal types expressions #3341
• sqlsmith bug hunt: mview on mview with SELF JOIN fails #4083
• sqlsmith bug hunt: cannot recognize large numeric #4399
• SqlSmith Bug Hunt: panic when binding with-CTE containing expr::Subquery #4404
• sqlsmith bug hunt: 'assertion failed: (left == right) , src/frontend/src/optimizer/plan_node/logical_expand.rs:156:13 #4218
• sqlsmith bug hunt: 'internal error: entered unreachable code', src/frontend/src/optimizer/plan_node/batch_nested_loop_join.rs:50:23 #4220
• sqlsmith bug hunt: parse negative numbers #4344
• SqlSmith Bug hunt: Aggregate of Subquery results in panic #4434
• sqlsmith bug hunt: supported sum(interval) #4508
• sqlsmith bug hunt: channels not propagating errors #3908
• sqlsmith bug hunt: 'internal error: entered unreachable code', src/frontend/src/optimizer/plan_node/batch_nested_loop_join.rs:50:23 #4220
• sqlsmith bug hunt: parse negative numbers #4344

Tracking issue for workarounds:

• Tracking Issue: Fix Sqlsmith workarounds / refinements / bugs #3896

Other Notes:

• Please run e2e tests locally when submitting PRs, it is not yet integrated into CI as it is still unstable.
• Please prioritize stabilizing SqlSmith, so we can include in our e2e test suite. Will appreciate closing out bugs with SqlSmith and RisingWave first!

Thank you for your contributions!

[liurenjie1024]

cc @sumittal

[kwannoel]

Wondering if something like Quickcheck's shrink would be useful here. Do generated queries get very complex and hard to debug?

Perhaps can be used for other fuzzers we eventually use too.

If a generated test case fails, Quickcheck's shrink takes the input and shrinks it as much as possible, to make it easier to debug.
In our case, we need to take the generated query and shrink it, producing a simplified query.
With the simplified query we re-run it to try and reproduce a crash.
If it crashes, we can then use simplified query to debug.

Some ways I can think of shrinking, off the top of my head:

• Reducing no. of terms selected, projected etc...
• Extracting subqueries.

[liurenjie1024]

As part of fuzzy test, we should also generate invalid sql, can sqlsmit support that?

[neverchanje]

As part of fuzzy test, we should also generate invalid sql, can sqlsmit support that?

Currently, no. Invalid sql is too random. If there's unlimited time, surely you can do that.

[neverchanje]

Do generated queries get very complex and hard to debug?

No, actually. The cockroach version has parameters to control the overall complexity of the generated sql. It will limit the recursions (subqueries), select items, and tables in a join. So generally, we can let the sql adapt to our wishes.

[kwannoel]

Remaining refinements / workarounds / bugs are tracked in #3896 . Closing this issue.