Bug #79286
closedMultiple wrong joins in extbase database query if constraints use property paths
100%
Description
I have the following constraint in my extbase repository:
$query->matching( $query->logicalOr([ // start and end are inside the period $query->logicalAnd([ $query->greaterThanOrEqual('dateCycles.dateStart', $start), $query->lessThanOrEqual('dateCycles.dateEnd', $end), ]), // starts before the period but ends in period $query->logicalAnd([ $query->lessThanOrEqual('dateCycles.dateStart', $start), $query->greaterThanOrEqual('dateCycles.dateEnd', $start), ]), // starts before period and ends after period $query->logicalAnd([ $query->lessThanOrEqual('dateCycles.dateStart', $end), $query->greaterThanOrEqual('dateCycles.dateEnd', $end), ]), // starts in period and ends after period $query->logicalAnd([ $query->greaterThanOrEqual('dateCycles.dateStart', $start), $query->lessThanOrEqual('dateCycles.dateEnd', $end), ]), ]) );
a got a pretty confusing result. I had absolutely no idea why this query returned just results that dont match any of this constraints, so i checked the Raw SQL query that was executed on the database:
SELECT `tx_myextension_domain_model_appointment`.* FROM `tx_myextension_domain_model_appointment` `tx_myextension_domain_model_appointment` LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle` ON `tx_myextension_domain_model_appointment`.`uid` = tx_myextension_domain_model_datecycle.appointment LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle0` ON `tx_myextension_domain_model_datecycle`.`uid` = tx_myextension_domain_model_datecycle0.appointment LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle1` ON `tx_myextension_domain_model_datecycle0`.`uid` = tx_myextension_domain_model_datecycle1.appointment LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle2` ON `tx_myextension_domain_model_datecycle1`.`uid` = tx_myextension_domain_model_datecycle2.appointment LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle3` ON `tx_myextension_domain_model_datecycle2`.`uid` = tx_myextension_domain_model_datecycle3.appointment LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle4` ON `tx_myextension_domain_model_datecycle3`.`uid` = tx_myextension_domain_model_datecycle4.appointment LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle5` ON `tx_myextension_domain_model_datecycle4`.`uid` = tx_myextension_domain_model_datecycle5.appointment LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle6` ON `tx_myextension_domain_model_datecycle5`.`uid` = tx_myextension_domain_model_datecycle6.appointment LEFT JOIN `tx_myextension_domain_model_datecycle` `tx_myextension_domain_model_datecycle7` ON `tx_myextension_domain_model_datecycle6`.`uid` = tx_myextension_domain_model_datecycle7.appointment WHERE (((((`tx_myextension_domain_model_datecycle`.`date_start` >= 1483225200) AND (`tx_myextension_domain_model_datecycle0`.`date_end` <= 1485817200)) OR ((`tx_myextension_domain_model_datecycle1`.`date_start` <= 1483225200) AND (`tx_myextension_domain_model_datecycle2`.`date_end` >= 1483225200))) OR ((`tx_myextension_domain_model_datecycle3`.`date_start` <= 1485817200) AND (`tx_myextension_domain_model_datecycle4`.`date_end` >= 1485817200))) OR ((`tx_myextension_domain_model_datecycle5`.`date_start` >= 1483225200) AND (`tx_myextension_domain_model_datecycle6`.`date_end` <= 1485817200))) AND (`tx_myextension_domain_model_appointment`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_appointment`.`deleted` = 0) AND (`tx_myextension_domain_model_appointment`.`hidden` = 0) AND (`tx_myextension_domain_model_appointment`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_appointment`.`endtime` = 0) OR (`tx_myextension_domain_model_appointment`.`endtime` > 1484216220)) AND (( (`tx_myextension_domain_model_appointment`.`fe_groups` = '') OR (`tx_myextension_domain_model_appointment`.`fe_groups` IS NULL) OR (`tx_myextension_domain_model_appointment`.`fe_groups` = 0) OR (FIND_IN_SET('0', `tx_myextension_domain_model_appointment`.`fe_groups`)) OR (FIND_IN_SET('-1', `tx_myextension_domain_model_appointment`.`fe_groups`))))) AND ( ((`tx_myextension_domain_model_datecycle`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle`.`uid` IS NULL)) AND ( ((`tx_myextension_domain_model_datecycle0`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle0`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle0`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle0`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle0`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle0`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle0`.`uid` IS NULL)) AND ( ((`tx_myextension_domain_model_datecycle1`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle1`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle1`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle1`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle1`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle1`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle1`.`uid` IS NULL)) AND ( ((`tx_myextension_domain_model_datecycle2`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle2`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle2`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle2`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle2`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle2`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle2`.`uid` IS NULL)) AND ( ((`tx_myextension_domain_model_datecycle3`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle3`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle3`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle3`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle3`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle3`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle3`.`uid` IS NULL)) AND ( ((`tx_myextension_domain_model_datecycle4`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle4`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle4`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle4`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle4`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle4`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle4`.`uid` IS NULL)) AND ( ((`tx_myextension_domain_model_datecycle5`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle5`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle5`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle5`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle5`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle5`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle5`.`uid` IS NULL)) AND ( ((`tx_myextension_domain_model_datecycle6`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle6`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle6`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle6`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle6`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle6`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle6`.`uid` IS NULL)) AND ( ((`tx_myextension_domain_model_datecycle7`.`sys_language_uid` IN (0, -1)) AND ( (`tx_myextension_domain_model_datecycle7`.`deleted` = 0) AND (`tx_myextension_domain_model_datecycle7`.`hidden` = 0) AND (`tx_myextension_domain_model_datecycle7`.`starttime` <= 1484216220) AND ((`tx_myextension_domain_model_datecycle7`.`endtime` = 0) OR (`tx_myextension_domain_model_datecycle7`.`endtime` > 1484216220)))) OR (`tx_myextension_domain_model_datecycle7`.`uid` IS NULL)) ORDER BY `tx_myextension_domain_model_datecycle7`.`date_start` ASC
I think there should be just one "LEFT JOIN" for each unique property. Here is the result from debugging the File /typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php and its property $this->tablePropertyMap:
array(9) { ["dateCycles"] => string(38) "tx_myextension_domain_model_datecycle" ["dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle0" ["dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle1" ["dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle2" ["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle3" ["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle4" ["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle5" ["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"] => string(39) "tx_myextension_domain_model_datecycle6" ["dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles.dateCycles"]=> string(39) "tx_myextension_domain_model_datecycle7" }
So it seems that the following method call is responsible for this issue:
(/typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php @Line #903)
$childTableAlias = $this->getUniqueAlias($childTableName, $fullPropertyPath);
Updated by Gerrit Code Review almost 8 years 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/51290
Updated by Gerrit Code Review almost 8 years 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/51290
Updated by Gerrit Code Review almost 8 years 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/51290
Updated by Markus Klein almost 8 years ago
- Target version set to 8.6
- Is Regression changed from No to Yes
- Sprint Focus set to Stabilization Sprint
Updated by Gerrit Code Review almost 8 years 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/51290
Updated by Dominique Kreemers over 7 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset a522617e2a549e8165671be5a78586e885db66f4.
Updated by Riccardo De Contardi about 7 years ago
- Status changed from Resolved to Closed