Re: BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE.

Hi,

Thanks for looking into this.

>
> An idea for fixing it: The catalogs already record whether a type's
> equality implies identity, since btree deduplication needs exactly that
> guarantee and gets it from the optional BTEQUALIMAGE_PROC support
> function, which numeric_ops, float8_ops, interval_ops and record_ops all
> lack, precisely because equal values there can be byte-distinct. The
> check added by 44fb59fc605 could consult it wherever a grouping column
> is referenced other than as a direct operand of a comparison testing the
> grouping's own equality and when equality is identity, every member of a
> group is byte-identical and no expression can tell them apart, so the
> reference is safe.

I have been tinkering with the same idea of using equalimage_proc for
a WIP patch.

>
> The side effect is that this reasons about the type rather than the
> function, so it would block quals that were always safe, e.g round(n) =
> 5 respects numeric equality and could never split a group, but the
> planner cannot tell it from scale(n) = 1 without proving something about
> the function body, so it would no longer be pushed past the grouping,
> which can cause performance issues for such cases.
>

But I found that the original thread for 44fb59fc605 [1] already
considered something like this and didn't go with it due to the same
performance impllications. Perhaps it is worth revisiting now that
there has been an actual report with this issue. The original thread
also notes that planning cost will increase for other types like
integer too. I think the planning cost issue might be mitigated by
adding a fast path for image-faithful types. But the performance hit
for expressions like round(n) seems unavoidable.

[1] https://www.postgresql.org/message-id/CAMbWs48q6nO7_nZNrQaqaWHFcYT3g95ONYco90%2B0Lvi2WJgqag%40mail.gmail.com