I have a RecordType? which hasMany Record(s)

When I do this:

$select = Doctrine_Query::create()
            ->from("RecordType t")
            ->leftJoin("t.Record r")
            ->select('t.id, t.name, t.description, t.created_at, t.modified_at')->limit(30)->execute();

I get the above error. Looks like it's the limit clause causing it. Removing the limit clause gets rid of the error.

The SQL looks something like this:

[SELECT [r].[id] AS [r__id], [r].[name] AS [r__name], [r].
[description] AS [r__description], [r].[created_at] AS
[r__created_at], [r].[modified_at] AS [r__modified_at] FROM
[record_type] [r] LEFT JOIN [record] [r2] ON [r].[id] = [r2].
[record_type_id] WHERE [r].[id] IN (SELECT * FROM (SELECT TOP 30 *
FROM (SELECT TOP 30 DISTINCT [r].[id] FROM [record_type] [r]) AS
inner_tbl) AS outer_tbl)]

From #doctrine, the triple sub query should simply be something like:

where r.id in (select distinct top 30 id from record_type)

and the SELECT TOP 30 DISTINCT should be SELECT DISTINCT TOP 30.

Quick, dirty fix to file: Doctrine/Connection/Mssql.php (Line: 128)

$select = (preg_match('/^SELECT(s+)DISTINCT/i',  $query))?"SELECT DISTINCT":"SELECT";
$query = preg_replace('/^'.$select.'s/i', $select.' TOP ' . ($count+$offset) . ' ', $query);