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.40 rows=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.12 rows=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     | not null default
nextval('tblplayer_play_id_seq'::text)
 play_name     | varchar(30) | not null
 play_username | varchar(16) | not null
 play_password | varchar(16) | not null
 play_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)



Sun, 8 Oct 2000 16:56:44 -0400 (EDT)
Michael Fork <mfork@toledolink.com>
Query returning incorrect results
Michael Fork <mfork@toledolink.com> writes:
> 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...

Grouped views don't really work in 7.0.* or prior releases, except in
the very simplest cases.  The query rewriter basically just does "macro
expansion" of the view into your query, so unless you can write out the
equivalent query as valid SQL without using views, it won't work.  In
particular, selecting simultaneously from two views with different
grouping requirements cannot work, because there's only one GROUP BY
phase available.  Unfortunately, the rewriter is too stupid to notice
that it can't produce a correct translation :-(

This is fixed in current sources --- a grouped view will be done as
a true sub-query.

			regards, tom lane

Sun, 08 Oct 2000 22:41:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Query returning incorrect results 