Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

On Tue, Jul 7, 2026 at 7:44 AM Fujii Masao <masao.fujii@gmail.com> wrote:
> In HEAD, I found what looks like another case of the same issue. Is the
> cause essentially the same as the one fixed by your commit?
>
>     --------------------------
>     CREATE TEMP TABLE t (id int, num numeric);
>     INSERT INTO t VALUES (1, numeric '1.0'), (2, numeric '1.00');
>
>     SELECT * FROM (
>         SELECT DISTINCT ON (num) id, num FROM t ORDER BY num, id
>     ) s WHERE scale(num) = 2;
>     --------------------------
>
> This query should return no rows. The subquery selects the first row
> (id = 1, num = 1.0), and the outer WHERE clause should then filter
> it out because scale(1.0) = 1, not 2.

This is a known limitation, and was previously reported by Zsolt
Parragi at [1].  I explained why this patch doesn't address this in
[2] and [3].  The limitation is also documented in the comment:

 * This leaves one case uncaught: with a deterministic collation, a function
 * over the column can still feed a finer comparison than the direct-operand
 * check sees, for example record_image_ops over a rebuilt record, or scale()
 * over numeric where two equal values differ in scale.  Catching it would
 * require knowing that a type's equality is bitwise, which we do not test
 * here.

(Note that your specific repro is explicitly mentioned in this
comment; see the part about "scale() over numeric where two equal
values differ in scale".)

I'm not entirely sure whether this edge case is common enough to
warrant the complexity and overhead required to fix it.  If it does
turn out to be a frequent issue, we might be able to address it down
the road using Zsolt Parragi's equalimage approach.

Thoughts?

[1] https://postgr.es/m/CAN4CZFP4PrDi9-OKbFXTe9M1VEZHtj0nBxTiwM_p7fmZ9C2Xyw@mail.gmail.com
[2] https://postgr.es/m/CAMbWs49tXgHvyD-7PwMShtHeoYUqxxM9i-=FKazLrQLPT_APTA@mail.gmail.com
[3] https://postgr.es/m/CAMbWs48q6nO7_nZNrQaqaWHFcYT3g95ONYco90+0Lvi2WJgqag@mail.gmail.com

- Richard