pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
3+ messages / 2 participants
[nested] [flat]

* Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
@ 2000-05-24 17:08  Thomas Holmgren <thm@cs.auc.dk>
  0 siblings, 0 replies; 3+ messages in thread

From: Thomas Holmgren @ 2000-05-24 17:08 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
@ 2000-05-24 17:58  Thomas Holmgren <thm@cs.auc.dk>
  0 siblings, 1 reply; 3+ messages in thread

From: Thomas Holmgren @ 2000-05-24 17:58 UTC (permalink / raw)
  To: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Why is PostgreSQL 7.0 SQL semantics different from Oracle's?
@ 2000-05-24 21:04  Jan Wieck <JanWieck@t-online.de>
  parent: Thomas Holmgren <thm@cs.auc.dk>
  0 siblings, 0 replies; 3+ messages in thread

From: JanWieck@t-online.de @ 2000-05-24 21:04 UTC (permalink / raw)
  To: Thomas Holmgren <thm@cs.auc.dk>; +Cc: pgsql-sql

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 #





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-05-24 21:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-24 17:08 Why is PostgreSQL 7.0 SQL semantics different from Oracle's? Thomas Holmgren <thm@cs.auc.dk>
2000-05-24 17:58 Why is PostgreSQL 7.0 SQL semantics different from Oracle's? Thomas Holmgren <thm@cs.auc.dk>
2000-05-24 21:04 ` Jan Wieck <JanWieck@t-online.de>

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