Bug #75822

Epic #75555: Further Doctrine Development

Wrong backticks when using a doctrine select on count(), max(), min() etc.

Added by Christoph Kratz over 3 years ago. Updated 11 months ago.

Status:
Closed
Priority:
Must have
Category:
Database API (Doctrine DBAL)
Target version:
Start date:
2016-04-20
Due date:
% Done:

100%

TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

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


Related issues

Related to TYPO3 Core - Bug #75824: Doctrine: migrate ext:tstemplate/TypoScriptTemplateModuleController Closed 2016-04-20

Associated revisions

Revision 8f601d53 (diff)
Added by Morton Jonuschat over 3 years ago

[TASK] Doctrine: Add support for aggregate SQL functions

This patch extends the SQL Expressionbuilder with support for generating
expressions for MIN, MAX, AVG, SUM and COUNT aggregate functions, as
well as adding support for selecting literal (unquoted) SQL expressions
with the QueryBuilder using ->selectLiteral() and ->addSelectLiteral().

Releases: master
Resolves: #75822
Change-Id: Id1043ec8a60be1e6d3c273278a776e92bb8c973e
Reviewed-on: https://review.typo3.org/47826
Reviewed-by: Wouter Wolters <>
Tested-by: Wouter Wolters <>
Reviewed-by: Frank Naegler <>
Tested-by: Frank Naegler <>

History

#1 Updated by Morton Jonuschat over 3 years ago

  • Category set to Database API (Doctrine DBAL)
  • Status changed from New to Accepted
  • Assignee set to Morton Jonuschat
  • Target version set to 8.1
  • PHP Version set to 7.0

#2 Updated by Gerrit Code Review over 3 years ago

  • Status changed from Accepted to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/47826

#3 Updated by Benni Mack over 3 years ago

  • Target version changed from 8.1 to 8.2

#4 Updated by Morton Jonuschat over 3 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#5 Updated by Stephan Bauer about 2 years ago

I still have this problem with 8.7.7:
This

        $latestUpdate = $queryBuilder
            ->select('MAX(tstamp)')
            ->from('tx_decoderdb_domain_model_manufacturer')
            ->execute()
            ->fetch();

creates this SQL:

SELECT `MAX(tstamp)`
FROM `tx_decoderdb_domain_model_manufacturer` 
WHERE (`tx_decoderdb_domain_model_manufacturer`.`deleted` = 0) AND (`tx_decoderdb_domain_model_manufacturer`.`hidden` = 0) 
AND (`tx_decoderdb_domain_model_manufacturer`.`starttime` <= 1505941920) 
AND ((`tx_decoderdb_domain_model_manufacturer`.`endtime` = 0) OR (`tx_decoderdb_domain_model_manufacturer`.`endtime` > 1505941920))
LIMIT 1

#6 Updated by Morton Jonuschat about 2 years ago

Please read the documentation on how to use aggregate functions: https://docs.typo3.org/typo3cms/CoreApiReference/ApiOverview/Database/ExpressionBuilder/Index.html#aggregate-functions
Trying to use a SQL function/fragment in a method that expects a field/table name results in the observed quoting issues.

#7 Updated by Riccardo De Contardi about 2 years ago

  • Status changed from Resolved to Closed

#8 Updated by Harald Holzmann 11 months ago

Should also possible to use aggregate function ins order by clause. E.g. order by max(tstmp)

#9 Updated by Harald Holzmann 11 months ago

Here is the full example (get the last 10 edited pages from log):
SELECT MAX, `event_pid`
FROM `sys_log` WHERE (`tablename` = 'pages') OR (`tablename` = 'tt_content')
GROUP BY `event_pid`
ORDER BY max(`tstamp`)
DESC LIMIT 10

Also available in: Atom PDF