Bug #88919

QueryBuilder with DefaultRestrictionContainer effectivly transforms outer joins to inner joins because the restrictions are applied to all join tables unconditionally

Added by Stefan P 10 months ago. Updated 8 days ago.

Status:
Under Review
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2019-08-05
Due date:
% Done:

0%

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

Description

In an outer join (left or right join) one of the sides can be null.

But when using the QueryBuilder with it's DefaultRestrictionContainer it adds all conditions to all tables. Which is fine for inner joins but breaks outer joins (by efectivley kicking out rows that are fetched by the outer join, leaving one with teh same result as the inner join).

Consider this example (scenario: content inline in content):

SELECT c1.*
FROM tt_content AS c1
LEFT JOIN tt_content AS c2 on c1.parent_field = c2.uid
WHERE (c2 IS NULL OR c2.hidden = 0);

This would fetch all content rows but leave out the children whose parent is hidden.

Another example would be a join on pages to find pages by some parent page property.

Now you want this query with correctly respected TYPO3 fields (enable fields, deleted) and use the QueryBuilder. The QueryBuilder will add the DefaultRestriction container. So far so good. It will add the Restrictions to all join tables however, no matter if they're inner or outer joined. So it adds

c1.hidden = 0 AND c2.hidden = 0

which implcitly removes all join rows where the outer side is NULL. Leaving you implicitly with the inner join result.

As soon as an explicit outer join is requested the DefaultRestrictionContainer should only be applied to the from-table, not to the join-tables - or it should also add a OR join_table.uid IS NULL to the RestrictionContainer in case of outer joins.

For single-table or inner-join queries nothing has to change.

I found this on TYPO3 9, but assume this is true for 8 and 10 as well.

In case this is intended (can't believe this) this should at least be documented prominently - found this only by manually debugging the generated SQL statement to this that my left join condition was "short-circuited" by the DefaultRestrictionContainer.


Related issues

Related to TYPO3 Core - Feature #87776: Limit Restriction to table/s in QueryBuilder Closed 2019-02-24
Related to TYPO3 Core - Bug #86385: QueryBuilder restrictions break leftJoin Closed 2018-09-26
Duplicated by TYPO3 Core - Bug #32539: Using a constraint or ordering on a property on the right side of a m:n relation causes records from the left side with no related elements (0 cardinality) to not show Closed 2011-12-14

History

#1 Updated by Manuel Selbach 10 months ago

  • Related to Feature #87776: Limit Restriction to table/s in QueryBuilder added

#2 Updated by Matthias Meusburger 9 months ago

  • Related to Bug #86385: QueryBuilder restrictions break leftJoin added

#3 Updated by Gerrit Code Review 8 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 https://review.typo3.org/c/Packages/TYPO3.CMS/+/61880

#4 Updated by Gerrit Code Review 5 months 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/c/Packages/TYPO3.CMS/+/61880

#5 Updated by Gerrit Code Review 5 months 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/c/Packages/TYPO3.CMS/+/61880

#6 Updated by Tobias Doll 3 months ago

  • Related to deleted (Bug #86385: QueryBuilder restrictions break leftJoin)

#7 Updated by Tobias Doll 3 months ago

  • Related to Bug #86385: QueryBuilder restrictions break leftJoin added

#8 Updated by Susanne Moog 2 months ago

  • Duplicated by Bug #32539: Using a constraint or ordering on a property on the right side of a m:n relation causes records from the left side with no related elements (0 cardinality) to not show added

#9 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 https://review.typo3.org/c/Packages/TYPO3.CMS/+/61880

#10 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 https://review.typo3.org/c/Packages/TYPO3.CMS/+/61880

#11 Updated by Manuel Selbach about 1 month ago

The related task has been merged for version 10 and is available in 10.4.

Futhermore, there is a "workaround" that works already with version 9, that is described in a PR for the official documentation https://github.com/TYPO3-Documentation/TYPO3CMS-Reference-CoreApi/pull/840

#12 Updated by MHO no-lastname-given 8 days ago

There is a new extension cc_dbal_leftjoin which fixes this behavior. Just install it and add true as additional parameter for leftJoin.
https://extensions.typo3.org/extension/cc_dbal_leftjoin/

//$queryBuilder->leftJoin($fromAlias, $join, $alias, $condition);
$queryBuilder->leftJoin($fromAlias, $join, $alias, $condition, true);

So SQL changes:

# SELECT * FROM table1 LEFT JOIN table2 ON table1.uid = table2.field WHERE table1.deleted = 0 AND table2.deleted = 0;
SELECT * FROM table1 LEFT JOIN table2 ON table1.uid = table2.field AND table2.deleted = 0 WHERE table1.deleted = 0

Also available in: Atom PDF