[Proposal] Continuous SQLancer fuzzing for Cloudberry (5 bugs from a first run, looking for volunteers) #1952

Proposers

@roseduan, @my-ship-it

Proposal Status

Under Discussion

Abstract

Hi all,

@roseduan and I recently pointed SQLancer at Cloudberry main to see what would happen. Using the stock PostgreSQL provider and no Cloudberry-specific tuning, a few hours of fuzzing surfaced 5 distinct bugs: one silent wrong result, two internal ERRORs, one backend segfault, and one assertion failure. Rose has fix PRs up for two of them, and the other three are filed as issues below.

Given how little effort this took, we think Cloudberry would benefit from running SQLancer regularly and automatically. SQLancer is already listed as a planned testing item on the project roadmap (#868), and it has paid off here before: @shmiwy found #594 and #596 with it in 2024, both since fixed (#722, #598), and @congxuebin's #317 in 2023 was a useful data point even though it turned out to be expected behaviour. What we are proposing is to make this routine instead of an occasional one-off.

To be upfront: neither of us can drive the automation work ourselves right now. We are posting this to share what we found, sketch a plan that we think is realistic, and see whether a few people would like to pick up pieces of it, with our help getting started.

What we found

All five reproduce on current main with a 3-segment demo cluster built with --enable-cassert.

The run used the stock postgres provider against a 3-segment demo cluster built with --enable-cassert. The optimizer=on vs off comparison that exposed bug #1 was done by hand on the failing queries; SQLancer itself does not do that yet (see Implementation).

create table repro (c0 numeric unique);
insert into repro values (1), (2), (null), (null), (null);
select distinct c0 from repro;
-- optimizer=on -> 5 rows (plan is Gather Motion -> Seq Scan, no aggregate)
-- optimizer=off -> 3 rows

CREATE TABLE gs_part (a int) PARTITION BY RANGE (a) DISTRIBUTED BY (a);
CREATE TABLE gs_r (a int) DISTRIBUTED BY (a);
CREATE TABLE gs_s (a int) DISTRIBUTED BY (a);
SET optimizer = on;
EXPLAIN SELECT * FROM gs_s, gs_r FULL JOIN gs_part ON gs_r.a = gs_part.a;
-- ERROR: unexpected gang size: 3 (nodeMotion.c)

CREATE TABLE bogus_t (c0 boolean, c1 boolean);
CREATE INDEX bogus_i ON bogus_t (c0) INCLUDE (c1);
INSERT INTO bogus_t VALUES (true, true), (false, true), (true, false);
SET optimizer = on;
SELECT * FROM bogus_t WHERE c1;
-- ERROR: bogus index qualification (nodeIndexscan.c)
-- EXPLAIN shows "Index Cond: (c1 = true)" on bogus_i; c1 is not a key column

CREATE TABLE t3 (c0 boolean, c1 text, c2 int) DISTRIBUTED BY (c0);
INSERT INTO t3 SELECT (g%2=0), 'x'||g, g FROM generate_series(1,100) g;
CREATE STATISTICS s0 (dependencies) ON c0, c1 FROM t3; -- covers c0, c1 only
ANALYZE t3;
SET optimizer = on;
SELECT * FROM (SELECT ALL t3.c0 AS t3c0, t3.c1 AS t3c1, t3.c2 AS t3c2
 FROM t3 WHERE (t3.c0) IS TRUE
 GROUP BY t3.c0, t3.c1, t3.c2 ORDER BY t3.c1) AS result
WHERE result.t3c0 = TRUE AND result.t3c1 = '' AND result.t3c2 > 0; -- c2 not covered
-- SIGSEGV in CExtendedStatsProcessor::ApplyCorrelatedStatsToScaleFactorFilterCalculation
-- optimizer=off returns 0 rows

CREATE TABLE m1(c0 inet);
CREATE TABLE m2(c0 inet);
INSERT INTO m2 VALUES ('88.147.138.141'), ('76.163.212.11'), ('214.10.65.144');
ANALYZE m1, m2;
SELECT COUNT(*) FROM ONLY m1 LEFT OUTER JOIN m2 ON true
WHERE (m1.c0 IS NOT NULL)
 OR (m2.c0 BETWEEN SYMMETRIC '75.175.243.19' AND '230.9.216.68');
-- FailedAssertion("pselec >= 0.0 && pselec <= 1.0", costsize.c)
-- adjust_selectivity_for_nulltest() is Cloudberry code (gp_adjust_selectivity_for_outerjoins);
-- upstream PostgreSQL has no equivalent assertion, a non-assert build just gets a bad estimate

# Cloudberry with asserts + a demo cluster on port 7000
./configure --enable-cassert --enable-debug <your usual flags> && make -j$(nproc) install
make create-demo-cluster && source gpAux/gpdemo/gpdemo-env.sh
createdb test # the postgres provider connects to "test" first
# SQLancer (no recent Maven Central release, so build from main)
git clone --depth 1 https://github.com/sqlancer/sqlancer && cd sqlancer && mvn -q package -DskipTests
java -jar target/sqlancer-*.jar --num-threads 4 --num-queries 1000 --timeout-seconds 3600 \
 --username gpadmin --password '' \
 postgres --connection-url postgresql://localhost:7000/test \
 --oracle NOREC --test-tablespaces false --test-collations false
# also try --oracle QUERY_PARTITIONING (TLP). Failing cases land in ./logs/postgres/<db>.log
# as a replayable script with the random seed in the header. For each one, re-run the
# final query with optimizer=on and optimizer=off and compare.

Motivation

• Bug Update issue template and PR template #1 is the kind a hand-written regression suite is not designed to catch. No error, no crash, just a different answer than the Postgres planner gives. SQLancer's logic-bug oracles (TLP, NoREC, and PQS) exist for exactly this class of problem.
• Cloudberry has a differential oracle most databases do not have. Any query where optimizer=on and optimizer=off disagree is a bug in one of them. SQLancer calls this idea Differential Query Plans (DQP, SIGMOD 2024) and implements it for MySQL/MariaDB/TiDB via optimizer hints. For Cloudberry it reduces to running each generated query under both settings and comparing result sets. Nobody is generating random queries to exercise that today.
• 4 of the 5 bugs are in ORCA, which is expected rather than alarming. Upstream PostgreSQL fuzzing never touches ORCA, so it has simply had fewer random-query eyes on it than the Postgres planner. The same goes for bug Fix: license rewriting #5 , which is in Cloudberry-specific planner code (adjust_selectivity_for_nulltest), not inherited Postgres code. In other words, the code that is unique to this project is exactly the code that no one else is fuzzing for us. That also means there is probably more low-hanging fruit here, which is good news for anyone who enjoys optimizer bugs.
• An assert-enabled build turns many silent misestimates into crisp reports. Bug Fix: license rewriting #5 only shows up with asserts. Running the optimizer=off leg on an assert build is essentially free coverage for the Postgres-planner side that we do not get otherwise.

What we have not done, so nobody over-reads the result: we used the stock PostgreSQL provider (no DISTRIBUTED BY, partitions, or AO/AOCO/PAX tables in the generated schemas), we compared optimizer on/off by hand, we have not measured the false-positive rate from syntax Cloudberry intentionally does not support, and we have no CI cost numbers yet.

Implementation

Rough shape of what "SQLancer runs continuously and files de-duplicated issues" would take. None of this is decided; it is a starting point for whoever picks it up.

1. A Cloudberry provider for SQLancer

SQLancer has no Greenplum or Cloudberry provider today, but there is a close precedent: the Citus provider subclasses PostgresProvider / PostgresSchema / PostgresOptions (about 1.4k lines versus about 10k for PostgreSQL) and overrides only database creation and the expected-error lists. A Cloudberry provider would follow the same pattern and add:

• DISTRIBUTED BY (...) / DISTRIBUTED REPLICATED / DISTRIBUTED RANDOMLY on generated tables, partitioned tables, and USING ao_row | ao_column | pax storage.
• An expected-error allowlist for MPP restrictions the stock generator will trip on (UNIQUE constraint must contain all columns in the table's distribution key, PRIMARY KEY and DISTRIBUTED RANDOMLY are incompatible, INSERT ON CONFLICT is not supported for appendoptimized relations, scrollable / WITH HOLD cursors, INHERITS with replicated tables, and so on), plus Citus-style per-issue flags so known open bugs do not keep re-firing.
• An OPTIMIZER_DIFF oracle: execute each generated query under optimizer=on and off, compare sorted result sets. The TiDB DQPOracle is about 60 lines and a good template. It should also check optimizer_trace_fallback so that ORCA falling back to the planner is not counted as agreement.
• Reproducers for the new oracle so SQLancer's experimental --use-reducer can minimize its failures. Today the reducer works for the shared NoREC and TLP-WHERE oracles; other oracles and crashes need a small external delta-debugger (replay the log via psql, drop statements greedily while the failure persists).

Where should it live?

• (a) Upstream in sqlancer/sqlancer is our preference: the Citus provider was contributed the same way, and it gives the work visibility beyond this project. Upstream asks for a GitHub Actions job that boots the DBMS, style-clean code, and does prune providers nobody maintains (CnosDB, TDEngine, StoneDB were removed), so (a) implies keeping a Cloudberry container image usable in their CI.
• (b) A separate repo under the ASF project until the allowlist stabilizes, then upstream. We know this needs a PPMC decision and an Infra request, so we are not asking for it lightly.
• (c) Inside the main repo (src/test/sqlancer). SQLancer registers providers in its own Main, so this effectively means vendoring a SQLancer fork; we mention it for completeness.

A practical note for any option: there is no recent Maven Central release of SQLancer, so CI would pin a git SHA and build the jar (about 2 minutes) or cache it by SHA.

2. A scheduled run

• Phase 1, zero provider code. Run the existing TLP and NoREC oracles twice, once with optimizer=on and once with off, via connection options. This already catches crashes and internal errors under each planner.
• Phase 2. Add the provider and the OPTIMIZER_DIFF oracle from section 1.
• Shape. A nightly schedule: workflow with a small matrix ({on, off} x {TLP, NoREC}), each job: debug build (or reuse that day's build-dbg-cloudberry artifact), demo cluster, a time-boxed SQLancer run, then triage. Four jobs of roughly 4 hours each is a small fraction of the ASF Actions budget and stays under the 6-hour job limit. No self-hosted runners needed to start.
• Prerequisite. We noticed the debug build workflow has not had a green run recently (the last 30 are failures or cancelled). Getting it healthy again would be step zero for anything assert-based, and we would be glad to help look at it.
• Cluster crashes. On an MPP cluster a segment or QD crash makes every SQLancer thread fail at once. The runner needs a supervisor loop: on failure, snapshot cores, segment logs and SQLancer logs, restart the cluster, resume with a new seed, and treat everything in that crash window as one event.
• Kill switch. A repository variable checked at job start, so a noisy week does not need a workflow PR.

3. Triage and de-duplication

Three bug classes, three signatures:

• Crash: gdb -batch -ex bt on the core (the existing analyze_core_dumps.sh already does this), signature = hash of the top frames after dropping abort / ExceptionalCondition / raise.
• Internal ERROR / assertion: normalized message plus the (file.c:NNN) location Cloudberry already appends, with literals stripped.
• Wrong result: no stable signature. Re-run the reproducer on a fresh cluster and file only if it fails consistently.

Reporting can start small and grow: a nightly comment on one rolling tracking issue that a human triages, then automatic issue filing once the noise is understood. When automated: carry the signature in an HTML comment in the issue body, search existing issues by signature before filing, cap new issues at a few per run, keep a suppression file for known signatures, and always include the SQLancer seed and commit so a maintainer can replay exactly. Labels: type: Orca or planner from the signature, plus a sqlancer label if the community is happy to add one (or reuse type: Testing + help wanted).

4. Regression protection

Both fix PRs add the minimal reproducer to the regression suite; we would suggest keeping that habit for SQLancer-found bugs so the suite grows with the fuzzer. Fuzzing itself should never gate PRs (it is random and PR CI has enough to do). An optional run-sqlancer PR label that triggers a short fixed-seed run as a non-required check could be useful for ORCA PRs later.

Rollout / Adoption Plan

What we will do

• Keep working on [Bug] ORCA: "bogus index qualification" when a filter on an INCLUDE-only index column is pushed into the index qual #1948 , [Bug] ORCA: QD segfault in CExtendedStatsProcessor when extended statistics (dependencies) do not cover all filtered columns #1949 and [Bug] Planner: FailedAssertion "pselec >= 0.0 && pselec <= 1.0" in adjust_selectivity_for_nulltest() for OR clause under LEFT JOIN #1950 as time allows, and happily hand any of them off as a good first issue to someone who wants to get into ORCA.
• Write up exactly how we ran SQLancer (config, oracles, how we minimized the reproducers) so the next person does not start from zero. The 10-minute snippet above is the short version.
• Offer a 30 to 45 minute pairing / onboarding call to whoever picks up a piece, and review PRs that come out of this.

What we are hoping for: volunteers. Each piece below stands alone, so nobody has to sign up for all of it.

Questions

1. Is there appetite for this as part of regular testing? Anything we have missed or got wrong?
2. Where should the provider live: (a) upstream, (b) a separate repo, or (c) in-tree?
3. CI budget: is a nightly 4-job, 4-hour run acceptable on the shared ASF Actions quota, or should this start on a volunteer's machine posting to the rolling issue?
4. Would a sqlancer label be welcome, or should we reuse type: Testing + help wanted?

If this sounds reasonable and one or two people are interested, we will open a tracking issue with the four tasks above as help wanted sub-tasks after about a week of discussion. Either way, thanks for reading, and we hope the reproducers are useful on their own.