Forum OpenACS Q&A: Notice: Found postgres7.2 incompatibility
In 7.1.2 you could do a
select * from foo where
and it will do what you expect. In 7.2 this
will not return rows. You have to do:
where bar is
If you want to test this at home, here is a test script:
create table null_test ( index integer primary key, value varchar ); insert into null_test (index, value) values (1, NULL); insert into null_test (index, value) values (2, 'baz'); select * from null_test where value=NULL; select * from null_test where value is NULL; drop table null_test;I think that a global search and replace of =NULL and = Null will fix this. At least that is what I am going to try.
"bar = NULL" is supposed to return NULL no matter what value bar has (including NULL). That's standard SQL 92.
Unfortunately the PG group were lobbied to change the semantics to the non-standard MySQL semantics, which returns "true" rather than "NULL" if bar equals NULL. I was rather horrified to learn they'd done that, to be honest.
We may have accidently let some dependencies slip in, though, due to the fact that Oracle has a very non-standard conversion of '' to NULL in DML statements, and some code might explicitly say NULL since "they're the same anyway". Very non-standard and very non-portable, but wouldn't've been easily noticed by someone porting code from Oracle to PG ...
Carl, since you're going to grep around looking for such cases could you post a report here afterwards?
tcl/ad-user-contributions-summary.tcl tcl/ticket-defs.tcl-old tcl/ticket-todo-plugin.tcl tcl/rf-randompix.tcl www/photodb/search.tcl www/admin/news/index.tcl: case when ng.group_id=NULL then 0 else ng.group_id end as group_id, www/admin/categories/add-link-to-parent.tcl: where category_hierarchy_level($category_id, h.child_category_id, 0) != null"]The command I used for finding them was:
find . -regex '.*(sql|adp|tcl)' | xargs egrep -ri '[^:]= ?null'This doesn't filter out the UPDATEs but its a managable list.
Cheers, Carl C-M
* The timestamp() function is no longer available. Use timestamp 'string' instead, or CAST.In OpenACS 4.x this occurs only once, in the calendar package - patch is in the sdm.
In OpenACS 3.x there seems to be also one occurence of this that should be changed, here: www/education/class/admin/task-add-3.tcl - but I did that search on an old checkout and don't know if that file is still there, or what this education module does at all, so did not change it.
$ find . -regex '.*(sql|adp|tcl)' | xargs egrep -ri '([^:]=|<>) ?null'cheers,
select interval(reltime '10 minutes')
This works fine in 7.1.2 and not in 7.2.
I only found this in some inhouse code, but you might trip over it.
I'm using PG 7.1.2 and have many instances of =NULL and = NULL in mods I've made in OpenACS 3.2.x. So before I upgrade to PG 7.2 and beyond, I'll need to change many scripts. But I wonder, why wasn't =NULL just left in the PG code as a (depreciated) but still usable alternative?
Is it worth pursuing with the PG dev group or is it apt to
be a worthless exercise?
"Hey Bob, Just start making those changes from = NULL to is Null." ?
foo = NULL returns true
foo < NULL returns NULL
See the inconsistency? Three-valued logic is pervasive in SQL and implementors should never break it. Making "foo = NULL" is an effort to hide three-valued logic from the user, and yes, I think it's access and I think it's because MS thought that hiding three-valued logic is easier than explaining it, and the "=" case is the one most non-sophisticated users will run into.
But you can't write complex SQL without fully understanding how three-valued logic works, and breaking standard SQL queries is not an aid to those writing portable code. Which includes us, supporting multiple standard SQL RDBMS's is after all our goal!
Of course Oracle's "empty string = NULL in DML statements" is just as bad ...
update foo set bar = NULL where moo = 'cow';
So, be careful how you globally replace "= NULL".