TYPO3 Core

While migrating the old SQL to the new doctrine I found the following issue. You will find the original query under typo3/sysext/filelist/Classes/FileList.php.

Testcase:

/** @var QueryBuilder $queryBuilder */
            $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages','sys_template');
            $result = $queryBuilder
                ->select('pages.uid', 'count(*)', 'max(sys_template.root)', 'min(sys_template.root)')
                ->from('pages')
                ->from('sys_template')
                ->where(
                    $queryBuilder->expr()->eq('pages.uid', 'sys_template.pid')
                )
                ->andWhere(QueryHelper::stripLogicalOperatorPrefix(BackendUtility::deleteClause('pages')))
                ->andWhere(QueryHelper::stripLogicalOperatorPrefix(BackendUtility::versioningPlaceholderClause('pages')))
                ->andWhere(QueryHelper::stripLogicalOperatorPrefix(BackendUtility::deleteClause('sys_template')))
                ->andWhere(QueryHelper::stripLogicalOperatorPrefix(BackendUtility::versioningPlaceholderClause('sys_template')))

                ->groupBy('pages.uid')
                ->orderBy('pages.pid')
                ->addOrderBy('pages.sorting')
                ->setMaxResults(1)
                ->getSQL();

Wrong result:

SELECT `pages`.`uid`, `count(*)`, `max(sys_template`.`root)`, `min(sys_template`.`root)` FROM `pages`, `sys_template` WHERE (`pages`.`uid` = sys_template.pid) AND (pages.deleted=0) AND ((pages.t3ver_state <= 0 OR pages.t3ver_wsid = 0)) AND (sys_template.deleted=0) AND ((sys_template.t3ver_state <= 0 OR sys_template.t3ver_wsid = 0)) AND ((`pages`.`hidden` = 0) AND (`pages`.`starttime` <= 1461168600) AND ((`pages`.`endtime` = 0) OR (`pages`.`endtime` > 1461168600)) AND (`pages`.`deleted` = 0) AND (`sys_template`.`hidden` = 0) AND (`sys_template`.`starttime` <= 1461168600) AND ((`sys_template`.`endtime` = 0) OR (`sys_template`.`endtime` > 1461168600)) AND (`sys_template`.`deleted` = 0)) GROUP BY `pages`.`uid` ORDER BY `pages`.`pid` ASC, `pages`.`sorting` ASC LIMIT 1

It should be possible to get something like:

SELECT `pages`.`uid`, count(*), max(`sys_template`.`root`), min(`sys_template`.`root`) FROM `pages`, `sys_template` WHERE ...