Bug #67155

Invalid SQL Query for sys_categories

Added by Morton Jonuschat 2 months ago. Updated 12 days ago.

Status:Resolved Start date:2015-05-26
Priority:Should have Due date:
Assigned To:- % Done:

100%
Category:DBAL Spent time: -
Target version:-
TYPO3 Version:7 Is Regression:No
PHP Version:5.6 Sprint Focus:
Complexity:

Description

DBAL/AdoDB Error message:

#1421053336: ADOdb could not run this query: SELECT "uid" FROM "sys_category" WHERE FIND_IN_SET('0', "parent") != 0 OR "parent" = ''

Environment: TYPO3 7.3-dev, PostgreSQL 9.4

How to reproduce: Open the „Backend User Listing” and try to add a new account.

Related issues

related to Core - Bug #46271: DB-error with postgreSQL Resolved 2013-03-14
related to Core - Epic #64459: Make TYPO3 run on PostgreSQL New 2015-01-23
related to Core - Bug #67172: AdoDB error on editing a page Resolved 2015-05-27

Associated revisions

Revision 14f04a6a
Added by Morton Jonuschat 12 days ago

[BUGFIX] dbal: Cast field to CHAR for FIND_IN_SET()

Implement explicit casting of fields to a character representation.
Most DBMS are stricter in regard to data type checking and emit an
error when trying to use FIND_IN_SET() on non-text field types.

On the DBAL side of things the DBMS specifics are used to define that
an explicit cast is required for FIND_IN_SET() so that a query including
the CAST statement gets generated.

A PostgreSQL Specific has been added to enable the explicit casting in
conjuction with DBAL. To avoid checking repeatedly if a DBMS has defined
specific requirements a NullSpecific has been implemented that gets used
as a default.

In the DatabaseTreeDataProvider the listFieldQuery() function has been
changed to use an explicit CAST instead of relying on the implicit
cast done by MySQL when comparing it to an empty string.

The SqlParser has been extended with the support for CAST.

Resolves: #67155
Resolves: #67172
Resolves: #46271
Releases: master, 6.2
Change-Id: Ic77d1700e0fb4e3723c90b34e131dafb456038e0
Reviewed-on: http://review.typo3.org/39779
Reviewed-by: Andreas Fernandez <>
Tested-by: Andreas Fernandez <>
Reviewed-by: Markus Klein <>
Tested-by: Markus Klein <>

Revision a780e46a
Added by Morton Jonuschat 12 days ago

[BUGFIX] dbal: Cast field to CHAR for FIND_IN_SET()

Implement explicit casting of fields to a character representation.
Most DBMS are stricter in regard to data type checking and emit an
error when trying to use FIND_IN_SET() on non-text field types.

On the DBAL side of things the DBMS specifics are used to define that
an explicit cast is required for FIND_IN_SET() so that a query including
the CAST statement gets generated.

A PostgreSQL Specific has been added to enable the explicit casting in
conjuction with DBAL. To avoid checking repeatedly if a DBMS has defined
specific requirements a NullSpecific has been implemented that gets used
as a default.

In the DatabaseTreeDataProvider the listFieldQuery() function has been
changed to use an explicit CAST instead of relying on the implicit
cast done by MySQL when comparing it to an empty string.

The SqlParser has been extended with the support for CAST.

Resolves: #67155
Resolves: #67172
Resolves: #46271
Releases: master, 6.2
Change-Id: Ic77d1700e0fb4e3723c90b34e131dafb456038e0
Reviewed-on: http://review.typo3.org/39779
Reviewed-by: Andreas Fernandez <typo3@scripting-base.de>
Tested-by: Andreas Fernandez <typo3@scripting-base.de>
Reviewed-by: Markus Klein <markus.klein@typo3.org>
Tested-by: Markus Klein <markus.klein@typo3.org>
Reviewed-on: http://review.typo3.org/41842

History

#1 Updated by Morton Jonuschat 2 months ago

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.

#2 Updated by Morton Jonuschat 2 months ago

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

#3 Updated by alexis nicolas 2 months ago

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.

#4 Updated by Gerrit Code Review 2 months 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 http://review.typo3.org/39779

#5 Updated by Morton Jonuschat 2 months ago

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)

#6 Updated by Gerrit Code Review 2 months ago

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

#7 Updated by Gerrit Code Review about 1 month ago

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

#8 Updated by Gerrit Code Review about 1 month ago

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

#9 Updated by Gerrit Code Review about 1 month ago

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

#10 Updated by Gerrit Code Review about 1 month ago

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

#11 Updated by Gerrit Code Review 18 days ago

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

#12 Updated by Morton Jonuschat 12 days ago

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

#13 Updated by Gerrit Code Review 12 days ago

  • Status changed from Resolved to Under Review

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

#14 Updated by Morton Jonuschat 12 days ago

  • Status changed from Under Review to Resolved

Also available in: Atom PDF