Forum OpenACS Q&A: Notice: Found postgres7.2 incompatibility

Request notifications

The NULL syntax has changed slightly between 7.1.2 and 7.2.

In 7.1.2 you could do a select * from foo where bar=NULL and it will do what you expect. In 7.2 this will not return rows. You have to do: where bar is NULL

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)
(1, NULL);

insert into null_test 
(index, value)
(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.

Posted by Don Baccus on
Is there code in our toolkit that does this?  Ouch, it shouldn't ...

"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?

I will be working specifically with the 3.2.x toolkit, but I will happily post my grep and search and replace commands here that fix things.
Posted by Roberto Mello on
Don, I think it was because of MS Access semantics. It may have also been because of MySQL, but I don't know enough about it to say. Regardless, I was also surprised when I learned they were lobbied into that.
Posted by Don Baccus on
Oh, Roberto, you may be right, I may be confusing Access with MySQL.
Well, it looks like the ACS3.2.x is pretty clean. In my ACS3.2.x installation I found the following files with problems:
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

Posted by Tilmann Singer on
There is another minor incompatibility:
     * 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.

Here is a slight improvement in my query for finding these guilty strings:
$ find . -regex '.*(sql|adp|tcl)' | xargs egrep -ri '([^:]=|<>) ?null' 
Posted by Carl Coryell-Martin on
I don't understand whats going on underneath but here is a scrap of code that showed up in our system:

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.


Posted by Louis Zirkel on
I found as I was trying to do some things with news that PG7.2 seems to have a really hard time with anything that is an "interval" and is declared that way.  I found that by taking many declarations out of some queries that had intervals coded in them the queries worked.

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." ?


Posted by Roberto Mello on
I think it was deprecated a couple versions ago, so they finally took it out. It's not worth it to have that non-standard thing in there. Just do a search/replace to change "= NULL" to "IS NULL" and be done with it.
Posted by Don Baccus on
Not only is it non-standard but it is WRONG and MISLEADING.  It is very important to understand how three-valued logic works in SQL and disabling three-valued logic for one and only one operator is simply a bug.

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 ...

Posted by Robert Locke on
Actually, "= NULL" is valid in the assignment context, as in:

    update foo set bar = NULL where moo = 'cow';

So, be careful how you globally replace "= NULL".