On 1 Aug 2002, Hannu Krosing wrote:
> On Thu, 2002-08-01 at 12:29, Curt Sampson wrote:
> > On Thu, 1 Aug 2002, Zeugswetter Andreas SB SD wrote:
> >
> > > I had a "union all" view, which is actually a quite different animal than
> > > a "union" view which needs to eliminate duplicates before further processing.
> >
> > I had the same problem with UNION ALL.
> >
>
> Could someone give an example where it is not safe to push the WHERE
> clause down to individual parts of UNION (or UNION ALL) wher these parts
> are simple (non-aggregate) queries?
For union, queries that want to do something like use a temporary
sequence to act sort of like rownum and do row limiting. Admittedly
that's already pretty much unspecified behavior, but it does change
the behavior in the place of duplicate removal. In addition, I think
using bits of the spec we don't completely support you can have the
same issue with the undefined behavior of which duplicate is returned
for values that aren't the same but are equal, for example where the
duplicate removal is in one collation but the outer comparison has
a different explicitly given one.
I haven't come up with any useful examples, and not really any for
union all, however.