On Fri, 1 Aug 2003, maillist wrote:
> Stephan Szabo wrote:
> >
> > On Thu, 31 Jul 2003, maillist wrote:
> >
> > > select * from apps where (not apppag = null);
> > > returns nothing when it should return every thing
> >
> > No. It should return nothing. apppag = null returns unknown for every
> > row. Use IS NULL to test if a value is null.
> hmmm now i'm confused when i set a value i don't use the phrase to null
> and '= null' has always worked in the past, and if '= null' doesn't work
> it should be an invalid query because i can see no other use for it, and
> for the most part is, to are ignored anyways.
Old versions converted an explicit token sequence like = NULL into IS NULL
for you (due to broken clients). Newer versions have a compatibility GUC
configuration option transform_null_equals which turns it on (which I
usually forget about). However, the option should generally be
discouraged because it is inconsistent (= NULL is converted, but comparing
two variables where the second is NULL does not for example and not
foo=null works but foo!=null doesn't). The change was made to better
fit the SQL specification.
As for =NULL in general, it's pretty meaningless but meets our syntactic
constraints.
> so would the correct query be
> select * from apps where (not apppag = is null)
> or
> select * from apps where (not apppag is null)
Actually since you're doing not, I'd suggest
where (apppag is not null)
> both of these look silly, but I do use null in other fields on purpuse
> to represent uninitailized fields. So I definitlay am going to have to
> grep my code for "=*null" and check to see what needs to be done. Ijust
> thought it was a bug, that should not have been found, because the query
> had un needed checks. I need to do more testing before I allow
> production servers to upgrade
You can turn on the option as a short term thing, although I'd suggest
changing the queries in any case.