Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> It's not immediately obvious to me that these are equivalent, or
>> perhaps I should say it's not clear under what conditions is the
>> transformation legitimate.
> Could you tell me in what cases two of above are not equivalent?
It may well be OK, I just want to see a rigorous demonstration of it.
It *looks* right, but intuition is frequently misleading. Two points
that particularly need thought are (a) what about NULLs --- SQL's
three-way boolean logic breaks a lot of other things that seem right
intuitively; (b) does the same equivalence hold for UNION ALL,
INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL?
If you think that it's so obvious as not to require any thought,
I offer the following counterexample:
... WHERE random() > 0.5;
Pushing down a WHERE like this one *will* change the results.
That particular case doesn't bother me, but user-defined functions
that access other tables might have history-dependent behavior,
too. Do we need to allow for that?
regards, tom lane