Thread: bug or feature, || -operator and NULLs

bug or feature, || -operator and NULLs

From
Andreas Joseph Krogh
Date:
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 |                                             |
------------------------+---------------------------------------------+


Re: bug or feature, || -operator and NULLs

From
Csaba Nagy
Date:
> 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.




Re: bug or feature, || -operator and NULLs

From
Andreas Joseph Krogh
Date:
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 |                                             |
------------------------+---------------------------------------------+


Re: bug or feature, || -operator and NULLs

From
Martijn van Oosterhout
Date:
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.

Re: bug or feature, || -operator and NULLs

From
Csaba Nagy
Date:
> 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.




Re: bug or feature, || -operator and NULLs

From
Lukas Kahwe Smith
Date:
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


Re: bug or feature, || -operator and NULLs

From
Mario Weilguni
Date:
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?



Re: bug or feature, || -operator and NULLs

From
Andreas Joseph Krogh
Date:
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 |                                             |
------------------------+---------------------------------------------+


Re: bug or feature, || -operator and NULLs

From
Lukas Kahwe Smith
Date:
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



Re: bug or feature, || -operator and NULLs

From
Alvaro Herrera
Date:
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.


Re: bug or feature, || -operator and NULLs

From
Andrew Dunstan
Date:
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


Re: bug or feature, || -operator and NULLs

From
Csaba Nagy
Date:
> 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.





Re: bug or feature, || -operator and NULLs

From
"Mario Weilguni"
Date:
>
>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 ;-)


Re: bug or feature, || -operator and NULLs

From
Andreas Joseph Krogh
Date:
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 |                                             |
------------------------+---------------------------------------------+


Re: bug or feature, || -operator and NULLs

From
Andreas Joseph Krogh
Date:
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 |                                             |
------------------------+---------------------------------------------+


Re: bug or feature, || -operator and NULLs

From
"Mario Weilguni"
Date:
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 |                                             |
------------------------+---------------------------------------------+


Re: bug or feature, || -operator and NULLs

From
Andrew Dunstan
Date:
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


Re: bug or feature, || -operator and NULLs

From
Martijn van Oosterhout
Date:
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.

Re: bug or feature, || -operator and NULLs

From
Lukas Kahwe Smith
Date:
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


Re: bug or feature, || -operator and NULLs

From
Andrew Dunstan
Date:
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




Re: bug or feature, || -operator and NULLs

From
"Mario Weilguni"
Date:
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




Re: bug or feature, || -operator and NULLs

From
Csaba Nagy
Date:
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.




Re: bug or feature, || -operator and NULLs

From
Neil Conway
Date:
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




Re: bug or feature, || -operator and NULLs

From
Lukas Kahwe Smith
Date:
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


Re: bug or feature, || -operator and NULLs

From
Teodor Sigaev
Date:
> 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/
 


Re: bug or feature, || -operator and NULLs

From
Peter Eisentraut
Date:
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/


Re: bug or feature, || -operator and NULLs

From
Jeff Davis
Date:
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



Re: bug or feature, || -operator and NULLs

From
"Jim C. Nasby"
Date:
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)


Re: bug or feature, || -operator and NULLs

From
Martijn van Oosterhout
Date:
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.

Re: bug or feature, || -operator and NULLs

From
Jeff Davis
Date:
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




Re: bug or feature, || -operator and NULLs

From
Robert Treat
Date:
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


Re: bug or feature, || -operator and NULLs

From
Markus Schaber
Date:
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


Re: bug or feature, || -operator and NULLs

From
"Albe Laurenz"
Date:
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