Bug #84303

Uncaught exception while executing query on Microsoft SQL Server

Added by Andrej K over 1 year ago. Updated about 1 year ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2018-03-15
Due date:
% Done:

100%

TYPO3 Version:
9
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

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  

Associated revisions

Revision a3828a4d (diff)
Added by Georg Ringer over 1 year ago

[BUGFIX] Add missing column in database query

To avoid exceptions on certain database systems the pid must be selected
as well as it is used in the order statement.

Resolves: #84303
Releases: master, 8.7
Change-Id: I1f41925d9c22c20a2fa3f080b85bb4d17d03a778
Reviewed-on: https://review.typo3.org/56571
Tested-by: TYPO3com <>
Reviewed-by: Simon Gilli <>
Reviewed-by: Anja Leichsenring <>
Tested-by: Anja Leichsenring <>
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>

Revision 11a9fb08 (diff)
Added by Georg Ringer over 1 year ago

[BUGFIX] Add missing column in database query

To avoid exceptions on certain database systems the pid must be selected
as well as it is used in the order statement.

Resolves: #84303
Releases: master, 8.7
Change-Id: I1f41925d9c22c20a2fa3f080b85bb4d17d03a778
Reviewed-on: https://review.typo3.org/56656
Tested-by: TYPO3com <>
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>

History

#1 Updated by Stephan GroƟberndt over 1 year ago

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

#2 Updated by Gerrit Code Review over 1 year ago

  • Status changed from New 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/56571

#3 Updated by Andrej K over 1 year ago

After applying changeset, there is this error:

Oops, an error occurred!

An exception occurred while executing 'SELECT [pages].[uid], [pages].[pid], COUNT AS [count], MAX AS [root_max_val], MIN 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, 8120]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Column 'pages.pid' is invalid in the select list 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.

#4 Updated by Gerrit Code Review over 1 year ago

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

#5 Updated by Gerrit Code Review over 1 year ago

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

#6 Updated by Gerrit Code Review over 1 year ago

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

#7 Updated by Gerrit Code Review over 1 year ago

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

#8 Updated by Georg Ringer over 1 year ago

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

#9 Updated by Benni Mack about 1 year ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF