From: Jinhui Lai <jh.lai@qq.com>
To: David Rowley <dgrowleyml@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18904: INTERSECT with an impossible where should eliminateboth from the query plan
Date: Tue, 29 Apr 2025 20:13:31 +0800
Message-ID: <tencent_F22C20E3310DEC4D88B1BA086FD1DA5DFF0A@qq.com> (raw)
In-Reply-To: <CAApHDvrH7U=jDci=uyPx_TK8bPo4Xfy41q6LvqS6n-5QNO7_sQ@mail.gmail.com>
References: <18904-c5fea7892f4d26ed@postgresql.org>
<CAKFQuwYDxYLNEODZhBL=7iR-7gGfrcFCz=AUBaw2=WEYrjMbhA@mail.gmail.com>
<435085.1745776956@sss.pgh.pa.us>
<CAApHDvrH7U=jDci=uyPx_TK8bPo4Xfy41q6LvqS6n-5QNO7_sQ@mail.gmail.com>
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@gmail.com>
发件时间：2025年4月29日 19:57
收件人：Tom Lane <tgl@sss.pgh.pa.us>
抄送：David G. Johnston <david.g.johnston@gmail.com>, jh.lai@qq.com <jh.lai@qq.com>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
主题：Re: BUG #18904: INTERSECT with an impossible where should eliminateboth from the query plan
On Mon, 28 Apr 2025 at 06:02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Anyway, as David said, this is not a bug. If it's something you
> really want to see happen, try writing a patch yourself.
This is already somewhat better in v18 thanks to [1].
generate_nonunion_paths() already has some code to put the smallest
child on the left, and the recent changes in nodeSetOp.c means very
little work will be done when the left side is empty.
David
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=276279295
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: jh.lai@qq.com, dgrowleyml@gmail.com, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18904: INTERSECT with an impossible where should eliminateboth from the query plan
In-Reply-To: <tencent_F22C20E3310DEC4D88B1BA086FD1DA5DFF0A@qq.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox