Newsgroups: comp.databases
Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!sol.ctr.columbia.edu!ira.uka.de!sun1.ruf.uni-freiburg.de!cale
From: cale@sun1.ruf.uni-freiburg.de (Peter Caligari)
Subject: INGRES: create DISTINCT view as select .. from view/table
Message-ID: <1991Apr5.181544.22474@sun1.ruf.uni-freiburg.de>
Keywords: INGRES,table,view,distinct
Organization: Rechenzentrum der Universitaet Freiburg, Deutschland
Distribution: comp
Date: Fri, 5 Apr 91 18:15:44 GMT
Lines:      205


I recognized a quite strange behaviour in INGRES when trying to eliminate 
duplicate rows from a table field. I had two tables in wich duplicates 
were allowed (t1 and t2). Both tables contained a column (nr, see below) witch
is unique to it. Then I created a view (v) and a table (t), both joining 
tables 't1' and 't2' by that column 'nr'. 

Last I created a second view (v2 or v3 resp.) as:

        CREATE VIEW V2 AS SELECT DISTINCT A,B FROM V or
        CREATE VIEW V3 AS SELECT DISTINCT A,B FROM T

I expected INGRES to behave the same, but it didn't. Ther is a difference 
wether that last view is created out of a table or a view, although the 
corrosponding SELECTS worked the same on the view and the table.

This seams absolutly ridiculous to me. As far as select are concerned I 
thought of views as beeing totaly analog to 'real' tables.

Anybody out there having an explanation of this?

Thanks in advance, kindly 

        Peter (cale@ruf.uni-freiburg.de)

PS: What follows is a kind of 'dump' of the hole story, so it will probably 
    better for you to follow:
 
create table t1(
        nr i4 with null,
        a i4 with null,
        b i4 with null
)
with duplicates,
location = (ii_database)

create table t2(
        nr i4 with null,
        c i4 with null
)


  1> select * from t1

+-------------+-------------+-------------+
|nr           |a            |b            |
+-------------+-------------+-------------+
|            1|            1|            1|
|            2|            1|            1|
|            3|            2|            2|
+-------------+-------------+-------------+
(3 rows)
End of Request

  1> select * from t2

+-------------+-------------+
|nr           |c            |
+-------------+-------------+
|            1|            1|
|            2|            2|
|            3|            3|
+-------------+-------------+
(3 rows)
End of Request


  1> select a,b from t1

+-------------+-------------+
|a            |b            |
+-------------+-------------+
|            1|            1|
|            1|            1|
|            2|            2|
+-------------+-------------+
(3 rows)
End of Request

  1> select distinct a,b from t1

+-------------+-------------+
|a            |b            |
+-------------+-------------+
|            1|            1|
|            2|            2|
+-------------+-------------+
(2 rows)
End of Request

  1> create view v as
  2> select t1.a,t1.b,t2.c from t1,t2
  3> where t1.nr = t2.nr

  1> select * from v

+-------------+-------------+-------------+
|a            |b            |c            |
+-------------+-------------+-------------+
|            1|            1|            1|
|            1|            1|            2|
|            2|            2|            3|
+-------------+-------------+-------------+
(3 rows)
End of Request

  1> select a,b from v

+-------------+-------------+
|a            |b            |
+-------------+-------------+
|            1|            1|
|            1|            1|
|            2|            2|
+-------------+-------------+
(3 rows)
End of Request

  1> select distinct a,b from v

+-------------+-------------+
|a            |b            |
+-------------+-------------+
|            1|            1|
|            2|            2|
+-------------+-------------+
(2 rows)
End of Request

  1> create view v2 as
  2> select distinct a,b from v

End of Request

  1> select * from v2

+-------------+-------------+
|a            |b            |
+-------------+-------------+
|            1|            1|
|            1|            1|
|            2|            2|
+-------------+-------------+
(3 rows)
End of Request

  1> create table t as
  2> select t1.a,t1.b,t2.c from t1,t2
  3> where t1.nr = t2.nr

(3 rows)
End of Request

  1> select * from t

+-------------+-------------+-------------+
|a            |b            |c            |
+-------------+-------------+-------------+
|            1|            1|            1|
|            1|            1|            2|
|            2|            2|            3|
+-------------+-------------+-------------+
(3 rows)
End of Request

  1> select a,b from t

+-------------+-------------+
|a            |b            |
+-------------+-------------+
|            1|            1|
|            1|            1|
|            2|            2|
+-------------+-------------+
(3 rows)
End of Request

  1> select distinct a,b from t

+-------------+-------------+
|a            |b            |
+-------------+-------------+
|            1|            1|
|            2|            2|
+-------------+-------------+
(2 rows)
End of Request

  1> create view v3 as
  2> select distinct a,b from t

End of Request

  1> select * from v3

+-------------+-------------+
|a            |b            |
+-------------+-------------+
|            1|            1|
|            2|            2|
+-------------+-------------+
(2 rows)
End of Request