Kevin Grittner <kgrittn@ymail.com> writes:
> Further confirmation using the EXPLAIN patch with Antonin's v2
> patch against the table before any EXPLAIN or ANALYZE:
> �Hash Join� (cost=37.12..80.40 rows=442 width=12)
> �� Hash Cond: (((upper.f2)::double precision = lower.f3) AND (upper.f1 = lower.f2))
> �� ->� Seq Scan on subselect_tbl upper� (cost=0.00..27.70 rows=1770 width=16)
> �� ->� Hash� (cost=34.12..34.12 rows=200 width=12)
> �������� ->� HashAggregate� (cost=32.12..34.12 rows=200 width=12)
> �������������� Group Key: lower.f2
> �������������� ->� Seq Scan on subselect_tbl lower� (cost=0.00..27.70 rows=1770 width=12)
That's about what I thought: it's unique-ifying according to the original
semijoin qual, without realizing that the pulled-up clause from the lower
WHERE would need to be treated as part of the semijoin qual. This isn't
a bug in the existing code, because the case can never arise, since we
don't treat an IN/=ANY as a semijoin if the sub-select contains any
outer-level Vars. But if you remove that check from
convert_ANY_sublink_to_join then you've got to deal with the problem.
That said, I'm not too sure where the problem is in detail. I'd have
thought that subquery pullup would stick the subquery's WHERE clause
into the join quals of the parent JoinExpr node. Is that not happening,
or is it perhaps not sufficient to cue the UniquePath machinery?
> The additional information is so useful, I'm all for committing
> that patch.
Will do.
regards, tom lane