Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-23 Thread Albe Laurenz
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...

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-20 Thread Markus Schaber
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.

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
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*

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Martijn van Oosterhout
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Csaba Nagy
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Lukas Kahwe Smith
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Mario Weilguni
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Alvaro Herrera
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andrew Dunstan
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...

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Csaba Nagy
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Lukas Kahwe Smith
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Mario Weilguni
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
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':

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Mario Weilguni
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andrew Dunstan
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Martijn van Oosterhout
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Lukas Kahwe Smith
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andrew Dunstan
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Mario Weilguni
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Csaba Nagy
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):

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Neil Conway
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Lukas Kahwe Smith
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Teodor Sigaev
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]

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Csaba Nagy
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Peter Eisentraut
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
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';

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Jeff Davis
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Jim C. Nasby
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Martijn van Oosterhout
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.

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Jeff Davis
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

Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Robert Treat
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