SQL error
ntt - January 27, 2009 - 12:20
| Project: | Taxonomy Query Language |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
I get the following error when I do a search from an exposed filter of a view:
user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS finalTable)) )' at line 1 query: SELECT count(node.nid) FROM node node INNER JOIN users users ON node.uid = users.uid LEFT JOIN node n ON node.nid = n.nid WHERE ((n.moderate != 1 OR n.uid = 37)) AND ( (node.type IN ('userinfo')) AND (node.nid in (SELECT * FROM (SELECT DISTINCT nid FROM `term_node` LEFT JOIN node n ON node.nid = n.nid WHERE ((n.moderate != 1 OR n.uid = 37)) AND ( tid in (44)) AS finalTable)) )
#1
Well, obviously the code for that module is somehow breaking and it appears to be duplicating the variables for the table names.
Here's the SQL code broken down:
SELECT count(node.nid)
FROM node node
INNER JOIN
users users
ON node.uid = users.uid
LEFT JOIN
node n
ON node.nid = n.nid
WHERE ((n.moderate != 1 OR n.uid = 37)) AND
( (node.type IN ('userinfo')) AND
(node.nid in (
SELECT *
FROM (SELECT DISTINCT nid
FROM `term_node'
LEFT JOIN
node n
ON node.nid = n.nid
WHERE ((n.moderate != 1 OR n.uid = 37)) AND
( tid in (44)) AS finalTable)
)
)
So this looks like at least part of the problem in the SQL:
FROM node node
INNER JOIN
users users
I could be wrong, as I don't really know anything in MySQL yet, but standard SQL coding, I would imagine would be that you don't mention a table twice. At best, the SQL engine should look at that, and think you're renaming the table with the same name, which wouldn't make sense, and might cause other issues b/c of the logic.
That's my guess, anyway. The only other thing I saw, maybe, was that the parentheses count might be off, but there again, it could just be b/c there are so many that it's hard to keep up with! ;0)
Hopefully this'll be of some help to the person(s) resolving the issue. If not, I apologize. I'm just guessing that a variable got mentioned twice in the code (php, I imagine), or either the code uses a variable AND the hard-coded table name next to each other - that would be an easy mistake.
#2
kjv1611: The second table name is the alias. This is ok in SQL. It allows you to join with the same table more than once as long as they have different aliases. Using table aliases in Drupal is recommended since you can install Drupal with a prefix which would change your table names all over. Using alias allow your SQL to work reguardless.
#3
Thanks for that clarification. It's probably something I just never learned or forgot, b/c I didn't immediately have a need. :0) It makes more sense when looking at it that way. So there IS a time when it is actually helpful to use an alias (with the same name) as the table! And I just figured it'd make the 'puter say, "huh?!"
;0)
#4
@ntt: Did you found what the problem was?
Are you use hook_db_rewrite_sql() to play with the SQL by any chance?