Re: 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