#91359foreign_table_where subqueries not working any more
Description
Before TYPO3 9 i had configs like the following:
'foreign_table_where' => 'pages.uid IN (SELECT pid FROM fe_users GROUP BY fe_users.pid)'
with the current Version 9.5.16 it is not possible anymore.
As a quick fix I have to add another GROUP BY: pages.uid IN (SELECT pid FROM fe_users GROUP BY fe_users.pid) GROUP BY uid
The issue is in typo3/sysext/backend/Classes/Form/FormDataProvider/AbstractItemProvider.php:1192. The regex just takes the first occurence of a “GROUP BY” statement.
I already have a fix for the regular expression.
before:
/^(.*)[[:space:]]+GROUP[[:space:]]+BY[[:space:]]+([[:alnum:][:space:],._()"]+)$/is
after:
/^(.*)[[:space:]]+(GROUP[[:space:]]+BY[[:space:]])(?=(((?!\)).)*\()|[^()]*$)+([[:alnum:][:space:],._()"]+)$/is
I added a positive lookahead after a “GROUP BY” is found to check whether it is between “(.*)”.
The same “issue” applies for useage of “LIMIT” and “ORDER BY”.