Project

General

Profile

Actions
Copy link

Bug #75822

closed

Epic #75555: Further Doctrine Development

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

Added by Christoph Kratz about 8 years ago. Updated over 5 years ago.

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

100%

Estimated time:
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 1 (0 open — 1 closed)

Related to TYPO3 Core - Bug #75824: Doctrine: migrate ext:tstemplate/TypoScriptTemplateModuleControllerClosedChristoph Kratz2016-04-20

Actions
Actions
Copy link
 #1

Updated by Morton Jonuschat about 8 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
Actions
Copy link
 #2

Updated by Gerrit Code Review about 8 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

Actions
Copy link
 #3

Updated by Benni Mack almost 8 years ago

  • Target version changed from 8.1 to 8.2
Actions
Copy link
 #4

Updated by Morton Jonuschat almost 8 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
Actions
Copy link
 #5

Updated by Stephan Bauer over 6 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

Actions
Copy link
 #6

Updated by Morton Jonuschat over 6 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.

Actions
Copy link
 #7

Updated by Riccardo De Contardi over 6 years ago

  • Status changed from Resolved to Closed
Actions
Copy link
 #8

Updated by Harald Holzmann over 5 years ago

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

Actions
Copy link
 #9

Updated by Harald Holzmann over 5 years 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

Actions
Copy link

Also available in: Atom PDF