> 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.