Piotr Sulecki ([EMAIL PROTECTED]) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
nested implicit selects (views) go wrong
Long Description
I'm using PostgreSQL 7.0.2, but the bug is present in 6.5.3 as well.
I'm trying to create a database of league matches. The scoring is sort of table
tennis-like, i.e. the matches are up to two sets won, up to 15 points each. I created
the table 'sets' and then tried to create a view 'matches' when I run into Postgres
overwriting the result of one subselect with the result of another.
The following sequence is narrowed down to the problem "select".
PS. Please Cc: me too as I'm not on the list.
Sample Code
$ psql
psulecki=# create table sets ( wojciech int2 not null, psulecki int2 not null, day
date not null default current_date );
CREATE
psulecki=# insert into sets ( wojciech, psulecki ) values ( 15, 4 );
INSERT 18862 1
psulecki=# insert into sets ( wojciech, psulecki ) values ( 15, 8 );
INSERT 18863 1
psulecki=# insert into sets ( wojciech, psulecki ) values ( 1, 15 );
INSERT 18864 1
psulecki=# select * from sets;
wojciech | psulecki | day
----------+----------+------------
15 | 4 | 2000-09-11
15 | 8 | 2000-09-11
1 | 15 | 2000-09-11
(3 rows)
For now, everything's OK.
psulecki=# create view psulecki as select count (day) as pwin, day as pday from sets
where psulecki > wojciech group by day;
CREATE 18875 1
psulecki=# select * from psulecki;
pwin | pday
------+------------
1 | 2000-09-11
(1 row)
psulecki=# create view wojciech as select count (day) as wwin, day as wday from sets
where psulecki < wojciech group by day;
CREATE 18886 1
psulecki=# select * from wojciech;
wwin | wday
------+------------
2 | 2000-09-11
(1 row)
Ok, correct. But now we have the problem:
psulecki=# select pwin as psulecki, wwin as wojciech, pday as day from wojciech,
psulecki where pday = wday;
psulecki | wojciech | day
----------+----------+------------
2 | 2 | 2000-09-11
(1 row)
????? It should be:
psulecki | wojciech | day
----------+----------+------------
1 | 2 | 2000-09-11
(1 row)
No file was uploaded with this report
