query results different in v7.0 vs v6.5.3 ...
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
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, was a bug in 6.5
Andreas
Import Notes
Resolved by subject fallback
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