MySQL Forums
Forum List  »  Merge Storage Engine

query on MERGE table wrongly returns nothing
Posted by: Nathan Cheng
Date: May 17, 2006 12:21AM

Are there any circumstances under which the following results would not indicate the presence of a bug?

mysql> select count(*) from t0 where b between 1130745600 and 1146000000;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

mysql> show create table t0;
+-------+---------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------+
| t0 |CREATE TABLE `t0` (
`a` int(11) NOT NULL auto_increment,
`b` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`a`),
KEY `idx_1` (`b`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=FIRST UNION=(`t1`,`t2`,`t3`) |
+-------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from t1 where b between 1130745600 and 1146000000;
+----------+
| count(*) |
+----------+
| 817608 |
+----------+
1 row in set (1 min 13.74 sec)



This is happening currently in our 4.1.19 production environment, even after a flush tables/threads. Haven't tried a restart yet. I have no way to tell for sure, but I don't think it was always doing this; i.e. since our last restart/redeploy it was behaving correctly (I think).

We have been unsuccessful in trying to recreate this problem in a separate environment.

The issue is not with count(), as our actual query does not use count(), but has a much more complex select expression. No matter what the select expression I have used, the results are similarly wrong.

There are other numbers for the BETWEEN that work fine, so it seems there may be a problem accessing one specific table, however no errors appear anywhere.

Options: ReplyQuote


Subject
Views
Written By
Posted
query on MERGE table wrongly returns nothing
6223
May 17, 2006 12:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.