From: Thomas Holmgren <thm@cs.auc.dk>
To: pgsql-sql@postgresql.org
Subject: Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
Date: Wed, 24 May 2000 17:08:50 GMT
Message-ID: <Pine.GSO.4.21.0005241908350.17175-100000@borg.cs.auc.dk> (raw)
Hello everyone! :)
This little problem is bothering me a lot! It seems that PostgreSQL 7.0
uses different semantics than Oracle when evaluting SQL?!
I have two relations, A and B, both containing the attributes "number"
(int) and "amount" (int). There's no primary key, and the two relations
can contain multiple identical tuples.
I would like to query for a table containing the total amount for each
different number in B, substracted from the total amount for each
different number in A. In other words, sum A by grouping "number", sum B
by grouping "number" and finaly calculate the difference between the sums
for each "number".
I have defined two views, viewA and viewB. They are defined as follow:
CREATE VIEW viewA AS SELECT number, sum(amount) AS amount
FROM A GROUP BY number;
CREATE VIEW viewB AS SELECT number, sum(amount) AS amount
FROM B FROUP BY number;
This query then gives me the desired result (when I'm using Oracle):
SELECT viewA.number, viewA.amount - viewB.amount AS difference
FROM viewA, viewB
WHERE viewA.number = viewB.number
BUT WHEN I'm doing the EXACT SAME THING in PostgreSQL 7.0 I get a
different result! It seems that Postgres executes the natural join in the
query BEFORE performing the sum() in the definition of the views thus
giving me a wrong result.
How can I fix that??
How come PostgreSQL uses different semantics when evaluating SQL
expressions than other BDMSs?
Thank you! :))
Mvh.
Thomas Holmgren
Institut for Datalogi
Aalborg Universitet
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: thm@cs.auc.dk
Subject: Re: Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
In-Reply-To: <Pine.GSO.4.21.0005241908350.17175-100000@borg.cs.auc.dk>
References: <Pine.GSO.4.21.0005241908350.17175-100000@borg.cs.auc.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox