Subject Re: seems bug : Field accepts NULL Values (empty string) even if it is defined w
Author bjonessb
This is not a "unique" feature. Every respectable DBMS uses this
standard. Once you learn the rules, dealing with nulls is not a big
problem, and will eventually become an asset. There have been given
a few examples for using an empty string vs a null value. No matter
how it is used, the distinction is that an empty string HAS to be
assigned, so it's a value. A null is unassigned. Null is a state,
not a value. Furthermore, if you have two columns, cola and colb,
and all rows have both cola and colb set to null:

select * from table where cola = colb
select * from table where cola <> colb

Both of these statements will return zero rows. Since null is not a
value, equality and inequality can not be determined.

Nulls used in an equation will return a null value.

select cola + 0 from table
select colb || 'some string' from table
select cola * 10 from table

All three of these return null values.

Aggregate functions are also interesting.

select count(cola) from table

The count function will count only non-null values, so this
statement would return a value of zero, instead of null. The
average function will give you an average of the non-null values,
and ignore the null values.

I could go on, but it would be beneficial for you to do some
research on Nulls. Many of us have learned these lessons the hard
way :)


--- In, Jonathan Neve
<jonathan@m...> wrote:
> Alexander V.Nevsky wrote:
> >--- In, Jonathan Neve
> >wrote:
> >
> >
> >>Martijn Tonies wrote:
> >>
> >>
> >>
> >>>You're answering your own question: '' (empty string) is NOT
> >>>NULL is NULL (empty). An empty string is a value. NULL is a
> >>>so is "NOT NULL".
> >>>
> >>>
> >>>
> >>IMHO, this unique feature could nearly be considered a bug...
> >>
> >>
> >
> > In SQL standard? ;)
> >
> >
> Absolutely! A bug in that it defines a useless distinction that
> forever be a nuisance for every database developer! :-)
> Jonathan Neve.
> [Non-text portions of this message have been removed]