Bug #77298

Updated by Markus Klein over 4 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. 
 # 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) 
         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 

 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. 

 The code was introduced with https://review.typo3.org/10188 

 h2. external resources