[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