Re: Rules and Views

On Thu, 1 Aug 2002, Stephan Szabo wrote:

> 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.

Replying to myself, you can do this right now with char columns if you
just push the conditions down blindly, something like:

create table t1(a char(5));
create table t2(a char(6));

insert into t1 values ('aaaaa');
insert into t2 values ('aaaaa');

select * from (select * from t2 union select * from t1) as f wherea::text='aaaaa';
select * from (select * from t2 where a::text='aaaaa' unionselect * from t1 where a::text='aaaaa') as f;

The first select gives no rows, the second gives one.  We'd have
to transform the second where clause to something like
cast(a as char(6))::text='aaaaa' in order to get the same effect
I think.