MySQL Bugs: #39486: different select results in partitioned and unpartioned tables

Bug #39486	different select results in partitioned and unpartioned tables
Submitted:	16 Sep 2008 17:11	Modified:	17 Sep 2008 7:41
Reporter:	Wolfgang Purrer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.26	OS:	Windows (2003 sp2)
Assigned to:		CPU Architecture:	Any

Description:
I have a table (with about 280 mb data, and apporx 7 Million entries)
 which is in case A not partioned in case B partioned per month+year

if i do a simple select:
(Statement 1)
SELECT     *
FROM         daten
WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42') AND

(
  (kurz = 'AAA12XXXXXXX' OR
    kurz = 'AA160XXXXXX'
))
order by zeitpunkt

in case A it takes: 0,0112 seconds and I got 519 rows back
in case B it takes 18 secondes and i got 866 rows back.

the table A and B does have the same content (both have  7508431 rows, checked with the row count in the MySQL - Administrator)
and the table B is create with the following statement

insert into daten select * from daten_test

* if i look at the data in case B.. this result is even not possible because of the keys!
* i droped,created the B new and reimported the data .. but the result didn't change..) => i did in in both directions... but no change.

If I reduce the statement to
(Statement 2)

SELECT     *
FROM         daten
WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42') AND

(
  (kurz = 'AAA12XXXXXXX'
    
))
order by zeitpunkt

they both got the same results... maybe it has something to do with the or.

if you do a order by kurz, zeitpunkt also the number of rows in result changes

if i change the query to 
(Statement 3)
SELECT     *
FROM         daten
WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42') AND

(
  (kurz in ( 'AAA12XXXXXXX','AAA160XXXXXX')
))
order by zeitpunkt

the problem also appears.

The table B looks like this
(Statement 4)
CREATE TABLE  daten(
  `kurz` char(12) CHARACTER SET latin1 NOT NULL DEFAULT '0',
  `zeitpunkt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `wert` decimal(30,5) NOT NULL DEFAULT '0.00000',
  PRIMARY KEY (`kurz`,`zeitpunkt`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PARTITION BY RANGE (to_days(zeitpunkt)) ( PARTITION p2008_06 VALUES LESS THAN (733589) ENGINE = MyISAM, PARTITION p2008_07 VALUES LESS THAN (733620) ENGINE = MyISAM, PARTITION p2008_08 VALUES LESS THAN (733651) ENGINE = MyISAM, PARTITION p2008_09 VALUES LESS THAN (733681) ENGINE = MyISAM, PARTITION rest VALUES LESS THAN MAXVALUE ENGINE = MyISAM) ;

The table A is the same only without the partition part.

How to repeat:

CREATE TABLE  daten(
  `kurz` char(12) CHARACTER SET latin1 NOT NULL DEFAULT '0',
  `zeitpunkt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `wert` decimal(30,5) NOT NULL DEFAULT '0.00000',
  PRIMARY KEY (`kurz`,`zeitpunkt`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PARTITION BY RANGE (to_days(zeitpunkt)) ( PARTITION p2008_06 VALUES LESS THAN (733589) ENGINE = MyISAM, PARTITION p2008_07 VALUES LESS THAN (733620) ENGINE = MyISAM, PARTITION p2008_08 VALUES LESS THAN (733651) ENGINE = MyISAM, PARTITION p2008_09 VALUES LESS THAN (733681) ENGINE = MyISAM, PARTITION rest VALUES LESS THAN MAXVALUE ENGINE = MyISAM) ;

load data infile 'c:\\exp.sql' into table daten;

AAA12XXXXXXX	2008-08-16 17:30:00	75.60000
AAA150XXXXXX	2008-08-16 17:30:00	75.60000
AAA160XXXXXX	2008-08-16 17:30:00	85.90000
AAA170XXXXXX	2008-08-16 17:30:00	86.70000
AAA250XXXXXX	2008-08-16 18:45:00	72.50000
AAA260XXXXXX	2008-08-16 18:45:00	84.80000
AAA160XXXXXX	2008-08-16 19:30:00	85.40000
AAA170XXXXXX	2008-08-16 19:30:00	87.40000
AAA270XXXXXX	2008-08-16 19:45:00	91.20000
AAA12XXXXXXX	2008-08-16 21:30:00	75.30000

SELECT     *
FROM         daten
WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42') AND

(
  (kurz in ( 'AAA12XXXXXXX','AAA160XXXXXX')
))

and 

SELECT     *
FROM         daten
WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42') AND

(
  (kurz in ( 'AAA12XXXXXXX','AAA160XXXXXX')
)) order by kurz

i changed the synopsis to transport the message clearer:

The two main bugs or:

* with an "or" in the statement i got more rows back as expected (some duplicates)
* with an or in the statement the query slows down at a factor 8000
* with an order by... the original amount is shown, but still slow!

Thank you for the bug report. What is the result of your test case in how to repeat section?. Thanks in advance.

Your MySQL connection id is 2
Server version: 5.1.30-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use gh
Database changed
mysql 5.1 >SELECT     *
    -> FROM         daten
    -> WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42')
    -> AND
    ->
    -> (
    ->   (kurz in ( 'AAA12XXXXXXX','AAA160XXXXXX')
    -> ));
+--------------+---------------------+----------+
| kurz         | zeitpunkt           | wert     |
+--------------+---------------------+----------+
| AAA12XXXXXXX | 2008-08-16 17:30:00 | 75.60000 |
| AAA12XXXXXXX | 2008-08-16 21:30:00 | 75.30000 |
| AAA160XXXXXX | 2008-08-16 17:30:00 | 85.90000 |
| AAA160XXXXXX | 2008-08-16 19:30:00 | 85.40000 |
+--------------+---------------------+----------+
4 rows in set (0.00 sec)

mysql 5.1 >
mysql 5.1 >SELECT     *
    -> FROM         daten
    -> WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42')
    -> AND
    ->
    -> (
    ->   (kurz in ( 'AAA12XXXXXXX','AAA160XXXXXX')
    -> )) order by kurz;
+--------------+---------------------+----------+
| kurz         | zeitpunkt           | wert     |
+--------------+---------------------+----------+
| AAA12XXXXXXX | 2008-08-16 17:30:00 | 75.60000 |
| AAA12XXXXXXX | 2008-08-16 21:30:00 | 75.30000 |
| AAA160XXXXXX | 2008-08-16 17:30:00 | 85.90000 |
| AAA160XXXXXX | 2008-08-16 19:30:00 | 85.40000 |
+--------------+---------------------+----------+
4 rows in set (0.01 sec)

mysql 5.1 >

SELECT     *
FROM         daten
WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42') AND

(
  (kurz in ( 'AAA12XXXXXXX','AAA160XXXXXX')
))

'AAA12XXXXXXX', '2008-08-16 17:30:00', 75.60000
'AAA12XXXXXXX', '2008-08-16 21:30:00', 75.30000
'AAA160XXXXXX', '2008-08-16 17:30:00', 85.90000
'AAA160XXXXXX', '2008-08-16 19:30:00', 85.40000
'AAA160XXXXXX', '2008-08-16 17:30:00', 85.90000
'AAA160XXXXXX', '2008-08-16 19:30:00', 85.40000

SELECT     *
FROM         daten
WHERE     (zeitpunkt >= '2008-08-16  16:49:42') AND (zeitpunkt <= '2008-09-16  16:49:42') AND

(
  (kurz in ( 'AAA12XXXXXXX','AAA160XXXXXX')
))
order by kurz;

'AAA12XXXXXXX', '2008-08-16 17:30:00', 75.60000
'AAA12XXXXXXX', '2008-08-16 21:30:00', 75.30000
'AAA160XXXXXX', '2008-08-16 17:30:00', 85.90000
'AAA160XXXXXX', '2008-08-16 19:30:00', 85.40000

Thank you for a problem report. Please, try to repeat with a newer version, 5.1.28. In case of the same problem, please, send SHOW CREATE TABLE results for the table(s) involved, as well as EXPLAIN and EXPLAIN PARTITIONS results for the problematic SELECT(s).

both problems are solved in 5.1.28 ...