Re: Rules and Views

On Thu, 2002-08-01 at 18:02, Tom Lane wrote:
> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > 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.
>
> Hmm. I think this consideration boils down to whether the WHERE clause
> can give different results for rows that appear equal under the rules of
> UNION/EXCEPT/INTERSECT.

Yes. I originally started to ponder this when trying to draw up a plan
for automatic generation of ON UPDATE DO INSTEAD rules for views. While
pushing down the WHERE clause is just a performance thing for SELECT it
is essential for ON UPDATE rules.

> If it gives the same result for any two such
> rows, then it's safe to push down; otherwise not.
>
> It's not too difficult to come up with examples. I invite you to play
> with
>
> select z,length(z) from
> (select 'abc '::char(7) as z intersect
> select 'abc '::char(8) as z) ss;
>
> and contemplate the effects of pushing down a qual involving length(z).

I guess the pushdown must also push implicit conversions done to parts
of union.

if that conversion were applied to z's in both parts of UNION then the
result should be the same.

select z,length(z) from
(
select 'abc '::char(7) as z
union
select 'abc '::char(8) as z
) ss where length(z) = 7;

becomes:

select z,length(z) from
(
select 'abc '::char(7) as z
where length(cast('abc '::char(7) as char(7))) = 7
union
select 'abc '::char(8) as z
where length(cast('abc '::char(8) as char(7))) = 7
) ss ;

which both return 'abc ', 7

Of course it is beneficial to detect when the conversion is not needed,
so that indexes will be used if available.

---------------
Hannu