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 2 months ago. Updated 15 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 Under Review 2019-02-24
Related to TYPO3 Core - Bug #86385: QueryBuilder restrictions break leftJoin New 2018-09-26

History

#1 Updated by Manuel Selbach 2 months ago

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

#2 Updated by Matthias Meusburger about 2 months ago

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

#3 Updated by Gerrit Code Review 15 days 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

Also available in: Atom PDF