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.
