Patrick's playground

Today I was unpleasantly surprised by a change in behavior when upgrading Propel from version 1.5.4 to 1.6. I frequently use zero dates in MySQL ('0000-00-00') and these dates are affected by the upgrade. As it was deployment time I had to find a solution quickly. This post outlines what the problem is and what I did to solve it.
So what's all the fuzz about? It's about a query like this:

$contractObjectCollection = ContractQuery::create()
            ->filterByOrderId($this->getID())
            ->condition('contractToAfterNow', 'Contract.ContractTo > ?', date('Y-m-d'))
            ->condition('contractToIsNul', 'Contract.ContractTo = ?', '0000-00-00')
            ->combine(array('contractToAfterNow', 'contractToIsNul'), 'or', 'contractToCondition')
            ->condition('contractFromIsFilledIn', 'Contract.ContractFrom <= ?', date('Y-m-d'))
            ->combine(array('contractToCondition', 'contractFromIsFilledIn'))
            ->orderByContractFrom('asc')
            ->orderByContractId('asc')
            ->find();

This query gathers contracts which are active (e.g. valid, started and not ended) at this moment.
In Propel 1.5.4 this code translates to the following query:

SELECT contract.* FROM `contract` WHERE contract.ORDER_ID='1' AND ((contract.CONTRACT_TO > '2011-06-07' OR contract.CONTRACT_TO = '0000-00-00') AND contract.CONTRACT_FROM <= '2011-06-07') ORDER BY contract.CONTRACT_FROM ASC,contract.CONTRACT_ID ASC

However, in Propel 1.6.0 the code translates to:

SELECT contract.* FROM `contract` WHERE contract.ORDER_ID='1' AND ((contract.CONTRACT_TO > '2011-06-07' OR contract.CONTRACT_TO = '-0001-11-30') AND contract.CONTRACT_FROM <= '2011-06-07') ORDER BY contract.CONTRACT_FROM ASC,contract.CONTRACT_ID ASC

Instead of putting 0000-00-00 in the query, the date is translated to -0001-30-11. (Should I read this as 30th of November 1969?)
As my code relies heavily on the outcome of the query, I had to find a quick solution. After some informing on IRC I learned that there seems to be an issue concerning these zero dates (Propel prefers using NULL instead of 0000-00-00, but my application is a legacy application).
The actual solution comes down to writing the query by hand, using the (Debug)PDO instance for querying and using the PropelObjectFormatter to hydrate the results back into the objects you want to be working with.

$query = "SELECT contract.*
        FROM `contract` WHERE contract.ORDER_ID=1"
             AND ((contract.CONTRACT_TO > '" . date('Y-m-d') .
            "' OR contract.CONTRACT_TO = '0000-00-00') AND contract.CONTRACT_FROM <= '" . date('Y-m-d') . "')
            ORDER BY contract.CONTRACT_FROM ASC,contract.CONTRACT_ID ASC";

        $con = Propel::getConnection();
        $stmt = $con->prepare($query);
        $stmt->execute();

        $formatter = new PropelObjectFormatter();
        $formatter->setClass('Contract');
        $contractObjectCollection = $formatter->format($stmt);

And that's it. It cost me a little time to figure this out, but my deployment was saved.

Slight note
Please note that Propel normally puts all fields separately in the SELECT statement, but that I've shortened it by putting down the * sign