#84303Uncaught exception while executing query on Microsoft SQL Server
Description
Description
There is an uncaught exception while accessing template tools on Microsoft SQL Server
Prerequisites
Environment is set up in following way:
- TYPO3 version: 9.1.0
- Webserver: Microsoft-IIS/10.0
- PHP version: 7.2.3
- Database: MS SQL Server 12.0.2000.8
- OS: Windows 10
- TYPO3 was installed using composer (typo3/cms)
- Microsoft Drivers for PHP for SQL Server (php_sqlsrv.dll) version 5.2.0-RC from here:
https://github.com/Microsoft/msphpsql/releases
Test
1) Install TYPO3 using environment above
2) Apply patchsets from https://review.typo3.org/#/c/56118/ and https://forge.typo3.org/issues/84236
3) Login to backend
4) Click on Template in left menu
5) Click on root element in website tree structure menu
Results
After these steps I receive following error:
Oops, an error occurred!
An exception occurred while executing 'SELECT [pages].[uid], COUNT(*) AS [count], MAX([sys_template].[root]) AS [root_max_val], MIN([sys_template].[root]) AS [root_min_val] FROM [pages], [sys_template] WHERE ([pages].[uid] = [sys_template].[pid]) AND ((([pages].[deleted] = 0) AND ([sys_template].[deleted] = 0)) AND ((([pages].[t3ver_wsid] = 0) OR ([pages].[t3ver_state] <= 0)) AND (([sys_template].[t3ver_wsid] = 0) OR ([sys_template].[t3ver_state] <= 0)))) GROUP BY [pages].[uid] ORDER BY [pages].[pid] ASC, [pages].[sorting] ASC': SQLSTATE [42000, 8127]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Column "pages.pid" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared.
And this is in error log:
Core: Exception handler (WEB): Uncaught TYPO3 Exception: An exception occurred while executing 'SELECT [pages].[uid], COUNT(*) AS [count], MAX([sys_template].[root]) AS [root_max_val], MIN([sys_template].[root]) AS [root_min_val] FROM [pages], [sys_template] WHERE ([pages].[uid] = [sys_template].[pid]) AND ((([pages].[deleted] = 0) AND ([sys_template].[deleted] = 0)) AND ((([pages].[t3ver_wsid] = 0) OR ([pages].[t3ver_state] <= 0)) AND (([sys_template].[t3ver_wsid] = 0) OR ([sys_template].[t3ver_state] <= 0)))) GROUP BY [pages].[uid] ORDER BY [pages].[pid] ASC, [pages].[sorting] ASC': SQLSTATE [42000, 8127]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Column "pages.pid" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared. | Doctrine\DBAL\DBALException thrown in file C:\inetpub\wwwroot\vendor\doctrine\dbal\lib\Doctrine\DBAL\DBALException.php in line 179. Requested URL: http://localhost/typo3/index.php?route=%%2Fweb%%2Fts%%2F&token=0aca404dd6e98fbbaebacebc4dfb04c0960f275c