Re: Convert ALL SubLinks to ANY SubLinks

Hi all,

On Fri, Sep 11, 2026 at 11:57 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> On Thu, Feb 26, 2026 at 4:11 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> > Attached is a draft patch illustrating the idea. Any thoughts or
> > interest in this transformation?
>
> Here is an updated version of the patch, with better comments and a
> commit message.
>
> Considering where we are in the current release cycle, I am targeting
> this for v20.

Thank you for the updated patch. I reviewed and tested the v2 patch.
Since the regression files in the patch were based on a slightly
different version of the tree, I manually integrated the new
regression test cases into subselect.sql and subselect.out. The
subselect regression tests passed successfully: # All 240 tests
passed. I also verified the new test cases added by the patch. The
plans matched the intent of the patch:

1. <> ALL is converted to NOT (ANY ...) and can use a hashed ANY SubPlan.
2. Row-wise <> ALL is similarly converted to a hashed ANY SubPlan.
3. Cases where hashing is not applicable fall back to an ANY SubPlan
with Materialize.
4. ALL cases that can be pulled up are converted into anti-joins.
5. Negated ALL cases can be pulled up into the corresponding join paths.
6. When the operator has no negator, the original ALL SubPlan is
retained as expected.

I also performed additional semantic testing against the equivalent De
Morgan transformation by comparing a < ALL (SELECT ...) with: NOT (a
>= ANY (SELECT ...)) using combinations involving ordinary values,
NULL values, empty subqueries, and NULL outer values. The results
matched in the cases tested, including the expected SQL
three-valued-logic behavior. In addition, I tested Boolean expressions
within the ALL test expression using both AND and OR expressions. The
resulting plans showed the expected conversion to NOT (ANY (...)),
while retaining the Boolean expression structure.
Overall, based on the code inspection, regression testing, plan
verification, and the additional semantic tests, I did not find any
correctness issues with the v2 patch. +1 from my side for v2 patch
based on the testing.

Regards,
Solai