Null Values
Venkateswara Reddy, April 13, 2004 - 4:03 am UTC
Tom
Its realy very good.
Thanks
Reddy
1 + null <> sum(1, null)
Jim Dickson, November 05, 2004 - 5:59 am UTC
To me, the behaviour below is inconsistent but I am sure you can provide an explanation.
It appears SUM function and addition operator behave differently (at least when datatype not specified).
Tested on 9.2.0.5.0 Standard on Solaris 2.8
I am sure ANSI say something like null operator operand = null.
jdickson@DV01> select sum(a) from
2 (
3 select 1 a from dual
4 union all
5 select null a from dual
6 )
7 ;
SUM(A)
----------
1
jdickson@DV01>
jdickson@DV01> select 1 + null from dual;
1+NULL
----------
November 05, 2004 - 4:25 pm UTC
aggregates, by their definition, ignore nulls.
sum, min, max, count( expression ) -- they ignore nulls.
1+null is null by definition.
follow-up
Jim Dickson, November 05, 2004 - 6:07 am UTC
I meant to write:
I am sure ANSI say something like:
- NULL operator operand = null
and
- aggregates ignore NULLs.
But surely this leads to inconsitency above.
November 05, 2004 - 4:26 pm UTC
it is not an inconsistency -- you are comparing an expression:
1+null
to an aggregate
sum()
and they are not comparable, they are different.
What if we only have nulls?
alastair green, January 09, 2007 - 11:34 am UTC
Hello Tom,
We recently had a case where SUM was returning NULL as there were no non-NULL rows. I had naively assumed that it would return zero in this case:
SQL> create table Test_agg
2 (
3 Val_col integer
4 )
5 ;
Table created
SQL> insert into test_agg(val_col)values(1);
1 row inserted
SQL> insert into test_agg(val_col)values(2);
1 row inserted
SQL> insert into test_agg(val_col)values(3);
1 row inserted
SQL> insert into test_agg(val_col)values(null);
1 row inserted
SQL> select sum(val_col)from test_agg;
SUM(VAL_COL)
------------
6
SQL> delete test_agg where val_col is not null;
3 rows deleted
SQL> select sum(val_col)from test_agg;
SUM(VAL_COL)
------------
SQL>
Is this ANSI standard behaviour, if not, what is the rationale for doing it this way? My mental model was to think of nulls as being replaced by zeros but this is certainly not the case.
January 11, 2007 - 9:33 am UTC
nulls are NOT replaced by zeroes, sum(expression) is defined as the sum of the non-null values of expression.
There are no non-null values to sum, there is "nothing", the absence of anything here.
What if we only have nulls? Addendum
alastair Green, January 10, 2007 - 9:35 am UTC
I meant to add this on my previous post:
It appears that SUM is initially NULL and as non-NULL values are found ORACLE calculates the sum. Why is the initial value not zero?
To: alastair Green
Michel Cadot, January 12, 2007 - 12:11 am UTC
Why an initial value of 0?
A sum does not start at 0. It starts at the first value encountered. Then you add the following ones.
If there is no value, there is no sum; so the result is NULL.
Regards
Michel
use NVL function
raaghid, January 12, 2007 - 7:28 am UTC
Use NVL function, provided if both the variable values are null then it is considered as equal values.
SQL> declare
2 x int;
3 y int;
4 a int;
5 b int := 55;
6 begin
7 dbms_output.put_line( 'enter' );
8 if ( nvl(x,0) = nvl(y,0) )
9 then
10 dbms_output.put_line( 'x = y' );
11 end if;
12 if ( nvl(a,0) <> nvl(b,0) )
13 then
14 dbms_output.put_line( 'a <> b' );
15 end if;
16 dbms_output.put_line( 'exit' );
17 end;
18 /
strange bug
martina, January 12, 2007 - 9:09 am UTC
hi Tom,
when searching metalink i encountered to following:
-- bug 5608853 base bug 5608853
After 9i->10g upgrade , Customer met wrong result.
create table test1(aa varchar(10),bb varchar(10));
create table test2(cc varchar(10),aa varchar(10),dd varchar(10));
insert into test1 values('11','qaz');
insert into test1 values('22','wsx');
insert into test1 values('33','edc');
commit; .
select a.bb from test1 a,test2 b
where a.aa=b.aa(+)
and a.bb='qaz'
and b.dd(+)='';
< Result> 9i => qaz -> no problem
10g => no rows selected -> wrong result
So I copied and pasted that and tried it. It returned 'qaz' at 9i and no rows selected at 10g. then a colleague tried it too and found that it WORKED (returned qaz!) at one of our 10g. We did not find any differences between our 10gs. I was about to file a SR when i realized that it is no bug at all as it contains *and b.dd(+)=''; * so *no rows selected* is expected.
why does it return 'qaz' at 9i and at least one 10g???
why is it filed as a bug???
a puzzled martina
to raaghid; Bug waiting to happen
Marco van der Linden, January 13, 2007 - 3:31 am UTC
If you mean to test x = y where either both are equal or both are null, you definitely have a bug there. Run the code below and you'll see what I mean.
declare
x number;
y number := 0;
begin
if (x = y or (x is null and y is null))
then
dbms_output.put_line('x=y or x is null and y is null');
end if;
--
if nvl(x, 0) = nvl(y, 0)
then
dbms_output.put_line('x=y or (x=0 and y is null) or (x is null and y=0) or (x is null and y is null)');
end if;
end;
You are not comparing x and y where both may be null, you are saying 'if x or y is null, treat them as if they were zero'. Apples and pears.
NULL = UNKNOWN
Neil Barsema, January 15, 2007 - 9:25 am UTC
The moment the whole null issue realy hit home with me, was when I was told to think of it as 'Unknown' in stead of 'no value' sudenly it starts to make sense that 'unknown = unknown' is unknown, it might be true, just like unknown <> unknown might be.
The only place where this doesn't realy hold up is
select * from dual where '' is null
As far as I can see there is no way to have an empty string be 'known' in Oracle.
unknown is unknown
martina, January 16, 2007 - 9:19 am UTC
hi neil,
select * from dual where '' is null;
Was always a synonym for me for:
select * from dual where null is null;
That is why i´m so bewildered that a query containing
the line b.dd(+)='' can deliver a result!
regards,martina
Try to keep this straight ...
Phil Singer, January 17, 2007 - 9:26 am UTC
This last clause points up to a long standing issue in Oracle. Oracle implements the SQL null as an empty string. Oracle recognizes that this is 'wrong', and advises everyone not to rely on it.
In other words, '' is not a null, it is an empty string.
I know exactly how long '' is; I have no idea how long a null is. If I know something about a datum, it cannot be null.
With other databases, which distinguish between NULL and '', a predicate such as "and col1 = '' " makes perfect sense. In fact, if you browse the MetaLink forums, you will find plenty of people trying to port applications which support empty string to Oracle, having problems, and wondering what to do.
Still strange
martina, January 18, 2007 - 12:57 pm UTC
ok, i would have written where b.dd (+) is null anyway if it was my statement. I would not have written = null or =''. but the outcome of the statement is null = '' due to the outer join. and that should not give any results, schould it?
strange is the fact that it gives results on 9i and on 1 10g out of several and we cannot find the difference.
regards, martina
Solution may lie in this direction
Phil Singer, January 18, 2007 - 11:06 pm UTC
I'm sorry that I did not catch the real follow up question here. Obviously, this is classified as a bug, because, whenever two different versions of the RDMS give different results for the same query, there must be a bug.
As to how this could happen, my hunch is that the two versions are resolving the ambiguity in the old outer join syntax two different ways. That predicate, b.dd='' can first be applied to the test2 table, and then the outer join takes place, or the outer join can first be made, and both predicates then applied. In the first case, only the a.bb='qaz' is left to be applied to the intermediate table, which, unless I am completely confused, will contain all three rows of test1 (outer joins always contain all rows of the required table which have not been filtered away).
ANSI NULLS (10g)
Elwyn Lloyd Jones, November 12, 2009 - 8:55 am UTC
If:
> select * from DUAL where (null=0 or 1=1);
works (It returns DUMMY: X)
Why (when I invert the logic) doesn't:
> select * from DUAL where not (null=1 or 1=0);
work? (It returns DUMMY: NULL)
November 15, 2009 - 1:57 pm UTC
select * from DUAL where (null=0 or 1=1);
is the same as:
select * from DUAL where (UNKNOWN or TRUE);
is the same as:
select * from DUAL where (UNKNOWN) or (TRUE);
is the same as:
select * from DUAL where (TRUE);
however,
select * from DUAL where not (null=1 or 1=0);
is the same as:
select * from DUAL where not (UNKNOWN or FALSE);
is the same as:
select * from DUAL where not (UNKNOWN) or not (FALSE);
is the same as:
select * from DUAL where not (UNKNOWN) or (TRUE);
is the same as:
select * from DUAL where (UNKNOWN) or (TRUE);
is the same as:
select * from DUAL where (TRUE);
I don't understand your "logic" thinking that the second query doesn't work? You didn't just invert the logic, you changed the predicate entirely.
I have no idea why you might think it should return nothing?? can you explain?
because ...
Sokrates, November 16, 2009 - 2:35 am UTC
because:
SQL> select * from DUAL where (null=0 or 1=1);
DUM
---
X
SQL> select * from DUAL where not (null=1 or 1=0);
no rows selected
Tom, your answer was not correct:
select * from DUAL where not (null=1 or 1=0)
is equivalent to
select * from DUAL where not (UNKNOWN or FALSE);
which is equivalent to
[
see
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions004.htm#sthref2743 ]
select * from DUAL where not (UNKNOWN);
which is
select * from DUAL where (UNKNOWN);
which surely does not return any row
The point to remember here is
UNKNOWN or FALSE : UNKNOWN
UNKNOWN or TRUE : TRUE
!!
November 23, 2009 - 9:14 am UTC
doh, thanks for that followup.
guess I should have just:
ops$tkyte%ORA11GR2> set autotrace on
ops$tkyte%ORA11GR2> select * from dual where NOT( 1=0 or null=null );
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3752461848
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
<b>
1 - filter(NULL IS NOT NULL)
</b>
Sokrates, November 23, 2009 - 9:32 am UTC
Does DISTINCT treat NULL as duplicate???
Mukund Nampally, April 10, 2010 - 5:07 pm UTC
Hi Tom,
I think Oracle considers each NULL as a unique value.
But when I do the distinct on columns having NULL values I get only one record with NULL value instead of all the records which have NULL value.
example: the standard emp table where comm column can have NULL value.
select distinct comm from emp gives:
--> this is NULL
1400.00
500.00
300.00
0.00
The first entry being the NULL value
But, is that not I should expect all the records with NULL values?Is that in case of ONLY NULL values the distinct keyword gives only one NULL value
Test: create table t(a number unique );
insert into t values (1);
insert into t values (NULL);
insert into t values (NULL);
insert into t values (NULL);
select * from t gives:
1
--> NULL
--> NULL
--> NULLL
and select distinct a from t gives
1
--> NULL
April 13, 2010 - 9:05 am UTC
that is the "documented, by definition" behavior of distinct as per the language specification.
group by works the same way.
NULLS and Cardinality
A reader, November 12, 2012 - 5:04 pm UTC
In your column in the latest issue of the Oracle Magazine (
http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom- 1867739.html), you discussed NULLs and cardinality. I'm not quite following your example--why does Oracle think there are only 175
rows instead of the original estimate of 36,024 rows after you updated NULLs to '01-jan-9999'? Can you please elaborate? THanks.
November 14, 2012 - 10:22 am UTC
think of the histogram - we have 703 distinct values in the column before the update - 704 afterwards. So, we have a frequency (not height balanced) histogram - an imperfect view of the data.
without that whacky value - the histogram would have looked something like:
x
x
x
x
x
x
x
x
x
xxxxxxxx
with that whacky out lying value - it would be:
x x
x x
x x
x x
x x
x x
x x
x x
x x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
if it were *height balanced*, but it is not - so we have to put many values into each bucket and you end up with (conceptually)
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
anytime you have more than 254 unique values - the histogram becomes "imperfect" and depending on the nature of the data - could be really bad.
(the next release has some new things to help address thing - but still - if you don't have to use a bad practice - don't!)
Other than NULL, what else do we have
Udankar, May 21, 2015 - 4:04 pm UTC
When I write a SELECT QUERY
SELECT COLUMN_NAME, COUNT(*)
FROM MATERIALIZED_VIEW
GROUP BY COLUMN_NAME
ORDER BY COLUMN_NAME
The first 3 rows are BLANK for the COLUMN_NAME
COLUMN_NAME | COUNT(*)
======================
BLANK | 4
BLANK | 101396
BLANK | 32868
When I rewrite the same SELECT QUERY as
SELECT COLUMN_NAME, COUNT(*)
FROM MATERIALIZED_VIEW
where COLUMN_NAME is null
GROUP BY COLUMN_NAME
ORDER BY COLUMN_NAME
No rows are returned
When I tweak the same SELECT QUERY again
SELECT COLUMN_NAME, COUNT(*)
FROM MATERIALIZED_VIEW
where COLUMN_NAME = ' '
GROUP BY COLUMN_NAME
ORDER BY COLUMN_NAME
The result set is
COLUMN_NAME | COUNT(*)
======================
BLANK | 32868
I am not sure what value is there in the other 2 blanks, when they are not "NULL" or BLANK SPACE
I am querying a Materialized view in Oracle.
This base table for the Materialized view is a DB2 table.
The column in discussion is a CHAR(18)