MySQL Forums
Forum List  »  Merge Storage Engine

MERGE Table Problem
Posted by: Ray Anderson
Date: April 06, 2005 01:51PM

Hello,

Sorry for the cross post. I posted this originally in the newbie section, but realized it should probably go here.

I think I may have uncovered a bug in the merge table handler, and was wondering if I just wasn't doing something stupid.

I have a data set that I cannot retrieve using a date between x and y clause when selecting from the merged table. If I select from one of the normal tables directly, the query works. I've ran the queries several times against the merge table and the individual tables to verify the data is there.

Below is a synopsis of my setup, and the queries I used to find this issue.

Any help is greatly appreciated.

Many thanks,

Ray

TWO BROKEN QUERIES
----------------------
QUERY
------------------------
SELECT modified, what, value, type, UNIX_TIMESTAMP(modified) as modified_ts
FROM minutes
WHERE 1
AND (what IN ('output_dsl_pct'))
AND (modified BETWEEN '2005-04-04 12:00:00' AND '2005-04-05 18:25:58')
ORDER BY what, modified

EXPLAIN
------------------------
1 SIMPLE minutes range what,what_2,modified what 38 NULL 624 Using where; Using filesort

PRODUCES
------------------------
0 rows

NOTE: If I change the FROM minutes to FROM 2005_04_minutes, I get the expected results

QUERY
------------------------
SELECT modified, what, value, type, UNIX_TIMESTAMP(modified) as modified_ts
FROM minutes
WHERE 1
AND (what IN ('output_dsl_pct'))
AND (modified >= '2005-04-04 12:00:00' and modified <= '2005-04-05 18:25:58')
ORDER BY what, modified DESC

EXPLAIN
------------------------
1 SIMPLE minutes range what,what_2,modified what 38 NULL 626 Using where; Using filesort

PRODUCES
------------------------
0 rows


WORKING QUERIES
------------------------
QUERY
------------------------
SELECT modified, what, value, type, UNIX_TIMESTAMP(modified) as modified_ts
FROM minutes
WHERE 1
AND (what IN ('output_dsl_pct'))
AND (modified >= '2005-04-04 12:00:00')
ORDER BY what, modified DESC

EXPLAIN
------------------------
1 SIMPLE minutes ref what,what_2,modified what 30 const 496 Using where; Using filesort

PRODUCES
------------------------
Showing rows 0 - 29 (965 total, Query took 0.0133 sec)

2005-04-05 18:24:00 output_dsl_pct 0.51 avg 1112750640
2005-04-05 18:22:00 output_dsl_pct 0.97 avg 1112750520
2005-04-05 18:20:00 output_dsl_pct 13.47 avg 1112750400
2005-04-05 18:18:00 output_dsl_pct 0.27 avg 1112750280
2005-04-05 18:16:00 output_dsl_pct 0.29 avg 1112750160
2005-04-05 18:14:00 output_dsl_pct 0.74 avg 1112750040
.
.
.

QUERY
------------------------
SELECT modified, what, value, type, UNIX_TIMESTAMP(modified) as modified_ts
FROM minutes
WHERE 1
AND (what IN ('output_dsl_pct'))
AND (modified <= '2005-04-05 18:25:58')
ORDER BY what, modified DESC

EXPLAIN
------------------------
1 SIMPLE minutes ref what,what_2,modified what 30 const 496 Using where; Using filesort

PRODUCES
------------------------
Showing rows 0 - 29 (5351 total, Query took 0.0499 sec)

2005-04-05 18:24:00 output_dsl_pct 0.51 avg 1112750640
2005-04-05 18:22:00 output_dsl_pct 0.97 avg 1112750520
2005-04-05 18:20:00 output_dsl_pct 13.47 avg 1112750400
2005-04-05 18:18:00 output_dsl_pct 0.27 avg 1112750280
2005-04-05 18:16:00 output_dsl_pct 0.29 avg 1112750160
2005-04-05 18:14:00 output_dsl_pct 0.74 avg 1112750040
.
.
.


TABLE STRUCTURES
------------------------
-- phpMyAdmin SQL Dump
-- version 2.6.1-pl3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 05, 2005 at 08:17 PM
-- Server version: 4.1.10
-- PHP Version: 4.3.10
--
-- Database: `server_stats`
--

-- --------------------------------------------------------

--
-- Table structure for table `2005_03_minutes`
--

CREATE TABLE `2005_03_minutes` (
`what` varchar(30) NOT NULL default '',
`value` double NOT NULL default '0',
`type` enum('sum','avg') NOT NULL default 'sum',
`modified` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`what`,`modified`),
KEY `what` (`what`),
KEY `modified` (`modified`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `2005_04_minutes`
--

CREATE TABLE `2005_04_minutes` (
`what` varchar(30) NOT NULL default '',
`value` double NOT NULL default '0',
`type` enum('sum','avg') NOT NULL default 'sum',
`modified` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`what`,`modified`),
KEY `what` (`what`),
KEY `modified` (`modified`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `minutes`
--

CREATE TABLE `minutes` (
`what` varchar(30) NOT NULL default '',
`value` double NOT NULL default '0',
`type` enum('sum','avg') NOT NULL default 'sum',
`modified` datetime NOT NULL default '0000-00-00 00:00:00',
KEY `what` (`what`,`modified`),
KEY `what_2` (`what`),
KEY `modified` (`modified`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`2005_03_minutes`,`2005_04_minutes`);

Options: ReplyQuote


Subject
Views
Written By
Posted
MERGE Table Problem
6112
April 06, 2005 01:51PM
4352
April 06, 2005 08:16PM
4272
April 07, 2005 07:38AM
4119
April 07, 2005 08:28AM
4267
April 07, 2005 10:59AM
4123
April 07, 2005 12:51PM


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.