Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] bug or feature, || -operator and NULLs
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 TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] bug or feature, || -operator and NULLs
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 [EMAIL PROTECTED] 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 | | +-+ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] bug or feature, || -operator and NULLs
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. signature.asc Description: Digital signature
Re: [HACKERS] bug or feature, || -operator and NULLs
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] bug or feature, || -operator and NULLs
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? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] bug or feature, || -operator and NULLs
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bug or feature, || -operator and NULLs
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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug or feature, || -operator and NULLs
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 you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug or feature, || -operator and NULLs
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 [EMAIL PROTECTED] 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 | | +-+ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 [EMAIL PROTECTED] 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 | | +-+ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] bug or feature, || -operator and NULLs
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 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 [EMAIL PROTECTED] 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 | | +-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] bug or feature, || -operator and NULLs
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. signature.asc Description: Digital signature
Re: [HACKERS] bug or feature, || -operator and NULLs
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 broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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 6: explain analyze is your friend
Re: [HACKERS] bug or feature, || -operator and NULLs
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 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
Re: [HACKERS] bug or feature, || -operator and NULLs
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] bug or feature, || -operator and NULLs
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] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug or feature, || -operator and NULLs
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] bug or feature, || -operator and NULLs
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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug or feature, || -operator and NULLs
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 [EMAIL PROTECTED] 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 | | +-+ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] bug or feature, || -operator and NULLs
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:[EMAIL PROTECTED] 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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] bug or feature, || -operator and NULLs
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. signature.asc Description: Digital signature
Re: [HACKERS] bug or feature, || -operator and NULLs
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug or feature, || -operator and NULLs
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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
