I'm getting a very strange behavior in MySQL, which looks like some kind of weird bug. I know it's common to blame the tried and tested tool for one's mistakes, but I've been going around this for a while.
I have 2 tables, I, with 2797 records, and C, with 1429. C references I.
I want to delete all records in I that are not used by C, so i'm doing:
select * from i where id not in (select id_i from c);
That returns 0 records, which, given the record counts in each table, is physically impossible. I'm also pretty sure that the query is right, since it's the same type of query i've been using for the last 2 hours to clean up other tables with orphaned records.
To make things even weirder...
select * from i where id in (select id_i from c);
DOES work, and brings me the 1297 records that I do NOT want to delete.
So, IN works, but NOT IN doesn't.
Even worse:
select * from i where id not in (
select i.id from i inner join c ON i.id = c.id_i
);
That DOES work, although it should be equivalent to the first query (i'm just trying mad stuff at this point).
Alas, I can't use this query to delete, because I'm using the same table i'm deleting from in the subquery.
I'm assuming something in my database is corrupt at this point.
In case it matters, these are all MyISAM tables without any foreign keys, whatsoever, and I've run the same queries in my dev machine and in the production server with the same result, so whatever corruption there might be survived a mysqldump / source cycle, which sounds awfully strange.
Any ideas on what could be going wrong, or, even more importantly, how I can fix/work around this?
Thanks!
Daniel
