[RESOLVED] mysql says column doesnt exist!

[cornercuttinjoe]

im trying to install the blog software blur6ex, and i am getting an error.

I located the problem, and it has to do with this query right here:

SELECT blog.title,blog.replyID, blog.user, blog.date, users.realname FROM blog, users INNER JOIN permissionGroups ON blog.permission = permissionGroups.pGroup WHERE (permissionGroups.username='anonymous' AND blog.replyID<>0) GROUP BY blog.title ORDER BY blog.id DESC LIMIT 0,5;

The error received is:
ERROR 1054 (42S22): Unknown column 'blog.permission' in 'on clause'

What I don't get is that the blog table has a permission column.

Here is the blog table:

mysql> describe blog;
+-----------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(100) | YES | | NULL | |
| body | text | YES | MUL | NULL | |
| user | varchar(50) | YES | | NULL | |
| date | int(11) | YES | | NULL | |
| replyID | int(11) | YES | | NULL | |
| permission | varchar(50) | YES | | NULL | |
| userID | int(11) | YES | | NULL | |
| category | varchar(30) | YES | | NULL | |
+----------------+-----------------+------+-----+---------+----------------+

As you can see, there is a permission column.

Anyone have any ideas? When I was trying to isolate the problem and looking at the queries the system was performing, I found this query works fine:
SELECT blog.title,blog.ID FROM blog INNER JOIN permissionGroups ON blog.permission=permissionGroups.pGroup WHERE (permissionGroups.username='anonymous' AND blog.replyID=0) ORDER BY blog.date DESC LIMIT 0,10;

It has a blog.permission reference in the ON clause, and it works fine. Why doesnt the other?

Any help would be great. Thanks a lot.

[cornercuttinjoe]

well, i found a temporary fix. i dont know how it will affect the system, but if i reverse the blog, users in the FROM part of the statement, things seem to work alright, so it makes the query look like..

SELECT blog.title,blog.replyID, blog.user, blog.date, users.realname FROM users, blog INNER JOIN permissionGroups ON blog.permission=permissionGroups.pGroup WHERE (permissionGroups.username='anonymous' AND blog.replyID<>0) GROUP BY blog.title ORDER BY blog.id DESC LIMIT 0,5

i will also alert the blur6ex people about this.

[Sxooter]

Let me guess, you're running MySQL 5? Used to be, back in the day, it would join any way it had to to make things work. However, according to the SQL spec, joins take priority over commas in join order. So, your query is broken.

See, you're saying to select from "users, blog inner join permissionsGroups on ..." originally.

So, the first thing that fires is the join on. So, the query planner tries to join blog to permissionGroups. But it can't join them on blog.permission=permissionGroups.pGroup because it hasn't added in the blog entry yet.

So, fix your query. Join from "users inner join permissionGroups on (blog.permission=permissionGroups.pGroup join users on something or other.

MySQL is slowly becoming correct. Broken queries that used to just happen to work are going to start breaking more and more. And folks who wrote those queries are gonna have a lot of work ahead of them.

[cornercuttinjoe]

thanks a lot for the help. i will fix it.

[altumdesign]

Hey,

I'm having the same problem, but I can't seem to find where that query is programmed. Can you tell me which file it's in. It's much appreciated.

Thanks,

[altumdesign]

Finally. It's in m_recent_comments.php

I switched around the two tables and indeed it now works.

Thanks a lot, I would never have had the patience to find the fix.