We have moved to GitHub Issues
Created by Søren Gregersen 02 Mar 2017, 21:55:04 Updated by Søren Gregersen 03 Mar 2017, 02:17:02
Your report will have a greater chance of being addressed if you can give us clear steps to reproduce the issue, please answer the following questions in as much detail as possible:
'''What did you do?''''
I called the SplitSqlForPaging-method with a sql-statement that contained a quoted column-identifier in the order by clause string count, select, orderBy; var sql = "SELECT * FROM [table1] ORDER BY [table1].[Id]"; Umbraco.Core.Persistence.Database.SplitSqlForPaging(sql, out count, out select, out orderBy);
A workaround is to not quote the columns in the ORDER BY, but it seems like a good practice to quote them, when using generated / generic code.
'''What did you expect to happen?'''
That the output would remove the "order by" from the count SQL, and the values of the 3 output strings would be: count = "SELECT COUNT() FROM [table1]": select = " FROM [table1] ORDER BY [table1].[Id] "; orderBy = "ORDER BY [table1].[Id] ";
'''What actually happened?'''
The values of the 3 output strings are: count = "SELECT COUNT() FROM [table1] ORDER BY [table1].[Id]"; select =" FROM [table1] ORDER BY [table1].[Id] "; orderBy = null;
It seems like the regex from the PetaPoco (https://github.com/CollaboratingPlatypus/PetaPoco/blob/master/PetaPoco/Utilities/PagingHelper.cs#L19) works better:
Regex RegexOrderBy = new Regex(@"\bORDER\s+BY\s+(?!.?(?:)|\s+)AS\s)(?:((?>((?
""\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\[\]""\w().])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.RightToLeft | RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
Regex rxOrderBy = new Regex(@"\bORDER\s+BY\s+(?:((?>((?
var sql = "SELECT COUNT(*) FROM [table] ORDER BY [table].[Id]"; RegexOrderBy.IsMatch(sql); // true rxOrderBy.IsMatch(sql); // false
Backwards Compatible: True
Fix Submitted: Inline code
Due in version: