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.

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/

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:

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:

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:

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

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 ;-)

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.

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/

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/

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