Re: BUG #19805: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP

Hi Tender,

Sorry, This is a duplicate of BUG #19800, please follow up there.

Thanks,
Chunling

原始邮件


发件人：Tender Wang <tndrwang(at)gmail(dot)com&gt;
发件时间：2026年10月30日 15:27
收件人：303677365 <303677365(at)qq(dot)com&gt;, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;
主题：Re: BUG #19805: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP

PG&nbsp;Bug&nbsp;reporting&nbsp;form&nbsp;<noreply(at)postgresql(dot)org&gt;&nbsp;于2026年10月30日周五&nbsp;15:11写道：
&gt;
&gt;&nbsp;The&nbsp;following&nbsp;bug&nbsp;has&nbsp;been&nbsp;logged&nbsp;on&nbsp;the&nbsp;website:
&gt;&nbsp;SET&nbsp;enable_self_join_elimination&nbsp;=&nbsp;on;&nbsp;&nbsp;&nbsp;--&nbsp;default
&gt;&nbsp;SELECT&nbsp;s1.a,&nbsp;s2.a&nbsp;FROM&nbsp;m2&nbsp;s1&nbsp;JOIN&nbsp;m2&nbsp;s2&nbsp;ON&nbsp;s1.a&nbsp;=&nbsp;s2.a
&gt;&nbsp;&nbsp;GROUP&nbsp;BY&nbsp;CUBE&nbsp;(s1.a,&nbsp;s2.a)&nbsp;ORDER&nbsp;BY&nbsp;1,&nbsp;2;
&gt;
&gt;&nbsp;SET&nbsp;enable_self_join_elimination&nbsp;=&nbsp;off;
&gt;&nbsp;SELECT&nbsp;s1.a,&nbsp;s2.a&nbsp;FROM&nbsp;m2&nbsp;s1&nbsp;JOIN&nbsp;m2&nbsp;s2&nbsp;ON&nbsp;s1.a&nbsp;=&nbsp;s2.a
&gt;&nbsp;&nbsp;GROUP&nbsp;BY&nbsp;CUBE&nbsp;(s1.a,&nbsp;s2.a)&nbsp;ORDER&nbsp;BY&nbsp;1,&nbsp;2;
&gt;&nbsp;SQL
&gt;
&gt;&nbsp;Actual&nbsp;output&nbsp;(N&nbsp;=&nbsp;NULL):
&gt;
&gt;&nbsp;SJE&nbsp;on&nbsp;:&nbsp;(1,N)&nbsp;(1,1)&nbsp;(2,2)&nbsp;(2,N)&nbsp;(N,N)&nbsp;(N,1)&nbsp;(N,2)
&gt;&nbsp;SJE&nbsp;off:&nbsp;(1,1)&nbsp;(1,N)&nbsp;(2,2)&nbsp;(2,N)&nbsp;(N,1)&nbsp;(N,2)&nbsp;(N,N)

Hi,

This&nbsp;appears&nbsp;to&nbsp;be&nbsp;the&nbsp;same&nbsp;SJE/GROUPING&nbsp;SETS&nbsp;issue&nbsp;already&nbsp;reported
in&nbsp;BUG&nbsp;#19800&nbsp;[1]

Could&nbsp;you&nbsp;check&nbsp;whether&nbsp;your&nbsp;test&nbsp;case&nbsp;exposes&nbsp;a&nbsp;different&nbsp;underlying&nbsp;issue?
If&nbsp;so,&nbsp;please&nbsp;explain&nbsp;how&nbsp;it&nbsp;differs&nbsp;in&nbsp;this&nbsp;thread.&nbsp;Otherwise,&nbsp;please
keep&nbsp;additional&nbsp;test&nbsp;cases&nbsp;in&nbsp;the&nbsp;existing&nbsp;thread.

For&nbsp;future&nbsp;reports,&nbsp;please&nbsp;check&nbsp;for&nbsp;existing&nbsp;reports&nbsp;of&nbsp;the&nbsp;same
issue&nbsp;before&nbsp;opening&nbsp;a&nbsp;new&nbsp;thread.

[1]&nbsp;https://www.postgresql.org/message-id/19800-2f4fc7fcfb9a17a1%40postgresql.org

--&nbsp;
Thanks,
Tender&nbsp;Wang