DISTINCT does not work for MSSQL databases

[Lokilein]

Bug Report

Q	A
Version(s)	2.18.0

Summary

Selecting data by the Select-Class and AbstractTableGateway::selectWith returns data like you didn't use DISTINCT.

Current behavior

I have a query which performs differently on MySQL and MSSQL. When executing the query on MySQL, everything works as expected, but for MSSQL, it behaves as if I didn't enter the DISTINCT keyword.

How to reproduce

Here is my Select I'm building, with the input parameter $userid

$select = new Select(SurveySet::TABLE_NAME);
$select->quantifier(Select::QUANTIFIER_DISTINCT); // <-- DISTINCT
$select->columns(['id', 'name']);
$select->join(['s'   => Survey::TABLE_NAME], 's.survey_set_id = id', []);
$select->join(['c'   => Course::TABLE_NAME], 'c.verid = s.verid', []);
$select->join(['sta' => SubunitToAdministrate::TABLE_NAME], 'sta.subunitid = c.subunitid', []);
$select->where->equalTo('sta.userid', $userid);

$result = $this->surveySetTable->selectWith($select);

When I output the query with $select->getSqlString($this->surveySetTable->getAdapter()->getPlatform()), this looks fine. The query also works correctly on both SQL versions when I copy it:
For MSSQL this is:

SELECT DISTINCT [survey_set].[id] AS [id], [survey_set].[name] AS [name] 
FROM [survey_set] 
INNER JOIN [survey] AS [s] ON [s].[survey_set_id] = [id] 
INNER JOIN [course] AS [c] ON [c].[courseid] = [s].[courseid] 
INNER JOIN [subunit_to_admin] AS [sta] ON [sta].[subunitid] = [c].[subunitid] 
WHERE [sta].[userid] = '210'

And for MySQL it is:

SELECT DISTINCT `survey_set`.`id` AS `id`, `survey_set`.`name` AS `name` 
FROM `survey_set` 
INNER JOIN `survey` AS `s` ON `s`.`survey_set_id` = `id` 
INNER JOIN `course` AS `c` ON `c`.`courseid` = `s`.`courseid` 
INNER JOIN `subunit_to_admin` AS `sta` ON `sta`.`subunitid` = `c`.`subunitid` 
WHERE `sta`.`userid` = '210'

The relation survey_set to survey is 0..1 to 1..n. So a survey_set can be in multiple survey entries. Some surveys can be in no set (they are null then). The other joins might not be relevant, I guess any 1..n relation would bring a similar result.
For MSSQL, I have 9 sets in 69 surveys and I get 69 results instead of 9, which I get when I execeute the query manually in MSSQL.
For MySQL, I got 23 sets in 189 surveys and I got 23 results, which is the same as the SQL query gives me.

Based on the SQL-String, you can also tell that the select does consider and receive this value, but it seems to be ignored at execution. This also make me think that I am not using it wrong.

Expected behavior

I want the result of "selectWith($select)" to be the same as the DB-query that the $select-Query returns.