On Wed, 30 Apr 2025 at 00: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. 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.
I appreciate you pointing these things out. There are certainly other places in our codebase where we are better at optimising things when finding a dummy relation. e.g INNER JOINs. Your part about "it can lead to incorrect results", you've shown us no evidence of that. If you have some, please demonstrate the incorrect results.
All we're telling you here is that you've shown us no evidence that there's an actual bug here. Per Tom's email in [1], he doesn't seem particularly against making improvements in this area and I'm not either, but not part of a bug fix. Any changes for this would be for PostgreSQL 19 at the earliest.
If you're keen to work on the patch, have a look at is_dummy_rel() in joinrels.c. Likely that'll need to be modified to handle SubqueryScanPaths. The bulk of the code changes after that will go into generate_nonunion_paths(). Take note about Tom's warning about INTERSECT ALL vs INTERSECT. If you do come up with a patch, post it to the pgsql-hackers mailing list and explain what you'd like to do. See the guidelines in [2].
