On Fri, Mar 29, 2024 at 1:33 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Tomas Vondra <tomas.von...@enterprisedb.com> writes: > > Yeah. I think it's good to design the data/queries in such a way that > > the behavior does not flip due to minor noise like in this case. > > +1 Agreed. The query in problem is: -- we can pull up the sublink into the inner JoinExpr. explain (costs off) SELECT * FROM tenk1 A INNER JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); For this query, the RHS of the semijoin can be unique-ified, allowing it to be joined to anything else by unique-ifying the RHS. Hence, both join orders 'A/C/B' (as in the answer file) and 'B/C/A' (as in the reported plan diff) are legal. So I'm wondering if we can make this test case more stable by using 'c.odd > b.odd' instead of 'c.odd = b.odd' in the subquery, as attached. As a result, the RHS of the semijoin cannot be unique-ified any more, so that the only legal join order is 'A/B/C'. We would not have different join orders due to noises in the estimates, while still testing what we intend to test. Thanks Richard
v1-0001-Stabilize-a-test-case-in-subselect.patch
Description: Binary data
