Re: [HACKERS] create view as select distinct (fwd)

Hehe,

This is why I needed to pass it by the backend gurus :)

Thanks for pointing out these additional issues, Jan.

-Ryan

> > This is on the TODO list.
> >
> > I actually have a solution that seems to work fine, but I wanted to run it 
past
> > the backend guru's after we have finished the 6.5 beta.
> >
> > Sorry I din't get it finished before the beta started.
> >
> > -Ryan
> 
>     I wonder how it does!
> 
>     Have the following:
> 
>       CREATE TABLE t1 (a int4, b text);
>       CREATE TABLE t2 (c int4, d text);
>       CREATE VIEW v2 AS SELECT DISTINCT ON c * FROM t2;
> 
>     Populate them with:
> 
>       t1:
>       1    'one'
>       1    'ena'
>       2    'two'
>       2    'thio'
>       3    'three'
>       3    'tria'
>       4    'four'
>       4    'tessera'
> 
>       t2:
>       1    'I'
>       1    'eins'
>       2    'II'
>       2    'zwei'
>       3    'III'
>       3    'drei'
> 
>     Now you do
> 
>       SELECT t1.a, t1.b, v2.d FROM t1, v2
>         WHERE t1.a = v2.c;
> 
>     Does  that  work  and  produce the correct results? Note that
>     there are more than one correct results. The DISTINCT  SELECT
>     from t2 already has. But in any case, the above SELECT should
>     present 6 rows (all the rows of t1 from 1 to  33  in  english
>     and  greek) and column d must show either the roman or german
>     number.
> 
>     To make it more complicated, add table  t3  and  populate  it
>     with more languages.  Then setup
> 
>       CREATE VIEW v3 AS SELECT DISTINCT ON e * FROM t3;
> 
>     and expand the above SELECT to a join over t1, v2, v3.
> 
>     Finally,  think  about  a view that is a DISTINCT SELECT over
>     multiple tables.  Now you build another view as  SELECT  from
>     the  first  plus  some  other  table  and  make  the new view
>     DISTINCT again.
> 
>     The same kind of problem causes that views  currently  cannot
>     have ORDER BY or GROUP BY clauses. All these clauses can only
>     appear once per query, so there is no room where the  rewrite
>     system  can place multiple different ones.  Implementing this
>     requires first dramatic changes to the querytree layout and I
>     think it needs subselecting RTE's too.
> 
> 
> Sorry - Jan
> 
> --
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #======================================== jwieck@debis.com (Jan Wieck) #
> 
>