#1952 [Proposal] Continuous SQLancer fuzzing for Cloudberry (5 bugs from a first run, looking for volunteers) New discussion created by 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](https://github.com/apache/cloudberry/pull/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](https://github.com/apache/cloudberry/pull/1896) | | 3 | ORCA | Filter on an INCLUDE-only index column is pushed into the Index Cond: ERROR: bogus index qualification | [#1948](https://github.com/apache/cloudberry/issues/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](https://github.com/apache/cloudberry/issues/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](https://github.com/apache/cloudberry/issues/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 #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 Que… apache/cloudberry