db_rewrite_sql breaks on sql having "order by" clause

Hi, I am not sure if I hit the same bug you have, but I am reusing this report because the symptoms look pretty much the same.

Is your ORDER BY in a nested SELECT statement?

I made a brief analisys and I have a possible explanation, can anyone confirm it?

If we have a nested query with an ORDER BY in it, and no ORDER BY in the main query, then db_rewrite_sql picks the one in the nested query and adds a spurious parenthesis breaking SQL syntax.

Here a sample query that shows the problem (exposed also by #436868: Improve query)

SELECT node.nid AS nid
 FROM {node} node 
 WHERE (node.promote <> 0) AND (node.status <> 0) AND (node.tnid IS NULL OR node.tnid = 0 OR node.nid=(
      SELECT nid FROM node AS stfh_node
      WHERE stfh_node.tnid=node.tnid
      ORDER BY
        CASE stfh_node.language
          WHEN \'***CURRENT_LANGUAGE***\' THEN 0
WHEN \'en\' THEN 1

          ELSE 2
        END
      LIMIT 1
      ))

It gets rewritten to:

SELECT  DISTINCT(node.nid) AS nid
 FROM {node} node 
  INNER JOIN {node_access} na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = \'all\'))) AND (  (node.promote <> 0) AND (node.status <> 0) AND (node.tnid IS NULL OR node.tnid = 0 OR node.nid=(
      SELECT nid FROM node AS stfh_node
      WHERE stfh_node.tnid=node.tnid
      )ORDER BY
        CASE stfh_node.language
          WHEN \'***CURRENT_LANGUAGE***\' THEN 0
WHEN \'en\' THEN 1

          ELSE 2
        END
      LIMIT 1
      ))

note the misplaced parenthesis near ORDER BY.

The problem should be caused by db_rewrite_sql logic, it picks the last ORDER BY, which might not be enough in case of nested queries. The example is with ORDER BY, but it will happen also with GROUP BY and LIMIT.

Regards,
Antonio

Closed. The reason: expired.