Query returning incorrect results - Mailing list pgsql-hackers

When I execute the following two queries, the results differ -- with the
only change being that another table is joined (a 1-1 join that should not
affect the results -- I reduced down a much larger query that was
exhibiting the behavior to what appears to be the cause).  I know that
views have some limitations, and two of the relations used are views, so I
belive that that may be the problem, but I want to be sure...

Thanks

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

football=# SELECT play.play_id as play_id, year.correct_picks
as ytd_correct_picks FROM tblweek_correct correct, tblplayer play,
tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id =
correct.play_id AND correct.game_week = 6 AND play.play_id = 4;play_id | ytd_correct_picks 
---------+-------------------      4 |               141
(1 row)

football=# SELECT play.play_id as play_id, year.correct_picks as
ytd_correct_picks FROM tblplayer play, tblyear_correct year WHERE
play.play_id = year.play_id AND play.play_id = 4;play_id | ytd_correct_picks 
---------+-------------------      4 |                47
(1 row)

football=# EXPLAIN SELECT play.play_id as play_id, year.correct_picks as
ytd_correct_picks FROM tblweek_correct correct, tblplayer play,
tblyear_correct year WHERE play.play_id = year.play_id AND play.play_id =
correct.play_id AND correct.game_week = 6 AND play.play_id = 4;
NOTICE:  QUERY PLAN:

Aggregate  (cost=108.40..108.41 rows=0 width=64) ->  Group  (cost=108.40..108.40 rows=1 width=64)       ->  Sort
(cost=108.40..108.40rows=1 width=64)             ->  Nested Loop  (cost=0.00..108.39 rows=1 width=64)
-> Nested Loop  (cost=0.00..106.36 rows=1 width=56)                         ->  Nested Loop  (cost=0.00..104.33 rows=1
 
width=52)                               ->  Nested Loop  (cost=0.00..16.54 rows=1
width=40)                                     ->  Nested Loop  (cost=0.00..14.75
rows=1 width=36)                                           ->  Nested Loop
(cost=0.00..13.30 rows=1 width=32)                                                 ->  Nested Loop
(cost=0.00..11.52 rows=1 width=28)                                                       ->  Nested Loop
(cost=0.00..5.11 rows=1 width=16)                                                             ->  Seq Scan
on tblgame game  (cost=0.00..2.08 rows=1 width=8)                                                             ->
Index
Scan using tblgame_winner_pkey on tblgame_winner winner  (cost=0.00..2.01
rows=1 width=8)                                                       ->  Index Scan
using tblpick_gameid_playid on tblpick pick  (cost=0.00..5.05 rows=3
width=12)                                                 ->  Seq Scan on
tblplayer play  (cost=0.00..1.35 rows=35 width=4)                                           ->  Seq Scan on tblplayer
play
(cost=0.00..1.44 rows=1 width=4)                                     ->  Seq Scan on tblplayer play
(cost=0.00..1.35 rows=35 width=4)                               ->  Seq Scan on tblpick pick
(cost=0.00..51.24 rows=2924 width=12)                         ->  Index Scan using tblgame_pkey on tblgame
game  (cost=0.00..2.01 rows=1 width=4)                   ->  Index Scan using tblgame_winner_pkey on
tblgame_winner winner  (cost=0.00..2.01 rows=1 width=8)

EXPLAIN
football=# EXPLAIN SELECT play.play_id as play_id, year.correct_picks as
ytd_correct_picks FROM tblplayer play, tblyear_correct year WHERE
play.play_id = year.play_id AND play.play_id = 4;
NOTICE:  QUERY PLAN:

Aggregate  (cost=101.12..101.12 rows=0 width=32) ->  Group  (cost=101.12..101.12 rows=1 width=32)       ->  Sort
(cost=101.12..101.12rows=1 width=32)             ->  Hash Join  (cost=96.27..101.11 rows=1 width=32)
-> Seq Scan on tblgame_winner winner
 
(cost=0.00..1.72 rows=72 width=8)                   ->  Hash  (cost=96.26..96.26 rows=4 width=24)
 ->  Hash Join  (cost=3.40..96.26 rows=4
 
width=24)                               ->  Nested Loop  (cost=0.00..91.02 rows=19
width=20)                                     ->  Nested Loop
(cost=0.00..3.23 rows=1 width=8)                                           ->  Seq Scan on tblplayer play
(cost=0.00..1.44 rows=1 width=4)                                           ->  Seq Scan on tblplayer play
(cost=0.00..1.35 rows=35 width=4)                                     ->  Seq Scan on tblpick pick
(cost=0.00..51.24 rows=2924 width=12)                               ->  Hash  (cost=1.86..1.86 rows=86
width=4)                                     ->  Seq Scan on tblgame game
(cost=0.00..1.86 rows=86 width=4)

EXPLAIN
football=# \d tblweek_correct      View "tblweek_correct"  Attribute   |  Type   | Modifier 
---------------+---------+----------play_id       | integer | game_week     | integer | correct_picks | integer | 
View definition: SELECT play.play_id, game.game_week, count(*) AS
correct_picks FROM tblgame game, tblpick pick, tblgame_winner winner,
tblplayer play WHERE ((((pick.play_id = play.play_id) AND (game.game_id =
pick.game_id)) AND (pick.team_id = winner.team_id)) AND (game.game_id =
winner.game_id)) GROUP BY play.play_id, game.game_week;

football=# \d tblyear_correct      View "tblyear_correct"  Attribute   |  Type   | Modifier 
---------------+---------+----------play_id       | integer | correct_picks | integer | 
View definition: SELECT play.play_id, count(*) AS correct_picks FROM
tblgame game, tblpick pick, tblgame_winner winner, tblplayer play WHERE
((((pick.play_id = play.play_id) AND (game.game_id =
pick.game_id)) AND (pick.team_id = winner.team_id)) AND (game.game_id =
winner.game_id)) GROUP BY play.play_id;

football=# \d tblplayer play                                  Table "tblplayer"  Attribute   |    Type     |
           Modifier                         
 
---------------+-------------+---------------------------------------------------------play_id       | integer     |
notnull default
 
nextval('tblplayer_play_id_seq'::text)play_name     | varchar(30) | not nullplay_username | varchar(16) | not
nullplay_password| varchar(16) | not nullplay_online   | boolean     | default 'f'
 
Indices: idx_play_username,        tblplayer_pkey

\d: extra argument 'play' ignored
football=# select version();                       version                         
--------------------------------------------------------PostgreSQL 7.0.2 on i386-pc-bsdi3.1, compiled by gcc2 
(1 row)