| Subject | Re: seems bug : Field accepts NULL Values (empty string) even if it is defined w |
|---|---|
| Author | bjonessb |
| Post date | 2004-03-16T14:37:46Z |
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 :)
Bill
--- In firebird-support@yahoogroups.com, Jonathan Neve
<jonathan@m...> wrote:
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 :)
Bill
--- In firebird-support@yahoogroups.com, Jonathan Neve
<jonathan@m...> wrote:
> Alexander V.Nevsky wrote:<jonathan@m...>
>
> >--- In firebird-support@yahoogroups.com, Jonathan Neve
> >wrote:NULL.
> >
> >
> >>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 astate,
> >>>so is "NOT NULL".would
> >>>
> >>>
> >>>
> >>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]