: [GENERAL] greatest/least semantics different between oracle
and postgres
Maybe that reference was for an earlier version of Oracle and the
definition
changed at some point? I only have access to version 9 and greatest and
lest are strict there.
I am installing OracleXE
Hello,
I have not Oracle, so I cannot test it, but PostgreSQL implementation
respect Oracle:
http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php
Regards
Pavel Stehule
2007/6/30, Bruno Wolff III [EMAIL PROTECTED]:
The following is just FYI.
I was recently doing some stuff with
Bruno Wolff III [EMAIL PROTECTED] writes:
Also what value should I have used in a coalesce to guaranty still getting
the maximum?
I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's
not terribly legible though and if a and b are subselects I would worry a
little
Tom Lane [EMAIL PROTECTED] writes:
GREATEST/LEAST aren't in the spec, so there's not much help there.
Except ... if they ever do get added to the spec, what do you think
the spec will say? The odds it'd contradict Oracle seem about nil.
Fwiw even in the min/max/sum case the spec is moving
Gregory Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
GREATEST/LEAST aren't in the spec, so there's not much help there.
Except ... if they ever do get added to the spec, what do you think
the spec will say? The odds it'd contradict Oracle seem about nil.
Fwiw even in the
Gregory Stark [EMAIL PROTECTED] writes:
Bruno Wolff III [EMAIL PROTECTED] writes:
Also what value should I have used in a coalesce to guaranty still getting
the maximum?
I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's
not terribly legible though and if a and b
Gregory Stark [EMAIL PROTECTED] writes:
Fwiw even in the min/max/sum case the spec is moving away from having
aggregates ignore NULL values. You now get a warning in Oracle if your
aggregate includes any NULL inputs.
I don't think there's any moving involved; as far back as SQL92 the
Bruce Momjian [EMAIL PROTECTED] writes:
Fwiw even in the min/max/sum case the spec is moving away from having
aggregates ignore NULL values. You now get a warning in Oracle if your
aggregate includes any NULL inputs.
How does Oracle's new behavior relate to the standard moving?
Sorry I
Tom Lane [EMAIL PROTECTED] writes:
Gregory Stark [EMAIL PROTECTED] writes:
Bruno Wolff III [EMAIL PROTECTED] writes:
Also what value should I have used in a coalesce to guaranty still getting
the maximum?
I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want.
It's
not
Tom Lane wrote:
Gregory Stark [EMAIL PROTECTED] writes:
Fwiw even in the min/max/sum case the spec is moving away from having
aggregates ignore NULL values. You now get a warning in Oracle if your
aggregate includes any NULL inputs.
I don't think there's any moving involved; as far back
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
Anyway, there's no doubt that we can point to the behavior of MAX/MIN
as defense for what we made GREATEST/LEAST do, so I'm inclined to leave
their behavior alone, at least until such time as they're actually
standardized. But a note in
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
Anyway, there's no doubt that we can point to the behavior of MAX/MIN
as defense for what we made GREATEST/LEAST do, so I'm inclined to leave
their behavior alone, at least until such time as they're actually
Tom Lane wrote:
Anyway, there's no doubt that we can point to the behavior of MAX/MIN
as defense for what we made GREATEST/LEAST do, so I'm inclined to
leave
their behavior alone, at least until such time as they're actually
standardized.
I don't think I buy this - MIN and MAX are
On Sat, Jun 30, 2007 at 09:29:23 +0200,
Pavel Stehule [EMAIL PROTECTED] wrote:
Hello,
I have not Oracle, so I cannot test it, but PostgreSQL implementation
respect Oracle:
http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php
Maybe that reference was for an earlier version
We pretty much ignore the spec's concept of non-error completion
conditions, but it sounds like Oracle tries to support it.
Anyway, there's no doubt that we can point to the behavior of MAX/MIN
as defense for what we made GREATEST/LEAST do, so I'm inclined to leave
their behavior alone, at
Maybe that reference was for an earlier version of Oracle and the definition
changed at some point? I only have access to version 9 and greatest and
lest are strict there.
I am installing OracleXE and I'll test it.
Pavel
---(end of
Sent: Saturday, June 30, 2007 10:37 AM
To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org
Subject: Re: [GENERAL] greatest/least semantics different between oracle
and postgres
Maybe that reference was for an earlier version of Oracle and the
definition
changed at some point
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Pavel Stehule
Sent: Saturday, June 30, 2007 10:37 AM
To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org
Subject: Re: [GENERAL] greatest/least semantics different between
Sent: Saturday, June 30, 2007 10:37 AM
To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org
Subject: Re: [GENERAL] greatest/least semantics different between oracle
and postgres
Maybe that reference was for an earlier version of Oracle and the
definition
changed at some point? I
The following is just FYI.
I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and
noticed that it returned null if ANY of the arguments were null. Out of
curiosity I checked postgres' definition of that function and found that it
returns null only if ALL of the arguments are
On 6/30/07, Bruno Wolff III [EMAIL PROTECTED] wrote:
The following is just FYI.
I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and
noticed that it returned null if ANY of the arguments were null. Out of
curiosity I checked postgres' definition of that function and found
Andrej Ricnik-Bay [EMAIL PROTECTED] writes:
On 6/30/07, Bruno Wolff III [EMAIL PROTECTED] wrote:
I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and
noticed that it returned null if ANY of the arguments were null. Out of
curiosity I checked postgres' definition of that
On 6/30/07, Tom Lane [EMAIL PROTECTED] wrote:
Hmm ... I fear Oracle's behavior is more correct, because if any
argument is null (ie, unknown), then who can say what the greatest or
least value is? It's unknown (ie, null). But I suspect our behavior
is more useful. Comments?
But in min/max
On Sat, Jun 30, 2007 at 00:15:42 -0400,
Tom Lane [EMAIL PROTECTED] wrote:
Andrej Ricnik-Bay [EMAIL PROTECTED] writes:
On 6/30/07, Bruno Wolff III [EMAIL PROTECTED] wrote:
I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and
noticed that it returned null if ANY of the
On Jun 29, 2007, at 9:15 PM, Tom Lane wrote:
Hmm ... I fear Oracle's behavior is more correct, because if any
argument is null (ie, unknown), then who can say what the greatest or
least value is? It's unknown (ie, null). But I suspect our behavior
is more useful. Comments?
I agree with
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Ben
Sent: Friday, June 29, 2007 10:18 PM
To: Tom Lane
Cc: PostgreSQL General ((EN))
Subject: Re: [GENERAL] greatest/least semantics different between oracle
and postgres
On Jun 29, 2007
Ben [EMAIL PROTECTED] writes:
On Jun 29, 2007, at 9:15 PM, Tom Lane wrote:
Hmm ... I fear Oracle's behavior is more correct, because if any
argument is null (ie, unknown), then who can say what the greatest or
least value is? It's unknown (ie, null). But I suspect our behavior
is more
paul rivers [EMAIL PROTECTED] writes:
But I question the original poster's report of Oracle's behavior. I don't
have 9.2.0.8 to test, but on 9.2.0.7:
Er ... your example doesn't actually seem to involve greatest() or
least()?
regards, tom lane
Er ... your example doesn't actually seem to involve greatest() or
least()?
So sorry, it's been a long day, I misread. Yes, greatest/least definitely
does work on Oracle as the OP said. Apologies again.
---(end of broadcast)---
TIP 4:
29 matches
Mail list logo