Actions
Bug #79286
closedMultiple wrong joins in extbase database query if constraints use property paths
Start date:
2017-01-12
Due date:
% Done:
100%
Estimated time:
TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
Yes
Sprint Focus:
Stabilization Sprint
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);
Actions