[ic] some issues with 5.0 and oracle

Stefan Hornburg racke at linuxia.de
Wed Feb 4 14:44:53 EST 2004


On Wed, 21 Jan 2004 22:54:44 +0000 (UTC)
Jon Jensen <jon at endpoint.com> wrote:

> On Wed, 21 Jan 2004, Stefan Hornburg wrote:
> 
> > > Are you sure it's not failing because of the != comparison? SQL's 
> > > standard inequality operator is <> and not != so that could be it.
> > 
> > At least not with MySQL:
> > 
> > alter table products add column foo int(1);
> > 
> > mysql> select distinct foo from products;
> > +------+
> > | foo  |
> > +------+
> > | NULL |
> > +------+
> > 1 row in set (0.05 sec)
> > 
> > mysql> select count(*) from products where foo <> 1;
> > +----------+
> > | count(*) |
> > +----------+
> > |        0 |
> > +----------+
> > 1 row in set (0.05 sec)
> 
> Equality and inequality operators do not operate on NULLs. So if your 
> table contains only null "foo" fields, it is correct to say the count() of 
> rows where foo <> (or != if you prefer) 1 is 0. This is how SQL NULLs work 
> -- they are not values; they're the absence of values. See:
> 
> http://www.mysql.com/doc/en/Working_with_NULL.html
> 
> Incidentally both PostgreSQL and MySQL accept != as a synonym for <> but 
> it is <> that is defined in SQL, and I do not believe Oracle accepts !=.

Therefore inactive needs to be NOT NULL for all RDBMS.

Bye
	Racke

-- 
LinuXia Systems => http://www.linuxia.de/
Expert Interchange Consulting and System Administration
ICDEVGROUP => http://www.icdevgroup.org/
Interchange Development Team



More information about the interchange-users mailing list