
Okay, not sure if this is a bug in v6.5.3 or v7.0, but the same query
running on the *same* data, but v6.5.3 vs 7.0 ...

SELECT pl.code,p.description,p.price
  FROM po pu,products p, po_list pl
 WHERE pl.po_num = 118
   AND pl.code = p.code
ORDER BY p.description;


produces 2 records (expected) under v6.5.3) but under v7 produces 224
... what it appears to do is repeat each of those 2 records 112 times,
which is the size of the 'po' table ...

Since we aren't actually *using* the 'po' table in that query, I removed
it in the v7.0 system and it comes back with the two records I'm expecting
...

So, my first guess is that v6.5.3 was less strict as far as tables listed
in the FROM directive then v7.0, so it just ignored it if it wasn't
actually used ... but want to make sure it *isn't* a bug we've introduced
with v7.0 ...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 11 May 2000 10:18:15 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
query results different in v7.0 vs v6.5.3 ...
The Hermit Hacker <scrappy@hub.org> writes:
> Okay, not sure if this is a bug in v6.5.3 or v7.0, but the same query
> running on the *same* data, but v6.5.3 vs 7.0 ...

> SELECT pl.code,p.description,p.price
>   FROM po pu,products p, po_list pl
>  WHERE pl.po_num = 118
>    AND pl.code = p.code
> ORDER BY p.description;

> produces 2 records (expected) under v6.5.3) but under v7 produces 224
> ... what it appears to do is repeat each of those 2 records 112 times,
> which is the size of the 'po' table ...

> Since we aren't actually *using* the 'po' table in that query, I removed
> it in the v7.0 system and it comes back with the two records I'm expecting

> So, my first guess is that v6.5.3 was less strict as far as tables listed
> in the FROM directive then v7.0, so it just ignored it if it wasn't
> actually used ... but want to make sure it *isn't* a bug we've introduced
> with v7.0 ...

No, this is a bug we *removed* in 7.0.  Since the query is joining 'po'
with no join constraint, you ought to get a cross-product result.
Earlier versions dropped 'po' from the join because it wasn't explicitly
referred to elsewhere in the query, but I can't see any way that that's
correct behavior under SQL92.

			regards, tom lane

Thu, 11 May 2000 13:11:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: query results different in v7.0 vs v6.5.3 ... 