Hi, I am using windows 2000 server and MySql 4.07gamma.
I have a merge table, combining 2 tables. The tables both have only one index (non unique) which consist of 2 fields (both these fields are defined as char but in reality they only have integer values in them. Index Field 1 is char(1) and index field2 is char(3). Queries over the the individual tables produce results as expected however queries over the merge table often give an empty result set when they should not. The only way I can get the correct result in these cases on the merge file is to enter the second part of the where clause as Integer (ie without the quotes) IE select * from table1 where field1 = '2' and field2 = '100' > 50 records select * from table2 where filed1 = '2' and field2 = '100' > 30records Now the problem select * from MergeTable where field1 = '2' and field2 = '100' > 0 records ie empty result set BUT select * from MergeTable where field1 = '2' and field2 = 100 > 80 records (the correct result) IE by using field2 = 100 I get the correct result but not when I use field2 = '100' ( using the quotes) As field2 is defined as char I would have thought using the quotes is the correct syntax (and this works perfectly with the individual tables) As the query works fine on the individual tables I am sure the problem is with the merge table. To make sure the field definitions were 100% correct for the merge table I used mysqldump and then cut and paste. This was also the method I used to create table2 from table1, so I am quite sure all the table definitions are the same. Could this be a bug? Am I missing something here? Thanks Clyde England PS I get the feeling it is the index that is part of the problem, because if I remove the index and re-create the files, all is OK - but of course now much slower. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
