Thread

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

Jan Wieck <jwieck@debis.com> — 1999-04-26T06:57:27Z

Oleg Broytmann wrote:

>
> Hello!
>
>    VIEW on 6.4.2 ignores DISTINCT. Is it a bug? known?
>

    It's  a known missing feature (not a bug - more like a design
    fault).

    DISTINCT is implemented as a unique sort step taken over  the
    final  result of a query. Views are implemented via the query
    rewrite rule system. If now someone would define  a  DISTINCT
    view and selects a join of it with another table, the rewrite
    system cannot tell the planner that only the scan's resulting
    from  the  view  should be sorted unique.  It could only tell
    that the entire result should be DISTINCT - what's wrong - so
    I left it out.

    I'm  planning  to  implement some kind of subquery rangetable
    entries someday. At that time, all these problems  (DISTINCT,
    GROUP BY, ORDER BY) of views will disappear.


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) #

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

Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-10T16:19:49Z


I assume this has not been fixed?



> Hello!
> 
>    VIEW on 6.4.2 ignores DISTINCT. Is it a bug? known?
> 
> Oleg.
> ---- 
>     Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
>            Programmers don't die, they just GOSUB without RETURN.
> 
> ---------- Forwarded message ----------
> Date: Fri, 23 Apr 1999 13:33:00 +0400 (MSD)
> From: Artem Chuprina <ran@pirit.com>
> To: Oleg Broytmann <phd@sun.med.ru>
> Subject: create view as select distinct
> 
> pirit=> select distinct value_at from www_counter_store;
>   value_at
> ----------
> 04-22-1999
> (1 row)
> 
> pirit=> create view www_counter_store_dates as select distinct value_at from www_counter_store;
> CREATE
> pirit=> select * from www_counter_store_dates;
> ----------
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> 04-22-1999
> (15 rows)
> 
> -- 
> Artem Chuprina                             E-mail: ran@pirit.com
> Network Administrator                        FIDO: 2:5020/371.32
> PIRIT Corp.                              Phone: +7(095) 115-7101
> 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-10T16:26:44Z

Perhaps we should issue an error message if this is attempted until we
get it working?


> Oleg Broytmann wrote:
> 
> >
> > Hello!
> >
> >    VIEW on 6.4.2 ignores DISTINCT. Is it a bug? known?
> >
> 
>     It's  a known missing feature (not a bug - more like a design
>     fault).
> 
>     DISTINCT is implemented as a unique sort step taken over  the
>     final  result of a query. Views are implemented via the query
>     rewrite rule system. If now someone would define  a  DISTINCT
>     view and selects a join of it with another table, the rewrite
>     system cannot tell the planner that only the scan's resulting
>     from  the  view  should be sorted unique.  It could only tell
>     that the entire result should be DISTINCT - what's wrong - so
>     I left it out.
> 
>     I'm  planning  to  implement some kind of subquery rangetable
>     entries someday. At that time, all these problems  (DISTINCT,
>     GROUP BY, ORDER BY) of views will disappear.
> 
> 
> 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) #
> 
> 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

Jan Wieck <jwieck@debis.com> — 1999-05-10T16:39:08Z

Bruce Momjian wrote:

>
>
>
> I assume this has not been fixed?
>
>
>
> > Hello!
> >
> >    VIEW on 6.4.2 ignores DISTINCT. Is it a bug? known?
> >
> > Oleg.

    Right.  This  requires subselecting RTE's - one of the bigger
    TODO's after v6.5.

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) #