TYPO3 Core

Bug #77298

Updated by Markus Klein over 7 years ago

h2. setup 

 Model A has a relation to model B (eg categories). 
 Model A can be translated. 
 The relation to B is only maintained in the default language 
 We assume content_fallback mode. 

 h2. action 

 
 Fetching translated records for A which are assigned to B with uid 1. 

 h2. expected result 

 
 A list of A-records. 

 h2. actual result 

 
 Empty result 

 h2. issue 

 
 Invalid SQL code generated in Typo3DbQueryParser::getSysLanguageStatement 


 h2. affected versions 

 
 6.0 to 8 

 h2. example 

 
 ext:news with categories. 

 * Create a record in default language 
 * Assign a category to your news. 
 * Translate the news (but do not a assign a category in the translation, since this should be fetched from default lang) 
 * Create a plugin and select a category filter there 

 The result is "no news found". 

 This is an extraction of the invalid SQL generated with comments inside. 
 <pre> 
 # limit the result to the selected category uid 1 only 
 # this part only allows for news records of default language (correct query part!) 
 tx_news_domain_model_news.uid IN ( 
     SELECT uid_foreign FROM sys_category_record_mm  
     WHERE uid_local='1' AND sys_category_record_mm.fieldname = 'categories' 
     AND sys_category_record_mm.tablenames = 'tx_news_domain_model_news' 
 ) 

 # this part is the killer 
 AND ( 
    # either we allow only translated records (which never have a direct category relation) 
     tx_news_domain_model_news.sys_language_uid IN (1,-1) 
     OR ( 
   # or we allow news records, which do not have a translation in the language uid 1 at all (bad luck if all news are properly translated) 
         tx_news_domain_model_news.sys_language_uid=0 
         AND tx_news_domain_model_news.uid NOT IN ( 
             SELECT tx_news_domain_model_news.l10n_parent 
             FROM tx_news_domain_model_news  
             WHERE tx_news_domain_model_news.l10n_parent>0 
             AND tx_news_domain_model_news.sys_language_uid=1 
             AND tx_news_domain_model_news.deleted=0 
         ) 
     ) 
 ) 
 </pre> 

 h2. remarks 

 
 It is completely wrong to select records directly in the translated language in an overlay scenario as any other where condition (eg category selection above) of the query, which covers relation filtering, will always relate to the default language record. There current SQL excludes exactly those default records (since they have a translation) and all those where clauses fail. 

 h2. external resources 

 
 http://www.dmitry-dulepov.com/2015/01/strange-code-in-extbase-persistance.html

Back