TYPO3 Core

Additional information:

PostgreSQL fails the query with the error message:

ERROR:  function find_in_set(unknown, bigint) does not exist

The function is defined with arguments find_in_set(unknown, bigint)

A find_in_set() on an integer column doesn't make any sense, even on MySQL.

The query originates at \TYPO3\CMS\Core\Tree\TableConfiguration\DatabaseTreeDataProvider::getChildrenUidsFromParentRelation, line #382 where a query for a child with possibly multiple parents is being created

The problem seems to be in \TYPO3\CMS\Core\Database\DatabaseConnection:exec_SELECTgetRows().
There, a query is performed but sql_fetch_assoc() returns a 'false' on it.

Background information for the Patch under review:

The impact of the explicit cast on MySQL seems very low according to the query profile. The dataset used for the set was a tree consisting of 100.000 nodes, 100 of which were on the top level,
the rest randomly distributed on varying levels of depth.

mysql> SHOW PROFILES;
+----------+------------+-----------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                         |
+----------+------------+-----------------------------------------------------------------------------------------------+
|        1 | 0.03615300 | SELECT uid FROM sys_category WHERE FIND_IN_SET('0', parent) != 0 OR CAST(parent AS CHAR) = '' |
|        2 | 0.03553800 | SELECT uid FROM sys_category WHERE FIND_IN_SET('0', parent) != 0 OR parent = ''               |
+----------+------------+-----------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

Using FIND_IN_SET() make indexes unusable so there is no difference according to EXPLAIN for a statement:

mysql> EXPLAIN SELECT uid FROM sys_category WHERE FIND_IN_SET('0', parent) != 0 OR parent = '';
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+--------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows   | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sys_category | index | category_parent | category_parent | 4       | NULL | 100479 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+--------+--------------------------+
1 row in set (0,00 sec)

mysql> EXPLAIN SELECT uid FROM sys_category WHERE FIND_IN_SET('0', parent) != 0 OR CAST(parent AS CHAR) = '';
+----+-------------+--------------+-------+---------------+-----------------+---------+------+--------+--------------------------+
| id | select_type | table        | type  | possible_keys | key             | key_len | ref  | rows   | Extra                    |
+----+-------------+--------------+-------+---------------+-----------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sys_category | index | NULL          | category_parent | 4       | NULL | 100479 | Using where; Using index |
+----+-------------+--------------+-------+---------------+-----------------+---------+------+--------+--------------------------+
1 row in set (0,01 sec)

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

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

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

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

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

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