Skip site navigation (1) Skip section navigation (2)

nested implicit selects (views) go wrong

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: nested implicit selects (views) go wrong
Date: 2000-09-11 11:47:24
Message-ID: 200009111147.e8BBlOI22582@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Piotr Sulecki (piotr(dot)sulecki(at)ios(dot)krakow(dot)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 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


Responses

pgsql-bugs by date

Next:From: Lamar OwenDate: 2000-09-11 14:43:11
Subject: Re: [SQL] Re: [BUGS] "ORDER BY" issue - is this a bug?
Previous:From: Mario Farias-Elinos BaezDate: 2000-09-11 07:23:34
Subject: Re: what utilities do i need for postgres on solaris 7 ??

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group