Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Actually I think in except you may only push down to the left, since in
> this case you know that any duplicate from the right will not be
> returned (since there must be none). So, you can't potentially drop
> a row from the right side that may have been a duplicate of a left
> side row that does match the condition.
But we *want* to push down --- the point is to get some selectivity
into the bottom queries. You're right that in a plain EXCEPT it would
be possible to push only to the left, but that doesn't give the
performance improvement we want.
> If we assume two collations one case sensitive one not with the
> except in the non-sensitive and the where in the sensitive and
> a left with 'A' and right with 'a', it'd be incorrect to push a
> case sensitive where foo='A' down to the right since that'd change the
> output from zero rows to one.
You missed my point. Per spec, either zero or one rows out of the whole
thing is okay, because either the 'A' or the 'a' row might be returned
as the representative row for the group by the EXCEPT. Yes, the
behavior may change, but it's still within spec.
> In which case we don't have to worry about the nextval() case.
Yeah, I think nextval() and random() and so forth can be ignored;
the transformations we already do will confuse the results for such
cases, so one more isn't gonna make it worse.
regards, tom lane