MySQL Bugs: #2324: looks for spurious table when 'where clause' filters out all rows

Bug #2324	looks for spurious table when 'where clause' filters out all rows
Submitted:	8 Jan 2004 9:31	Modified:	8 Jan 2004 14:58
Reporter:	tim maggio	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.14	OS:	Windows (Windows 2000)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When running a very simple query against a very simple table and the filter eliminates all possible rows, the empty set is returned. This part works perfectly. However, in addition to this, the server throws an exception 1146 (no such table) with the following message:

Table 'misadav1.impossible where noticed after reading const tables' doesn't exist

How to repeat:
drop table hork
;

create table hork
(
     a int
)
;

insert into hork values( 1 )
;

select * from hork
where a = 2
;

Suggested fix:
In our real-life situation, there are 3 key fields in the table we are querying. When we got this problem, we created a spurious 4th key field and ignore it in our queries. This seems to get around this very odd problem.

For this test case, I created the table that MySQL claimed it could not find, and it worked fine.

Both these solutions are pretty lame.

changed synopsis

This sounds like you are actually reporting a MySQL Control Center problem, rather than a problem with the server.  Is that the case?

If so, this issue (already reported) should have been resolved in MySQLCC 0.9.4, so you should try that version.

I just downloaded 0.9.4 (actually the help menu says 0.9.4-beta) and reran the test case. The same bug exhibits itself. Is there something else I am missing?

But you are right about another thing. One of my co-workers said that the query works fine in the tool he is using to query mysql.

Alright.  I am marking this as a duplicate of #2195.

I am still unable to duplicate the issue, but I will try again.