Development MySQL EXPLAIN says "impossible" and i dont know why

Heres the query im trying to run, get 0 results even though i expect at least one. Ran it through EXPLAIN and it said "Impossible WHERE noticed after reading const tables"

Code:

SELECT `watch`.`what` , `threads`.`forum` , `threads`.`title` , `posts`.`when` , `users`.`username` , `profiles`.`display`
FROM `watch`
INNER JOIN `threads` ON `watch`.`what` = `threads`.`id`
INNER JOIN `posts` ON `threads`.`last` = `posts`.`id`
INNER JOIN `users` ON `posts`.`who` = `users`.`id`
INNER JOIN `profiles` ON `users`.`default` = `profiles`.`id`
WHERE `watch`.`who` = '282'
AND `watch`.`type` = 'thread'
AND `posts`.`when` > '1222501580'
ORDER BY `posts`.`when` DESC

watch.who will eventually be a variable as will posts.when

posts.when is a unix timestamp of some point in the past

Im not understanding what the problem is.

im not seing whats so impossible in my where statement. (and yes i know im kind of re-inventing the wheel here, and yes its for BB system)
please point out the more then likly obvious mistake that i have missed.

 

You're taking it too literally - it just means there's currently no data in the table that'll ever match your where clause, or the expression you've given will never evaluate true as

E.g., if you have this table:

Code:

id | name
-----------
1  | draxin
2  | rtt

then SELECT * FROM table WHERE id = 666 will produce 'Impossible WHERE noticed...'. You'd get the same if you did 'SELECT * FROM table where 1=0'. It's impossible to get anything out of the query for those two examples.

Take a closer look at your query & the data you have available in the table and you'll soon figure it out.

 

whats funny is i just tried it again but replaced my WHERE statement with "WHERE 1" and it still says "Impossible WHERE noticed after reading const tables"

so im really confused, because WHERE 1 should return the data requested for every row in watch, right?

Code:

 EXPLAIN SELECT `watch`.`what` , `threads`.`forum` , `threads`.`title` , `posts`.`when` , `users`.`username` , `profiles`.`display`
FROM `watch`
INNER JOIN `threads` ON `watch`.`what` = `threads`.`id`
INNER JOIN `posts` ON `threads`.`last` = `posts`.`id`
INNER JOIN `users` ON `posts`.`who` = `users`.`id`
INNER JOIN `profiles` ON `users`.`default` = `profiles`.`id`
WHERE 1 

it couldnt be a problem with my inner joins could it? if it was it would kick an error for that and not an impossible where, right?

 

In that case it's coming from one of your joins

 

i dont see how but i trust your judgment and have to admit that i dont see how it could be anything else.
=(

back to digging through data

 

well, With your joins, the ON is effectively a where.

 

Could you show us your table structure? That number of joins can be a bit crazy (performance aside), try maybe removing them all and adding them back in one at a time until you find the problematic join. Could be you used an inner when it should have been an outer or a left, something like that.

 

yep, exactly

Draxin, this:

select [columns]
from table_a, table_b
where table_a.id = table_b.id

is the same as:

select [columns]
from table_a
inner join table_b ON table_b.id = table_a.id

so you can see why you're getting a "where" notice even if your final "where" is not impossible

 

when i tried

Code:

select [columns]
from table_a, table_b
where table_a.id = table_b.id

(while using the query above with proper table names in place of table_a and table_b)

MySQL spit back an error on the FROM saying that 'table_b' is "Not unique table/alias: 'table_b'"

and the table structures are rather large

 

yeah, im starting to think there is something fundamentaly wrong with our MySQL server setup

iv had trouble in the past where ill do an INSERT and it will do the insert 3 or more times, and just for S.A.G. i tried adding a LIMIT to the insert (which shouldnt be allowed) and sure enough it fixed the problem, but then a couple of days latter it started throwing errors on the LIMIT in the INSERT.

so i think our MySQL setup is foobared or just crazy

 

How are you running the INSERT? Command-line? PMA? Internal web app? If it's something you made in-house, there could be a weird loop occuring or any other number of odd problems. For all of PMA's shortcomings, I've never seen it fire off multiple INSERTs (could happen if you've got a weird proxy server or something, I guess) but if it's happening at the command line then there's definitely something very screwed up.

Also, are all of your column names distinct across the different tables? You may need to do something like:
SELECT a.id AS a_id, b.id AS b_id FROM a, b WHERE a.id=b.a_id
Or in a more practical example:
SELECT content.id AS contentid, content.name AS contentname, authors.id AS authorid, authors.name AS authorname FROM content JOIN authors ON content.author_id = authors.id; -- (alternately, FROM content, authors WHERE content.authorid=authors.id)

 

they arent distinct across tables but thats why im calling them via table.column
and where they overlap im using an AS.

but in the above query all the columns called are unique in the select statement.

and the insert is being done via website and as for the chance of looping, there isnt a loop involved.