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

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

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

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

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