Received: from news.tht.net (news.hub.org [216.126.91.242]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA12027 for ; Wed, 24 May 2000 13:08:56 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id NAA24315 for pgsql-sql@postgresql.org; Wed, 24 May 2000 13:08:56 -0400 (EDT) (envelope-from news) X-Authentication-Warning: news.tht.net: news set sender to using -f X-Newsgroups: comp.databases.postgresql.sql From: Thomas Holmgren Subject: Why is PostgreSQL 7.0 SQL semantics different from Oracle's? Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Lines: 47 Date: Wed, 24 May 2000 17:08:50 GMT X-Trace: twister.sunsite.auc.dk 959188130 130.225.194.125 (Wed, 24 May 2000 19:08:50 MET DST) Organization: "SunSITE Denmark -- sunsite.auc.dk To: pgsql-sql@postgresql.org X-Archive-Number: 200005/119 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