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

[my-ship-it]

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

#	Component	Symptom	Status
1	ORCA	SELECT DISTINCT / GROUP BY on a nullable UNIQUE column returns duplicate NULLs (silent wrong result)	PR #1941
2	ORCA	Merge FULL JOIN whose one side is provably empty (e.g. a partitioned table with no partitions): ERROR: unexpected gang size	PR #1896
3	ORCA	Filter on an INCLUDE-only index column is pushed into the Index Cond: ERROR: bogus index qualification	#1948
4	ORCA	QD backend segfault (null dereference) in extended-statistics cardinality estimation when a dependencies statistics object does not cover all filtered columns	#1949
5	Planner (Cloudberry-specific code)	Pushed-down OR selectivity outside [0, 1] under a LEFT JOIN trips the assertion in adjust_selectivity_for_nulltest() (assert-enabled build)	#1950

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).

Reproducers

1. ORCA drops the Agg for DISTINCT over a nullable UNIQUE column

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

2. Merge FULL JOIN with a provably-empty side

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)

3. Bogus index qualification with an INCLUDE column

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

4. QD segfault when extended statistics do not cover all filtered columns

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

5. Selectivity outside [0, 1] under an outer join (assert build)

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

Try it yourself in about 10 minutes

# 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.

#	Task	Rough size	"Done" looks like
1	Phase-1 scheduled workflow: assert build + demo cluster + time-boxed SQLancer run with optimizer on and off, artifacts uploaded	small to medium	A schedule / workflow_dispatch workflow that produces logs on demand
2	Cloudberry provider modelled on the Citus provider (distribution policies, partitions, AO/AOCO/PAX, MPP expected-error allowlist)	medium	Runs end-to-end on a demo cluster with a low false-positive rate
3	OPTIMIZER_DIFF oracle plus reproducers for the reducer	small to medium	Finds bug #1 unaided
4	De-duplication (three signature classes), crash supervisor loop, rolling summary issue; automatic filing later	medium	Raw failures collapse to distinct root causes; one nightly comment with new findings

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.

[reshke]

💯

[roseduan]

SQLancer is a great tool for us to discover potential bugs in Cloudberry. And I will continue working on deeper integration with Clouderry. I will also create a MR in sqlancer in the near future, and welcome everyone to run it with cludberry to find more issues.

[leborchuk]

Yep! Very useful tool.

Could you provide detailed instructions on how to launch it in the development environment?

It will be great to perform regular checks. But they should be done by a skilled developer. I do not believe in the results of auto-scans. They usually provide a lot of false positives.

How I see it, right now I am developing Anser (and, in fact, the same considerations apply to many other changes - for example, #1762). At some point, I will decide that I want to enable it for much broader query types. I will conduct some performance tests. However, I should also be sure that there are no other issues, such as wrong results or core dumps. I need good assistance to check, and SQLancer can help me to make sure everything is OK.

How to organize it - let's open an issue on the found bugs. The only subtle point here is that we should thoroughly describe the issue, make good examples of how to reproduce it, and what the expected behavior is. I am going to involve new developers in our community, and it will be a great task to start doing something. Of course, not all issues are simple; some of them are quite tricky. So what? Developing databases is not easy.

[my-ship-it]

Maybe @roseduan could help provide more details on it.

[roseduan]

Yep! Very useful tool.

Could you provide detailed instructions on how to launch it in the development environment?

It will be great to perform regular checks. But they should be done by a skilled developer. I do not believe in the results of auto-scans. They usually provide a lot of false positives.

How I see it, right now I am developing Anser (and, in fact, the same considerations apply to many other changes - for example, #1762). At some point, I will decide that I want to enable it for much broader query types. I will conduct some performance tests. However, I should also be sure that there are no other issues, such as wrong results or core dumps. I need good assistance to check, and SQLancer can help me to make sure everything is OK.

How to organize it - let's open an issue on the found bugs. The only subtle point here is that we should thoroughly describe the issue, make good examples of how to reproduce it, and what the expected behavior is. I am going to involve new developers in our community, and it will be a great task to start doing something. Of course, not all issues are simple; some of them are quite tricky. So what? Developing databases is not easy.

I have created a MR in sqlancer community and wait their review.
sqlancer/sqlancer#1365

[my-ship-it]

Thanks Rose! SQLancer now officially supports Apache Cloudberry (Incubating).
We will set up pipeline running SQLancer regularly against Cloudberry to discover potential bugs and continuously improve its quality.

[roseduan]

Thanks Rose! SQLancer now officially supports Apache Cloudberry (Incubating). We will set up pipeline running SQLancer regularly against Cloudberry to discover potential bugs and continuously improve its quality.

Now sqlancer support full test suites(sqlancer/sqlancer@044e355) of Apache Cloudberry, including PQS, TLP, Group BY/AGGREGATE, etc. Everyone now could run these tests and find potential problems of cloudberry, or you can contribute to this repo to maintain the tests continuously.

[roseduan]

Proposal: add a GitHub Actions workflow that runs SQLancer against main, separate
from the PR CI:

• Triggers: a weekly schedule and manual workflow_dispatch only. It does not run
  on PRs or pushes and is not a required check, like coverity.yml.
• Runs only in apache/cloudberry (repository_owner check), not in forks.
• Builds main in the existing build container, creates the demo cluster and runs
  the NoREC, TLP, PQS and DQP oracles with a fixed SQLancer commit.
• Cost: one runner, about N hours per week, with a hard timeout.
• On failure: upload SQLancer and server logs as artifacts.

How do you think?
@my-ship-it @tuhaihe

[tuhaihe]

+1. I think it looks good. Would you like to create the SQLancer workflow? If you are not available, I will help. Feel free to let me know.

[roseduan]

+1. I think it looks good. Would you like to create the SQLancer workflow? If you are not available, I will help. Feel free to let me know.

OK, I'd be grateful if you can add this routine.