db_rewrite_sql breaks on sql having "order by" clause
rnealxp - February 5, 2009 - 13:22
| Project: | Drupal |
| Version: | 6.12 |
| Component: | database system |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
I'm using CCK and have a content type with a column that is displayed using a select list control (dropdown). I'm populating the control using PHP and the user wants to see the data listed alphabetically, thus I must use "order by". When passing the sql through db_rewrite_sql, the sql gets corrupted. Perhaps fix is to check for existence of "order by" clause and then move the clause to the end of the additional sql that gets appended to the original sql. I suggest also updating db_rewrite docs to tell developers to join to table {node} and use alias "n" for it, and mention other accepted alias' to prevent ambiguity.
#1
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 BYin a nestedSELECTstatement?I made a brief analisys and I have a possible explanation, can anyone confirm it?
If we have a nested query with an
ORDER BYin it, and noORDER BYin 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 withORDER BY, but it will happen also withGROUP BYandLIMIT.Regards,
Antonio