From: JanWieck@t-online.de
To: Thomas Holmgren <thm@cs.auc.dk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
Date: Wed, 24 May 2000 23:04:06 +0200 (MEST)
Message-ID: <200005242104.XAA18271@hot.jw.home> (raw)
In-Reply-To: <Pine.GSO.4.21.0005241958440.28326-100000@luke.cs.auc.dk>
Thomas Holmgren wrote:
>
> Hello everyone! :)
>
> This little problem is bothering me a lot! It seems that PostgreSQL 7.0
> uses different semantics than Oracle when evaluting SQL?!
Not that much, but ...
> [...]
>
> 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;
here the problems start. PostgreSQL has (since epoch)
problems with aggregates, GROUP BY clauses and some other
things when used in views. We know exactly what causes these
problems, but fixing them requires some huge changes across
the entire backend. This work is scheduled for the 7.2
release.
> 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.
Close, due to the fact that after applying the rewrite rules
for the views, the entire thing is one join, but with only
one (and thus wrong) gouping step on the toplevel. The
groupings must be done on deeper levels per view, but theres
no way to tell that in the querytree from the rewriter.
> How can I fix that??
> How come PostgreSQL uses different semantics when evaluating SQL
> expressions than other BDMSs?
You can help us doing the huge changes in a couple of months.
Even if you cannot help coding it, you might penetrate what
we do with all those complicated schemas.
Stay tuned.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
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: JanWieck@t-online.de, thm@cs.auc.dk
Subject: Re: Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
In-Reply-To: <200005242104.XAA18271@hot.jw.home>
References: <Pine.GSO.4.21.0005241958440.28326-100000@luke.cs.auc.dk> <200005242104.XAA18271@hot.jw.home>
* 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