Mario Weilguni wrote:
This has been been discussed before, but Oracle behaves differently,
and
IMHO in a more correct way.
The following query returns NULL in PG:
SELECT NULL || 'fisk';
But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;
The latter seems more logical...
Hi, Martijn,
Martijn van Oosterhout wrote:
On Wed, Oct 18, 2006 at 11:36:44AM -0700, Jeff Davis wrote:
What's being suggested simply violates common sense. Basically:
if (a = b) then (a||c = b||c)
If a is 'x' and b is 'x' and c is NULL, the above statement doesn't hold
in PostgreSQL.
On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
The following query returns NULL in PG:
SELECT NULL || 'fisk';
But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;
The latter seems more logical...
Why would it be more logical ?
How many times do you *really*
On Wed, Oct 18, 2006 at 02:28:50PM +0200, Andreas Joseph Krogh wrote:
On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
The following query returns NULL in PG:
SELECT NULL || 'fisk';
But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;
The latter seems more
How many times do you *really* want to get the not known answer here
instead
of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?
All the time. If I would want the answer 'fisk', I would store ''
instead of NULL... your problem is that Oracle treats NULL as '' (empty
Martijn van Oosterhout wrote:
By following your suggestion we would get the following oddity:
SELECT NULL = '', NULL || 'fisk' = '' || 'fisk';
We would return NULL for the first and true for the second. Surely
that's not logical?
The problem is really that Oracle does not differntiate
Am Mittwoch, 18. Oktober 2006 13:52 schrieb Andreas Joseph Krogh:
This has been been discussed before, but Oracle behaves differently, and
IMHO in a more correct way.
The following query returns NULL in PG:
SELECT NULL || 'fisk';
But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM
Andreas Joseph Krogh wrote:
Why do these discussions always end in academic arguments over whats more
logical then not? From a *user's* point of view I really would like it to
treat the NULL operand of || as '', and obviously many other (at least
Oracle) users tend to agree with me on
Andreas Joseph Krogh wrote:
This has been been discussed before, but Oracle behaves differently, and IMHO
in a more correct way.
The following query returns NULL in PG:
SELECT NULL || 'fisk';
But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;
The latter seems more logical...
Why do these discussions always end in academic arguments over whats more
logical then not?
Because you asked the (rhetorical from your POV) question 'isn't this
more logical ?'
From a *user's* point of view I really would like it to
treat the NULL operand of || as '', and obviously many
Andreas Joseph Krogh wrote:
If aggregates ignore NULL one could argue that so shuld the ||-operator?
I agree that this behaviour may seem pedantic, but changing this is only
going to lead to a huge wtf? factor. The baviour for NULL in aggregates
is pretty well documented and known. Even
If you want this behaviour you will have to explicitly handle it with
COALESCE().
regards,
Lukas
True. But there's a point where oracle is really better here, they named
coalesce nvl = a lot easier to type ;-)
---(end of broadcast)---
TIP 3: Have
On Wednesday 18 October 2006 15:13, Andrew Dunstan wrote:
Andreas Joseph Krogh wrote:
This has been been discussed before, but Oracle behaves differently, and
IMHO in a more correct way.
The following query returns NULL in PG:
SELECT NULL || 'fisk';
But in Oracle, it returns 'fisk':
On Wednesday 18 October 2006 15:15, Mario Weilguni wrote:
If you want this behaviour you will have to explicitly handle it with
COALESCE().
regards,
Lukas
True. But there's a point where oracle is really better here, they named
coalesce nvl = a lot easier to type ;-)
They actually
Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even harder to
type.
-Ursprüngliche Nachricht-
Von: Andreas Joseph Krogh [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 18. Oktober 2006 15:48
An: pgsql-hackers@postgresql.org
Cc: Mario Weilguni
Betreff: Re: [HACKERS] bug
Andreas Joseph Krogh wrote:
When in doubt, consult the standard ... Oracle's treatment of NULL is
known to violate the standard, IIRC. Your measure of correctness seems
to be appears to me more logical, but ours is complies with the
standard.
I know PG violates the standard in other
On Wed, Oct 18, 2006 at 03:44:05PM +0200, Andreas Joseph Krogh wrote:
When in doubt, consult the standard ... Oracle's treatment of NULL is
known to violate the standard, IIRC. Your measure of correctness seems
to be appears to me more logical, but ours is complies with the
standard.
I
Mario Weilguni wrote:
Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even harder to
type.
amen .. coalesce was invented by a sadistic twit (something which people
have also called me .. so it goes).
regards,
Lukas
---(end of
Lukas Kahwe Smith wrote:
Mario Weilguni wrote:
Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even
harder to type.
amen .. coalesce was invented by a sadistic twit (something which
people have also called me .. so it goes).
Perhaps people are trying to pronounce it
Yes it's hard for me, maybe because I am no native english speaker.
-Ursprüngliche Nachricht-
Von: Andrew Dunstan [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 18. Oktober 2006 16:11
An: Lukas Kahwe Smith
Cc: Mario Weilguni; pgsql-hackers@postgresql.org
Betreff: Re: [HACKERS] bug
On Wed, 2006-10-18 at 16:15, Mario Weilguni wrote:
Yes it's hard for me, maybe because I am no native english speaker.
Considering the pure latin origin of the word, that's a funny argument
:-)
BTW, I pronounce it as an Italian would (that would be the closest to
it's origins):
On Wed, 2006-10-18 at 15:44 +0200, Andreas Joseph Krogh wrote:
I'm not advocating that NULL should have a string-vaule of anything, just
that
the ||-operator shuld treat NULL as dont bother with it and proceed
concatenation.
Not only is the current behavior more logical (IMHO) and backward
Neil Conway wrote:
I think a more sensible proposal could be made for some sort of optional
compatibility mode, as has been discussed many times in the past:
different NULL handling could theoretically be part of an Oracle SQL
dialect.
even more exciting in this context would be to add user
even more exciting in this context would be to add user controllable
NULL sorting behaviour. afaik this is in sql:2003.
ORDER BY .. [ NULLS (FIRST|LAST) ] ?
Wait a bit :), I'm waiting for separate 8.2 branch.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
The following query returns NULL in PG:
SELECT NULL || 'fisk';
But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;
The latter seems more logical...
Why would it be more logical ?
NULL means value not known.
Concatenate value not known with 'fisk' - what's the logical
Am Mittwoch, 18. Oktober 2006 15:07 schrieb Andreas Joseph Krogh:
Why do these discussions always end in academic arguments over whats more
logical then not?
Because that is ultimately the reason why SQL behaves the way it does. I'm
sure we could all come up with a long list of behaviors that
On Wednesday 18 October 2006 14:44, Mario Weilguni wrote:
Am Mittwoch, 18. Oktober 2006 13:52 schrieb Andreas Joseph Krogh:
This has been been discussed before, but Oracle behaves differently, and
IMHO in a more correct way.
The following query returns NULL in PG:
SELECT NULL || 'fisk';
On Wed, 2006-10-18 at 15:57 +0200, Martijn van Oosterhout wrote:
On Wed, Oct 18, 2006 at 03:44:05PM +0200, Andreas Joseph Krogh wrote:
When in doubt, consult the standard ... Oracle's treatment of NULL is
known to violate the standard, IIRC. Your measure of correctness seems
to be
Kahwe Smith
Cc: Mario Weilguni; pgsql-hackers@postgresql.org
Betreff: Re: [HACKERS] bug or feature, || -operator and NULLs
Lukas Kahwe Smith wrote:
Mario Weilguni wrote:
Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even
harder to type.
amen .. coalesce was invented
On Wed, Oct 18, 2006 at 11:36:44AM -0700, Jeff Davis wrote:
What's being suggested simply violates common sense. Basically:
if (a = b) then (a||c = b||c)
If a is 'x' and b is 'x' and c is NULL, the above statement doesn't hold
in PostgreSQL.
Heh, well, c is supposed to be not NULL.
On Wed, 2006-10-18 at 14:28 +0200, Andreas Joseph Krogh wrote:
On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
The following query returns NULL in PG:
SELECT NULL || 'fisk';
But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;
The latter seems more
On Wednesday 18 October 2006 10:35, Lukas Kahwe Smith wrote:
Neil Conway wrote:
I think a more sensible proposal could be made for some sort of optional
compatibility mode, as has been discussed many times in the past:
different NULL handling could theoretically be part of an Oracle SQL
32 matches
Mail list logo