I often use NULL values in database, when I use foreign keys for example

now, if I try

        $sql = new \Zend\Db\Sql\Sql($this->adapter);
        $insert = $sql->insert('post')
                ->values(array('post_id' => null, 'user_id' => null, 'post_title' => 'foobar'));
        $query = $sql->getSqlStringForSqlObject($insert);
        $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);

user_id has a fk constraint.

It would fail because the generated sql string will be

INSERT INTO `post` (`post_id`, `user_id`, `post_title`) VALUES ('', '', 'foobar')

If I want this to work, I have to use \Zend\Db\Sql\Expression:

->values(array('post_id' => null, 'user_id' => new \Zend\Db\Sql\Expression('null'), 'post_title' => 'foobar'));

I guess it's would be nice feature to automatically transform php NULL value to \Zend\Db\Sql\Expression('null') .

The same for Where

        $update = $sql->update('post')
                ->set(array('user_id' => 1))
                ->where(array('user_id' => null, 'post_valid' => 1));
        $query = $sql->getSqlStringForSqlObject($update);

The generated sql string will be

UPDATE `post` SET `user_id` = '1' WHERE `user_id` = '' AND `post_valid` = '1'

The workaround is a little more verbose

        $update = $sql->update('post')
                ->set(array('user_id' => 1));
        $update->where->addPredicate(new \Zend\Db\Sql\Predicate\IsNull('user_id'));
        $update->where->addPredicate(new \Zend\Db\Sql\Predicate\Operator('post_valid', Predicate\Operator::OPERATOR_EQUAL_TO, 1));
        $query = $sql->getSqlStringForSqlObject($update);

2 things to enhance here :

Automatically transform null value to \Zend\Db\Sql\Predicate\IsNull, and allow something like this

        $update = $sql->update('post')
                ->set(array('user_id' => 1))
                ->where(array(
                   new \Zend\Db\Sql\Predicate\IsNull('user_id'),
                   'post_valid' => 1
                ));
        $query = $sql->getSqlStringForSqlObject($update);

Protip :

If I use something like :

      $select = $sql->select('post')
                ->columns(array('*'))
                ->where(array(
                   'post_id' => array(1, 2, 3)
                ));
        $query = $sql->getSqlStringForSqlObject($select);
        var_dump($query);

It would be nice to detect that the post_id value is an array, and automatically use the predicate \Zend\Db\Sql\Predicate\In instead of \Zend\Db\Sql\Predicate\Operator