Coldfusion v2016 using SQL server query using order by right causes error.

[rpschwar27]

Have query that worked until v2016.  Using order by right(LogNumber,3) DESC get error:

Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'right'

query:

  SELECT LogNumber

  FROM tblMain

  WHERE (Program = 'Apple')

  ORDER BY right(LogNumber,3) DESC

[Carl Von Stetten]

Have you applied Update 1 yet?  If so, was this working before the update?

I wonder if it is somehow related to this similar CF11 Update 8 bug: Bug#4153580 - Coldfusion 11 update 8 breaks left and right sql sever functions

[rpschwar27]

I had already installed the Update1 so I uninstalled it and it still does not work.  I looked at the bug#4153580 and that appears to me my problem.  I have determined that if you have a right in the query it works, it's only if you have it in the order by that it doesn't work.

[WolfShade]

Can you add another column to your select and order by on that?

  SELECT LogNumber, right(LogNumber,3) as rlog
  FROM tblMain
  WHERE (Program = 'Apple')
  ORDER BY rlog DESC

HTH,

^_^

[rpschwar27]

That works but should not have to do that.  If you look at the log for bug #4153580, it says they had the same problem after doing update8 on v11.  I just did update8 on another machine running v11 and now I have the problem on that system also.  It appears the problem was introduced in update8 and continued with all v2016.