db_rewrite_sql breaks with from_unixtime

I marked this as 4.7, but I think this is still a problem in HEAD.

We have been struggling in views for several months with recurring problems of queries using from_unixtime that break when run through db_rewrite_sql. Earl suggested I try to get it down to the simplest possible reproducable use case and I have finally broken it down into a simple query using core tables that will always break:

<?php
$query = "SELECT * FROM {node} node LEFT JOIN {comments} comments ON node.nid = comments.nid WHERE from_UNIXTIME(node.created)";
$primary_table = 'node';
$primary_field = 'nid';

$query = db_rewrite_sql($query, $primary_table, $primary_field,  array());
?>

The above query will return as:

SELECT * FROM {node} node LEFT JOIN {comments} comments ON DISTINCT(node.nid) = comments.nid WHERE from_UNIXTIME(node.created)

Which is, of course, invalid sql. The pattern I finally came up with that will break is a join of any two fields with a where clause that uses FROM_UNIXTIME. That's it. Remove the join and it works fine. Remove the FROM_UNIXTIME and it works fine.

I found numerous issues related to problems with FROM_UNIXTIME and an interesting one was http://drupal.org/node/28577 which suggested that maybe the 'FROM' in FROM_UNIXTIME was getting confused with the 'FROM' part of the query. That particular issue seems to be resolved, but it still might be a clue to the problem.

Other examples of reports of this problem:

http://drupal.org/node/49157
http://drupal.org/node/67927
http://drupal.org/node/76806
http://drupal.org/node/78027