Bug #83074

The inSet (FIND_IN_SET) function generates invalid SQL

Added by Thanos no-lastname-given over 1 year ago. Updated over 1 year ago.

Status:
Closed
Priority:
-- undefined --
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2017-11-23
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
7.1
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

When building a custom query with getQueryBuilderForTable using the The inSet function in ExpressionBuilder.php, the SQL is wrong because the function returns the pair of fieldName - value in reverse order.

The solution is to change the line 364 of ExpressionBuilder.php to

return sprintf(
  'FIND_IN_SET(%s, %s)',
   $this->connection->quoteIdentifier($fieldName),
   $value
);

or to use plain sql to the query builder: 'FIND_IN_SET(el.element_type, '. $allowedTypes . ')', but with the addition single quotes in the argument string : "'x,c,z'"

patch.diff View (570 Bytes) Thanos no-lastname-given, 2017-11-23 19:34

History

#1 Updated by Michael Stucki over 1 year ago

  • Project changed from forge.typo3.org to TYPO3 Core
  • TYPO3 Version set to 9

Wrong project. Moving...

#2 Updated by Morton Jonuschat over 1 year ago

  • Category set to Database API (Doctrine DBAL)
  • Status changed from New to Needs Feedback
  • Priority changed from Must have to -- undefined --

The inSet function as provided by the ExpressionBuilder is meant to find database rows which have a certain value $value in a column $fieldName which contains a list of comma separated values. InSet() is used quite frequently in the core and is almost certainly working as expected.

If you are trying to find all rows which have a single value in a column which is a member of a set of values you should b using the ->in() expression.

Otherwise a comprehensive code example/a complete example of what you are trying to accomplish might be helpful.

#3 Updated by Thanos no-lastname-given over 1 year ago

  • TYPO3 Version changed from 9 to 8
  • PHP Version set to 7.1

I made an custom sql that looks like this:

  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('table');
        $queryBuilder->getRestrictions()->removeAll();
        $queryBuilder->select('el.uid')
            ->from('table', 'el')
            ->where($queryBuilder->expr()->inSet('el.element_type', $queryBuilder->createNamedParameter($allowedTypes,\PDO::PARAM_STR)));

This outputs: 

SELECT `el`.`uid` FROM `table` `el` WHERE FIND_IN_
   SET(:dcValue1, `el`.`element_type`)

which is an incorrect sql statement.

If i reverse the order of the return arguments inside the inSet function then i get:

SELECT `el`.`uid` FROM `table` `el` WHERE FIND_IN_
   SET(`el`.`element_type`, :dcValue1)

which is a valid sql query for the MySql that returns the correct values.

#4 Updated by Alexander Opitz over 1 year ago

@Thanos

As Morton already wrote, you are using the function wrong.

The two parameters of inSet which you are using inSet(string $fieldName, string $value, ...) are defined:
$fieldName ... The name of the field, which contains a comma separated string/set which will be searched for.
$value ... The element which is searched inside this set (This string SHOULD NOT contain commas).

As you are using a NamedParameter you do not get an exception while calling the inSet method as the NamedParameter can't be quantified at that moment.

You should use the in() method which is defined as:

$fieldName The field to search in
$value ... The comma separated string/set( or array which will be imploded) for which will be searched inside the given field.

#5 Updated by Georg Ringer over 1 year ago

  • Status changed from Needs Feedback to Closed

closed because of wrong usage + no feedback

Also available in: Atom PDF