Drupal 7 db_query using CAST
It's not a Drupal error it's a MySQL error. CAST() won't accept INT as a valid type but does give you a choice between SIGNED and UNSIGNED to cast to an integer type. 

The following query runs fine for me in Drupal:

SELECT * FROM node ORDER BY CAST(nid AS UNSIGNED)