Piotr Sulecki (piotr.sulecki@ios.krakow.pl) 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
twosets won, up to 15 points each. I created the table 'sets' and then tried to create a view 'matches' when I run into
Postgresoverwriting 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
