Bug: HAVING `count(*) FILTER (WHERE ref.bool)` is rewritten to `FILTER (WHERE dist.int)` on a two-table distributed join

[Manuel-Neuer1]

Search before asking

I had searched in the issues and found no open report of this. The closest historical bug is #2558 (“Aggregate in HAVING might use the wrong column in joins”, fixed in 2019 by #2569): the worker query received sum(d1.description) instead of sum(amounts.amount). This report is the same class of HAVING var-remapping bug, but for the FILTER (WHERE …) predicate, which still substitutes a distribution column for a reference-table boolean.

Version

• Citus 14.1-1 on PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1)
• Multi-node cluster: 1 coordinator + 2 workers (citusdata/citus:14.1.0)
• Found by an automated equivalence-testing fuzzer (SQLancer-derived DQR oracle): relocating a HAVING predicate into a derived-table projection must not change the result.

What's Wrong?

For a colocated join of two distributed tables plus a reference table, a HAVING count(*) FILTER (WHERE <reference boolean>) query fails on the workers:

ERROR:  argument of FILTER must be type boolean, not type integer
CONTEXT:  while executing command on citus-worker2:5432

The same predicate, placed in a derived-table projection and filtered afterwards (logically equivalent), succeeds and returns the expected row.

EXPLAIN (VERBOSE) shows the coordinator rewrote the FILTER target. The user wrote FILTER (WHERE t0.flag) (t0.flag is boolean); the worker fragment is:

HAVING (count(*) FILTER (WHERE t2.k) > 0)   -- t2.k is the integer distribution column

So this is not a type error in the submitted SQL. Citus substituted the wrong Var while deparsing the HAVING clause for the worker.

The relocated form’s worker fragment keeps FILTER (WHERE t0.flag) and returns the correct result.

How to Reproduce?

Single coordinator + 2 workers. Paste on the coordinator (any database that already has CREATE EXTENSION citus and registered workers):

CREATE TABLE t0(flag boolean);
CREATE TABLE t1(k int);
CREATE TABLE t2(k int);
SELECT create_reference_table('t0');
SELECT create_distributed_table('t1', 'k');
SELECT create_distributed_table('t2', 'k');
INSERT INTO t0 VALUES (true);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);

-- Base form: HAVING FILTER  ->  ERROR
--   argument of FILTER must be type boolean, not type integer
--   CONTEXT: while executing command on <worker>:5432
SELECT t1.k, t2.k, t0.flag
FROM t2 JOIN t1 ON t2.k = t1.k, t0
GROUP BY t1.k, t2.k, t0.flag
HAVING count(*) FILTER (WHERE t0.flag) > 0;

-- Relocated / DQR form: same FILTER in a derived-table projection  ->  1 row (1, 1, t, t)
SELECT *
FROM (
  SELECT t1.k, t2.k, t0.flag,
         (count(*) FILTER (WHERE t0.flag) > 0) AS ref1
  FROM t2 JOIN t1 ON t2.k = t1.k, t0
  GROUP BY t1.k, t2.k, t0.flag
) s
WHERE ref1;

Worker query of the base form (EXPLAIN (VERBOSE, COSTS OFF) on the first SELECT; “Tasks Shown: One of 32”):

SELECT t1.k, t2.k, t0.flag
FROM ((public.t2_XXXX t2 JOIN public.t1_YYYY t1 ON ((t2.k OPERATOR(pg_catalog.=) t1.k)))
      JOIN public.t0_ZZZZ t0 ON (true))
WHERE true
GROUP BY t1.k, t2.k, t0.flag
HAVING (count(*) FILTER (WHERE t2.k) OPERATOR(pg_catalog.>) 0)

Note FILTER (WHERE t2.k) — the original predicate was t0.flag.

Worker query of the relocated form keeps the boolean column:

... (count(*) FILTER (WHERE t0.flag) OPERATOR(pg_catalog.>) 0) AS ref1 ...

Local (non-distributed) copies of the same three tables accept both forms and both return one row. The divergence is specific to the distributed pushdown of HAVING.

Additional analysis (root cause)

Trigger (all of the following; dropping any one makes the base form succeed):

1. Two colocated distributed tables joined on the distribution column.
2. A reference table in the same FROM list (cross join is enough).
3. HAVING count(*) FILTER (WHERE <boolean column of the reference table>).

Shapes that do not fail:

Shape	Base HAVING FILTER (WHERE t0.flag)
Only the reference table	OK
One distributed table + the reference table	OK
Two distributed tables, FILTER (WHERE true)	OK
Two distributed tables, count(*) FILTER (WHERE t0.flag) in the SELECT list, no HAVING	OK
Same SQL on local (non-Citus) tables	OK, both forms return 1 row

So the broken path is specifically: HAVING-clause Aggref.aggfilter remapping across a two-distributed-table join. Putting the same Aggref in the target list (the DQR relocation, or a plain SELECT count(*) FILTER (...)) is planned correctly.

This matches the #2558 pattern (HAVING aggregate attached to the wrong join input) with FILTER as the remaining unfixed site.

Severity: high for any query that uses FILTER in HAVING over a multi-distributed-table join — a well-typed boolean predicate is rewritten into a distribution-column reference and rejected (or, with a boolean distribution column, would silently filter on the wrong column).

Follow-up 1

The same HAVING FILTER remapping fires when the two distributed tables are hash-distributed on int4range, not only on integer. The worker still substitutes the distribution column for the reference-table boolean; only the rejected type in the error changes.

CREATE TABLE t0(c0 smallint, c1 boolean);
CREATE TABLE t2(c0 int4range);
CREATE TABLE t3(c0 int4range);
SELECT create_reference_table('t0');
SELECT create_distributed_table('t2', 'c0');
SELECT create_distributed_table('t3', 'c0');
INSERT INTO t0 VALUES (1, true), (2, false);
INSERT INTO t2 VALUES ('[1,2)'::int4range);
INSERT INTO t3 VALUES ('[1,2)'::int4range);

-- Base: ERROR argument of FILTER must be type boolean, not type int4range
-- CONTEXT: while executing command on <worker>
SELECT t3.c0, t2.c0, t0.c0, t0.c1
FROM t3 JOIN t2 ON (t3.c0 = t2.c0), t0
WHERE t0.c1
GROUP BY t0.c0, t2.c0, t0.c1, t3.c0
HAVING count(*) FILTER (WHERE t0.c1) > 0;

-- Relocated: 1 row  ([1,2), [1,2), 1, t)
SELECT *
FROM (
  SELECT t3.c0, t2.c0, t0.c0, t0.c1,
         (count(*) FILTER (WHERE t0.c1) > 0) AS ref1
  FROM t3 JOIN t2 ON (t3.c0 = t2.c0), t0
  WHERE t0.c1
  GROUP BY t0.c0, t2.c0, t0.c1, t3.c0
) s
WHERE ref1;

Worker fragment of the base form (EXPLAIN (VERBOSE, COSTS OFF)):

HAVING (count(*) FILTER (WHERE t2.c0) OPERATOR(pg_catalog.>) 0)

t2.c0 is int4range; the submitted predicate was t0.c1 (boolean). The substitution is not specific to integer distribution columns — any non-boolean distribution type surfaces as a type error; a boolean distribution column would silently filter on the wrong column.

Follow-up 2

Same HAVING remap with EVERY(ref.bool) (no FILTER). Boolean distribution column → silent wrong rows, 2 vs 0.

CREATE TABLE e0(c0 boolean);
CREATE TABLE e1(c0 boolean);
CREATE TABLE er(c0 boolean);
SELECT create_distributed_table('e0', 'c0');
SELECT create_distributed_table('e1', 'c0');
SELECT create_reference_table('er');
INSERT INTO e0 VALUES (true);
INSERT INTO e1 VALUES (true);
INSERT INTO er VALUES (false), (NULL);

-- Base: 2 rows (t,t,NULL) and (t,t,f)  — wrong
SELECT e0.c0, e1.c0, er.c0
FROM e0 JOIN e1 ON e0.c0 = e1.c0, er
GROUP BY e0.c0, e1.c0, er.c0
HAVING EVERY(er.c0);

-- Relocated: 0 rows  — correct (EVERY(false)/EVERY(NULL) is not TRUE)
SELECT *
FROM (
  SELECT e0.c0, e1.c0, er.c0, EVERY(er.c0) AS ref1
  FROM e0 JOIN e1 ON e0.c0 = e1.c0, er
  GROUP BY e0.c0, e1.c0, er.c0
) s
WHERE ref1;

Worker fragment of the base form:

HAVING every(e0.c0)

Submitted SQL used EVERY(er.c0). e0.c0 is the boolean dist col (true on the join), so both groups pass. One distributed table + reference, and local copies, both forms return 0 rows.