Description
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.