Re: BUG #18904: INTERSECT with an impossible where should eliminateboth from the query plan

Dear PostgreSQL Developer,

My point is that this kind of query, which is always logically empty, should not be executed at all. Executing it not only wastes time, but more concerningly, it can lead to incorrect results. I have discovered bugs in MySQL, MariaDB, and TiDB where such queries, which should have returned empty result sets, instead produced incorrect non-empty results due to being actually executed. Since I’ve only recently started working with PostgreSQL in the past two days, I haven’t yet tested whether it exhibits similar bugs. If I do find any such cases, I will provide concrete examples. Therefore, my additional point is that avoiding the execution of such queries can fundamentally eliminate these types of logical bugs at their source.

Best regrerds,
原始邮件


发件人：David Rowley <dgrowleyml(at)gmail(dot)com&gt;
发件时间：2025年4月29日 19:57
收件人：Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us&gt;
抄送：David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com&gt;, jh(dot)lai(at)qq(dot)com <jh(dot)lai(at)qq(dot)com&gt;, pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;
主题：Re: BUG #18904: INTERSECT with an impossible where should eliminateboth from the query plan

On&nbsp;Mon,&nbsp;28&nbsp;Apr&nbsp;2025&nbsp;at&nbsp;06:02,&nbsp;Tom&nbsp;Lane&nbsp;<tgl(at)sss(dot)pgh(dot)pa(dot)us&gt;&nbsp;wrote:
&gt;&nbsp;Anyway,&nbsp;as&nbsp;David&nbsp;said,&nbsp;this&nbsp;is&nbsp;not&nbsp;a&nbsp;bug.&nbsp;&nbsp;If&nbsp;it's&nbsp;something&nbsp;you
&gt;&nbsp;really&nbsp;want&nbsp;to&nbsp;see&nbsp;happen,&nbsp;try&nbsp;writing&nbsp;a&nbsp;patch&nbsp;yourself.

This&nbsp;is&nbsp;already&nbsp;somewhat&nbsp;better&nbsp;in&nbsp;v18&nbsp;thanks&nbsp;to&nbsp;[1].
generate_nonunion_paths()&nbsp;already&nbsp;has&nbsp;some&nbsp;code&nbsp;to&nbsp;put&nbsp;the&nbsp;smallest
child&nbsp;on&nbsp;the&nbsp;left,&nbsp;and&nbsp;the&nbsp;recent&nbsp;changes&nbsp;in&nbsp;nodeSetOp.c&nbsp;means&nbsp;very
little&nbsp;work&nbsp;will&nbsp;be&nbsp;done&nbsp;when&nbsp;the&nbsp;left&nbsp;side&nbsp;is&nbsp;empty.

David

[1]&nbsp;https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=276279295