GitHub user my-ship-it created a discussion: [Proposal] Continuous SQLancer fuzzing for Cloudberry (5 bugs from a first run, looking for volunteers)
### Proposers @roseduan, @my-ship-it ### Proposal Status Under Discussion ### Abstract Hi all, @roseduan and I recently pointed [SQLancer](https://github.com/sqlancer/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). <details> <summary>Reproducers</summary> **1. ORCA drops the Agg for DISTINCT over a nullable UNIQUE column** ```sql 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** ```sql 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** ```sql 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** ```sql 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)** ```sql 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 ``` </details> <details> <summary>Try it yourself in about 10 minutes</summary> ```bash # 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. ``` </details> ### Motivation - **Bug #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 #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 #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 #1948, #1949 and #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. GitHub link: https://github.com/apache/cloudberry/discussions/1952 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
