FAQ
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

Search Discussions

  • Tom Lane at May 11, 2000 at 5:11 pm

    The Hermit Hacker 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 11, '00 at 1:18p
activeMay 11, '00 at 5:11p
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

The Hermit Hacker: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase