"IS NULL" in Zend_Db_Table not working

Question

I am trying to make a join on 2 tables in Zend using the structure DbTable / model / mapper. If in my cartographer I do this:

$select = $this->getDbTable()->select(Zend_Db_Table::SELECT_WITH_FROM_PART) ->setIntegrityCheck(false) ->join('images', 'images.oldFilename = availablePictures.filename') ->where('images.ref IS NOT NULL'); $resultSet = $this->getDbTable()->fetchAll( $select ); 

it works like a charm, but if I try the same thing with IS NULL and not NOT NULL, I get nothing where I have to get a result set of several rows, just like when trying to use it directly in MySQL with help

 SELECT * FROM ( `availablePictures` AS a LEFT JOIN `images` AS i ON a.filename = i.oldFilename ) WHERE i.`ref` IS NULL 

It seems to me that Zend doesn't like my IS NULL or am I doing something wrong?

Answer 1

The solution was found in Machine comment on my original post. In doing what he suggested, I noticed that Zend created an internal join because I used the wrong select method, therefore:

 $select = $this->getDbTable()->select(Zend_Db_Table::SELECT_WITH_FROM_PART) ->setIntegrityCheck(false) ->joinLeft('images', 'images.oldFilename = availablePictures.filename') ->where('images.ref IS NOT NULL'); $resultSet = $this->getDbTable()->fetchAll( $select ); 

how it should be.

Answer 2

I think this is due to how MySql decides what is NULL and what is not. Is it possible that the expected results have a default empty row assignment of '' or 0 in the images.ref column? MySql does not treat them as NULL. Look at here:

http://dev.mysql.com/doc/refman/4.1/en/working-with-null.html