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