Обсуждение: bug or feature, || -operator and NULLs
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... -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
> 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 answer? I would say the logical result is 'value not known'... if one of the components is not known, how can you know what is the result ? Cheers, Csaba.
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* want to get the "not known" answer here instead of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'? > NULL means "value not known". I know. > Concatenate "value not known" with 'fisk' -> what's the logical answer? > > I would say the logical result is 'value not known'... if one of the > components is not known, how can you know what is the result ? That's like saying: SELECT sum(field) should return NULL(value not known) if some of the tuples are NULL, which is definitly not what you want. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
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 logical... > 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'? In general, if you pass a NULL to a function, you get a NULL return. An operator is just a function call. IIRC, this works on oracle too: SELECT NULL = ''; returns true. On postgresql it return null (sql standard). 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? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
> 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 string), so even if you insert an empty string it will end up as NULL, that's why they HAVE TO give the result you say it's more logical. > That's like saying: SELECT sum(field) should return NULL(value not known) if > some of the tuples are NULL, which is definitly not what you want. And it would really return null, if aggregates wouldn't ignore the NULL values altogether... the null values are skipped before they get into the summing. The same happens with count, if you specify a column it will only count the ones which are not null: cnagy=# create table test_null(a integer); CREATE TABLE cnagy=# insert into test_null values (1); INSERT 0 1 cnagy=# insert into test_null values (null); INSERT 0 1 cnagy=# insert into test_null values (2); INSERT 0 1 cnagy=# select sum(a) from test_null;sum ----- 3 (1 row) cnagy=# select count(a) from test_null;count ------- 2 (1 row) But: cnagy=# select (1 + 2 + null) is null;?column? ----------t (1 row) Cheers, Csaba.
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 properly between '' and NULL. regards, Lukas
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 DUAL; > > The latter seems more logical... I've worked alot with oracle a few years ago and I agree, the feature is handy and makes sometimes life easier, but it's simply wrong. I heard a while ago that newer oracle versions changed this to sql - standard, is this true?
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'; > > > > But in Oracle, it returns 'fisk': > > SELECT NULL || 'fisk' FROM DUAL; > > > > The latter seems more logical... > > I've worked alot with oracle a few years ago and I agree, the feature is > handy and makes sometimes life easier, but it's simply wrong. I heard a > while ago that newer oracle versions changed this to sql - standard, is > this true? Oracle(10.1.0.4.0) still treats '' as NULL. 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 that. On Wednesday 18 October 2006 14:42, Csaba Nagy wrote: > And it would really return null, if aggregates wouldn't ignore the NULL > values altogether... the null values are skipped before they get into > the summing. The same happens with count, if you specify a column it > will only count the ones which are not null: If aggregates ignore NULL one could argue that so shuld the ||-operator? -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
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 MySQL returns NULL in this case, and they are known todo all sorts of changes for better "ease of use". If you want this behaviour you will have to explicitly handle it with COALESCE(). regards, Lukas
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 that. So coalesce the column to the empty string if that's what you want: select coalesce(NULL, '') || 'fisk' will get you 'fisk'. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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... > > 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". In any case, why should null have a string value of '' any more than it should have a value of 'blurfl'? Your analogy elsewhere with aggregate functions like sum() is not relevant, as these are documented to ignore null values. cheers andrew
> 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 other (at least > Oracle) users tend to agree with me on that. They have to, otherwise they can't meaningfully concatenate an empty string to anything in Oracle, because there's no such thing in Oracle... empty string = NULL in Oracle, which is the real cause of the problem. We've been bitten by this on Oracle before. > If aggregates ignore NULL one could argue that so shuld the ||-operator? OK, this is more complicated I guess, check out the rules related to 'strict' state transition functions in: http://www.postgresql.org/docs/8.1/static/sql-createaggregate.html Basically, if you like, you could define a 'my_sum' aggregate which does not ignore nulls. Or you can define an operator which treats NULLs as empty string if you like... Cheers, Csaba.
> >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 ;-)
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': > > SELECT NULL || 'fisk' FROM DUAL; > > > > The latter seems more logical... > > 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 places and core's favourite argument for doing so is "the standard is braindead here, so we do it our way". > In any case, why should null have a string value of '' any more than it > should have a value of 'blurfl'? > > Your analogy elsewhere with aggregate functions like sum() is not > relevant, as these are documented to ignore null values. 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". -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
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 support COALESCE now and explicit JOINs too. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even harder to type. -----Ursprüngliche Nachricht----- Von: Andreas Joseph Krogh [mailto:andreak@officenet.no] Gesendet: Mittwoch, 18. Oktober 2006 15:48 An: pgsql-hackers@postgresql.org Cc: Mario Weilguni Betreff: Re: [HACKERS] bug or feature, || -operator and NULLs 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 support COALESCE now and explicit JOINs too. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+
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 places and core's favourite argument > for doing so is "the standard is braindead here, so we do it our way". > > In very few places. If you think that an argument like that will make us break well established standards-compliant behaviour, you are surely sadly mistaken. cheers andrew
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 know PG violates the standard in other places and core's favourite argument > for doing so is "the standard is braindead here, so we do it our way". But they're few and far between and not on things people actually notice much. What's being suggested simply violates common sense. Basically: if (a = b) then (a||c = b||c) That seems a perfectly good rule, which works for both Oracle and PostgreSQL. Breaking seems to be a bad idea all round. > 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". I would argue it's inconsistant. No other function treats a NULL like an empty string, so I really don't see why textcat() should. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
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
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 wrongly. According to m-w, the right ways is: Pronunciation: "kO-&-'les or more informally "koh a less". Is that really so hard? cheers andrew
Yes it's hard for me, maybe because I am no native english speaker. -----Ursprüngliche Nachricht----- Von: Andrew Dunstan [mailto:andrew@dunslane.net] Gesendet: Mittwoch, 18. Oktober 2006 16:11 An: Lukas 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 by a sadistic twit (something which > people have also called me .. so it goes). Perhaps people are trying to pronounce it wrongly. According to m-w, the right ways is: Pronunciation: "kO-&-'les or more informally "koh a less". Is that really so hard? cheers andrew
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): "koh-ah-less-cheh" (I'm not sure if I got the sounds right for native english speakers, I'm also not one of them ;-). Cheers, Csaba.
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 compatible with existing Postgres application, it is consistent with the SQL spec and most non-broken implementations of it. I think your chances of getting the default behavior changed are slim indeed. 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. -Neil
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 controllable NULL sorting behaviour. afaik this is in sql:2003. regards, Lukas
> 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: teodor@sigaev.ru WWW: http://www.sigaev.ru/
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 would be "useful" or "better" or "intuitive", and we could also come up with a long list of reasons why such a database system would be totally crappy. You only need to check with our friends in Uppsala for some examples. -- Peter Eisentraut http://developer.postgresql.org/~petere/
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 "appears to me more logical", but ours is "complies with the > > > standard". > > > > I know PG violates the standard in other places and core's favourite argument > > for doing so is "the standard is braindead here, so we do it our way". > > But they're few and far between and not on things people actually > notice much. > > 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. I'm not disagreeing with your overall point, I'm just missing what you meant by the above statement. What are a, b, and c supposed to be? Regards,Jeff Davis
Yes, well, we english speakers get to deal with the monstrosity that is 'www'. :) In any case, I believe coalesce is in the standard, and even if it's not, Oracle is the only database I know of that doesn't use it. If you're that unhappy with coalesce and ||, you can always create functions that will do what you want. On Wed, Oct 18, 2006 at 04:15:00PM +0200, Mario Weilguni wrote: > Yes it's hard for me, maybe because I am no native english speaker. > > -----Urspr?ngliche Nachricht----- > Von: Andrew Dunstan [mailto:andrew@dunslane.net] > Gesendet: Mittwoch, 18. Oktober 2006 16:11 > An: Lukas 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 by a sadistic twit (something which > > people have also called me .. so it goes). > > Perhaps people are trying to pronounce it wrongly. According to m-w, the right ways is: > > Pronunciation: "kO-&-'les > > > or more informally "koh a less". > > Is that really so hard? > > cheers > > andrew > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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. Missed that. I was using the equals to include (NULL = NULL) but in SQL it's not like that. > I'm not disagreeing with your overall point, I'm just missing what you > meant by the above statement. What are a, b, and c supposed to be? I was trying to point out that what was being proposed was: NULL || 'bar' => 'bar' '' || 'bar' => 'bar' But NULL is not an empty string. Oracle chooses to make NULL and the empty string the same, we don't. So logically we shouldn't treat them the same for text concatination either. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
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 logical... > > > > Why would it be more logical ? > > 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'? > When you pass the result to an aggregate function. Example: => create table test(days int); CREATE TABLE => insert into test values(1); INSERT 0 1 => insert into test values(2); INSERT 0 1 => insert into test values(NULL); INSERT 0 1 => select sum((days::text||' days')::interval) from test; sum --------3 days (1 row) => select sum((coalesce(days::text,'')||' days')::interval) from test; ERROR: invalid input syntax for type interval: " days" The last query represents the "auto-coalescing" behavior you are looking for. However, it creates an error on a query that is perfectly valid. Regards,Jeff Davis
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
> > dialect.
>
> even more exciting in this context would be to add user controllable
> NULL sorting behaviour. afaik this is in sql:2003.
>
Something like
pagila=# select staff_id from staff order by picture is not null;staff_id
---------- 2 1
(2 rows)
pagila=# select staff_id from staff order by picture;staff_id
---------- 1 2
(2 rows)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
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. > > Heh, well, c is supposed to be not NULL. Missed that. I was using the > equals to include (NULL = NULL) but in SQL it's not like that. Maybe you should replace "=" with "IS NOT DISTINCT FROM" :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
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... > > I've worked alot with oracle a few years ago and I agree, the feature is handy > and makes sometimes life easier, but it's simply wrong. I heard a while ago > that newer oracle versions changed this to sql - standard, is this true? Unfortunately not, in Oracle's current version (10.2.0.2.0) it is still that way. I think that this Oracle 'feature' is almost as terrible as the fact that they treat '' as NULL, which is (as has been pointed out) most likely the reason for treating NULL as '' in ||. Yours, Laurenz Albe