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

On Tue, 29 Apr 2025 at 18:13, Jinhui Lai <jh.lai@qq.com> wrote:

> 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.
>

So, you fear unfound bugs.

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.
>

If you find bugs the team is normally very good in finding those, and any
bug you find can affect not only "logically empty" but some other queries,
so it is better to fix the real bug. In fact you stop a testing tool,
sending some logically empty queries and asserting the empty result.

OTOH adding a "logically empty prover" adds complexity, maintenance effort
and bug surface to the system. You could end up with incorrectly empty
queries due to a bug in the "prover" classifying a query as empty where it
was not. And it could, and I believe it would, result in overall decreased
performance for most of the user base.

Francisco Olarte.