TYPO3 Core

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