Project

General

Profile

Defect #27397

Updated by Toshi MARUYAMA about 7 years ago

When adding custom field in issues list, 
 USING MS SQL 2016 

 <pre> 
 generates query  
 SELECT custom_values.*, cv.cnt, cv.field_format FROM [custom_values] INNER JOIN 
     ( 
 ERROR 1 --- 	 SELECT cv.id 
			 cv.cnt, 
			 cv.field_format 
	 FROM 
	 ( 
	 SELECT cv.id, 
			 cv_m.cnt, 
			 cf.field_format, 
			 case when cf.multiple = 1 and cf.acl_trim_multiple = 1 then 1 else 0 end as mlt, 
 ERROR 2 --- 			 ROW_NUMBER() OVER (PARTITION BY i.id, cv.custom_field_id, ORDER BY i.id, cv.custom_field_id, cv.id) as row_number 
	 FROM custom_values cv 
			 INNER JOIN custom_fields cf on cf.id = cv.custom_field_id 
			 INNER JOIN issues i on i.id = cv.customized_id 
			 INNER JOIN (SELECT COUNT(1) as cnt, cv.custom_field_id, cv.customized_id FROM custom_values cv WHERE cv.customized_type = ''Issue'' and cv.custom_field_id IN (1) and cv.customized_id IN (1015,1012,1010,1005,49,48,46,44,41,29,28,27,23,22,18,13,11,10,9,8,7) GROUP BY cv.custom_field_id, cv.customized_id) cv_m on cv_m.custom_field_id = cf.id and cv_m.customized_id = i.id 
	 WHERE cv.customized_type = ''Issue'' 
		 and cv.custom_field_id IN (1) 
		 and i.id IN (1015,1012,1010,1005,49,48,46,44,41,29,28,27,23,22,18,13,11,10,9,8,7) 
 ERROR 3 --- 	 ORDER BY i.id, cv.custom_field_id, cv.id 
	 ) cv 
	 WHERE cv.mlt = 0 OR cv.row_number <= 3 
	 ) cv on cv.id = custom_values.id    ORDER BY [custom_values].[customized_id] ASC, [custom_values].[custom_field_id] ASC, [custom_values].[id] ASC 
 
 with 3 errors.  
 </pre> 

  

 1) no comma after id 
 2) ROW_NUMBER() OVER (PARTITION BY i.id, cv.custom_field_id, ORDER BY i.id, cv.custom_field_id, cv.id) as row_number 
 the comma doesn't be before order by 
 3)    ORDER BY  
 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Back