We run a postgres database and have generated out base models from the database. In the base models, we see the following issue:

        $this->hasColumn('contractor_type', 'string', 150, array(
             'type' => 'string',
             'length' => '150',
             'fixed' => false,
             'unsigned' => false,
             'notnull' => true,
             'default' => '\'enduser\'::character varying',
             'primary' => false,
             ));

So, the default value is showing some PostgreSQL meta information. We fixed this by changing function listTableColumns():

            if (preg_match("/^nextval\('(.*)'(::.*)?\)$/", $description['default'], $matches)) {

                $description['sequence'] = $this->conn->formatter->fixSequenceName($matches[1]);
                $description['default'] = null;
            }
+           elseif (preg_match("/^'(.*)'::character varying$/", $description['default'], $matches)) {
+               $description['default'] = $matches[1];
+           }

            $columns[$val['field']] = $description;

Doctrine_Record:1469

} else if ($type == 'timestamp' || $type == 'date') {
return strtotime($old) !== strtotime($new);

These new lines cause inability to save NOW() to "date" field.

Change was commited in r6403 branch 1.2

MySQL error is:

SQLSTATE[HY000]: General error: 1364 Field 'date' doesn't have a default value

Doctrine_Record::isValueModified compares timestamps as strings and values that are equal such as 2009-09-02 00:00:00 and 2009-09-02 are treated as modified.

The code should do a second compare on the strtotime value if the strings appear to be unequal.

If the fields name of the latitude or longitude invoker aren't 'latitude' or 'longitude', then the getDistanceQuery doesn't not work.

milesSql = sprintf($sql, $invoker->get('latitude'), $invoker->get('latitude'), $invoker->get('longitude'), '1.1515', 'miles');
...
$kilometersSql = sprintf($sql, $invoker->get('latitude'), $invoker->get('latitude'), $invoker->get('longitude'), '1.1515 * 1.609344', 'kilometers');

Replacing latitude by $latName (previously defined line 74 and 75) resolved this problem.

Here's the fix :

milesSql = sprintf($sql, $invoker->get($latName), $invoker->get($latName), $invoker->get($longName), '1.1515', 'miles');
...
$kilometersSql = sprintf($sql, $invoker->get($latName), $invoker->get($latName), $invoker->get($longName), '1.1515 * 1.609344', 'kilometers');

I'm using a mysql function to calculate the distance between 2 points. When I'm using this funciton in a doctrine query it works. But when I'm using the functions result in a where clause it dont work:

The Query:
$basicQuery = $query->select('id, getGeoDistanceKM(lng, lat, '.$points['lng'].', '.$points['lat'].') AS distance, getGeoDistanceKM(lng, lat, '.$points['lng'].', '.$points['lat'].') AS distance, IF(count(r.id) > 0, AVG(r.rating), 0) as ratingAvg')
->from('ProomsOffer o')
->leftJoin('o.PublicTransports pt')
->leftJoin('o.Unavailabilities ua')
->leftJoin('o.Ratings r')
->leftJoin('o.Bookings b')
->where('distance != 0')
->groupBy('o.id')
->orderBy('distance, ratingAvg');

The Error message:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'm__1' in 'where clause'
#0 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Connection.php(1015): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Mysql))
#1 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Connection.php(790): Doctrine_Connection->execute('SELECT COUNT...', Array)
#2 /Applications/MAMP/htdocs/prooms/cms/library/Doctrine/Doctrine/Query.php(2037): Doctrine_Connection->fetchAll('SELECT COUNT...', Array)
#3 /Applications/MAMP/htdocs/prooms/cms/application/modules/prooms/controllers/SearchController.php(163): Doctrine_Query->count()
#4 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Action.php(513): Prooms_SearchController->searchAction()
#5 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Dispatcher/Standard.php(289): Zend_Controller_Action->dispatch('searchAction')
#6 /Applications/MAMP/htdocs/prooms/cms/library/Zend/Controller/Front.php(946): Zend_Controller_Dispatcher_Standard->dispatch(Object(CMS_Controller_Request), Object(Zend_Controller_Response_Http))
#7 /Applications/MAMP/htdocs/prooms/cms/library/CMS/Application.php(372): Zend_Controller_Front->dispatch()
#8 /Applications/MAMP/htdocs/prooms/cms/library/CMS/Application.php(241): CMS_Application->_dispatch(Object(Zend_Controller_Front))
#9 /Applications/MAMP/htdocs/prooms/cms/public/index.php(50): CMS_Application->run()
#10

The getSqlQuery:
SELECT `m`.`id` AS `m_id`, getGeoDistanceKM(`m`.`lng`, `m`.`lat`, 48.2092062, 16.3727778) AS `m0`, getGeoDistanceKM(`m`.`lng`, `m`.`lat`, 48.2092062, 16.3727778) AS `m1`, IF(COUNT(`m4`.`id`) > 0, AVG(`m4`.`rating`), 0) AS `m41` FROM `module_prooms_offers` `m` LEFT JOIN `module_prooms_offers_public_transport` `m2` ON `m`.`id` = `m2`.`id_offer` LEFT JOIN `module_prooms_offers_unavailability` `m3` ON `m`.`id` = `m3`.`id_offer` LEFT JOIN `module_prooms_offers_ratings` `m4` ON `m`.`id` = `m4`.`id_offer` LEFT JOIN `module_prooms_bookings` `m5` ON `m`.`id` = `m5`.`id_offer` WHERE `m1` != 0 GROUP BY `m`.`id` ORDER BY `m1`, `m4_1`

Tested with Doctrine_Cache_APC and Doctrine_Cache_Db with SQLite.

• The model:

Article:
  actAs:
    Timestampable:
    Sluggable:
      fields:   [date, title]
    I18n:
      fields:       [title, content]
      actAs:
        Sluggable:
          fields:   [title]
          uniqueBy: [lang, title]
  columns:
    title:          string(255)
    date:           date
    content:        clob

• The symfony code:

public function configureDoctrine(Doctrine_Manager $manager)
{
  if (sfConfig::get('sf_cache') == true)
  {
    $cacheDriver = new Doctrine_Cache_Apc();
    
    $manager->setAttribute(Doctrine::ATTR_QUERY_CACHE, $cacheDriver);
    $manager->setAttribute(
      Doctrine::ATTR_QUERY_CACHE_LIFESPAN,
      sfConfig::get('app_cache_lifetime', 86000)
    );
    $manager->setAttribute(Doctrine::ATTR_RESULT_CACHE, $cacheDriver);
    $manager->setAttribute(
      Doctrine::ATTR_RESULT_CACHE_LIFESPAN,
      sfConfig::get('app_cache_lifetime', 86000)
    );
  }
}

public function fetchAll()
{
  return Doctrine_Query::create()
    ->from('Article a')
    ->leftJoin('a.Translation t')
    ->orderby('a.date DESC')
    ->useQueryCache()->useResultCache()
    ->execute();
}

• The Result:

500 | Internal Server Error | Doctrine_Record_UnknownPropertyException
Unknown record property / related component "title" on "Article"

When trying to fetch a nested set tree, where a base query uses join, an exception is thrown "'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'", which is caused by incorrect generated SQL query.

How to reproduce:

$query = Doctrine_Query::create()
            ->select('s.name')
            ->from('Test s')
            ->leftJoin('s.Test2 st WITH st.title = ?', array(1));

$tree_table = Doctrine::getTable('Test');
$tree = $tree_table->getTree();

viewing SQL at this point, is:

SELECT t.id AS t__id, t.name AS t__name 
FROM test t 
LEFT JOIN test_2 t2 
    ON t.id = t2.test_id AND (t2.title = ?)

set the query as a base for nestedSet tree:

$tree->setBaseQuery($query);

viewing SQL at this point, is:

SELECT t.id AS t__id, t.name AS t__name, t.lft AS t__lft, t.rgt AS t__rgt, t.level AS t__level 
FROM test t 
LEFT JOIN test_2 t2 
    ON t.id = t2.test_id AND (t2.title = ?) AND (t2.title = ?)

As you can see lft/rgt/level fields were added, which is ok, but also additional LEFT JOIN condition was duplicated - (t2.title = ?) .

These kind of queries are working in 1.0* branch and also seemed to work in 1.1* branch until 5680 revision, which broke it. Although Changeset 5700 seems to must have fixed it, but the SQL is still generated doubled in 1.1* and 1.2* branches.