TYPO3 Core

Using a query that causes a join via MM table yields wrong results due to incorrect transformation of the original query to doctrine.

The reason is that in \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::addUnionStatement() formerly the query was made up by LEFT JOINs with ON conditions. Those conditions have no been partially converted to where clauses, which causes rows to be discarded, which could hold null value after a LEFT JOIN.

Compare the queries before the doctrine migration and after.

Before:

SELECT tx_ext_domain_model_thing.uid, tx_ext_domain_model_thing.title, sys_category.title, sys_category_record_mm.* FROM tx_ext_domain_model_thing
  LEFT JOIN sys_category_record_mm AS sys_category_record_mm ON tx_ext_domain_model_thing.uid=sys_category_record_mm.uid_foreign
  LEFT JOIN sys_category AS sys_category ON sys_category_record_mm.uid_local=sys_category.uid AND sys_category_record_mm.tablenames = 'tx_ext_domain_model_thing' AND sys_category_record_mm.fieldname = 'categories'
WHERE 1=1 AND (tx_ext_domain_model_thing.sys_language_uid IN (0,-1))
      AND (((sys_category.sys_language_uid IN (0,-1))) OR sys_category.uid IS NULL)
      AND tx_ext_domain_model_thing.deleted=0
      AND ((sys_category.deleted=0) OR sys_category.uid IS NULL)
ORDER BY sys_category.title ASC, tx_ext_domain_model_thing.uid ASC

After:

SELECT `tx_ext_domain_model_thing`.* FROM `tx_ext_domain_model_thing` `tx_ext_domain_model_thing`
  LEFT JOIN `sys_category_record_mm` `sys_category_record_mm` ON `tx_ext_domain_model_thing`.`uid` = sys_category_record_mm.uid_foreign
  LEFT JOIN `sys_category` `sys_category` ON `sys_category_record_mm`.`uid_local` = sys_category.uid
WHERE ((`sys_category_record_mm`.`tablenames` = 'tx_ext_domain_model_thing') AND (`sys_category_record_mm`.`fieldname` = 'categories'))
      AND (`tx_ext_domain_model_thing`.`sys_language_uid` IN (0, -1))
      AND (`tx_ext_domain_model_thing`.`deleted` = 0)
      AND (((`sys_category`.`sys_language_uid` IN (0, -1)) AND (`sys_category`.`deleted` = 0)) OR (`sys_category`.`uid` IS NULL))
GROUP BY `tx_ext_domain_model_thing`.`uid`
ORDER BY `sys_category`.`title` ASC, `tx_ext_domain_model_thing`.`uid` ASC;

This is a regression to #77379.