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`);