cale@sun1.ruf.uni-freiburg.de (Peter Caligari) (04/06/91)
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