Hi,

Would you be able to provide data on which the problem is repeatable?
I tried to populate the table with an artificial dataset, but haven't succeeded at reproducing the issue so far.
You can upload it to our ftp in the private section: ftp://ftp.askmonty.org/private/

Also, your attachment says:

>>>> return 1 row: 'ZB'
...
>>>> return 1 row: 'Z' & 'ZB'

Do I guess correctly that it's a copy-paste error, that the 2nd line is supposed to say "2 rows", and it's the correct result?

Hello Elena,

Yes, you are right, there are two records returned. Depending on the select comes one or two records. The correct result is two records.
Unfortunately, the table is very large 220MB. When I try to shrink it, then the error didn't occurs. Is it possible to upload such a large table? Or what other suggestion do you have?

Mit freundlichen Grüßen/Best regards/Sincères salutations

Artur Pritzkau

The shrinked data-file is attached.

Hi,

Thanks for the data. I was able to reproduce the problem and further reduce the test case:

--source include/have_partition.inc

CREATE TABLE `t1` (
`Id` int(11) NOT NULL,
`BArt` char(3) NOT NULL,
`HubLst` mediumint(8) unsigned NOT NULL,
`TrbwGr` char(3) NOT NULL,
`GltVonDat` date NOT NULL,
`GltBisDat` date NOT NULL,
`Freq` tinyint(3) unsigned NOT NULL,
`Ident` char(150) DEFAULT NULL,
KEY `Ind_HubLst` (`HubLst`),
KEY `Ind_TrbwGr` (`TrbwGr`)
) ENGINE=MyISAM
PARTITION BY RANGE COLUMNS(BArt,GltBisDat)
(
PARTITION p22 VALUES LESS THAN ('E','2011-06-30') ENGINE = MyISAM,
PARTITION p46 VALUES LESS THAN ('ZB','2011-06-30') ENGINE = MyISAM,
PARTITION p47 VALUES LESS THAN ('ZB',MAXVALUE) ENGINE = MyISAM
);
INSERT INTO `t1` VALUES (16011353,'E',2000,'4m','2003-06-01','2003-07-09',50,480);
INSERT INTO `t1` VALUES (20005451,'Z',20000,'2m','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20005445,'Z',20000,'2m','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20005444,'Z',20000,'2m','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20005443,'Z',20000,'2m','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20005442,'Z',20000,'2m','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20005441,'Z',20000,'2m','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20005440,'Z',20000,'2m','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20005439,'Z',20000,'2m','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (20003668,'Z',16000,'4m','2005-01-01','2006-12-31',50,230);
INSERT INTO `t1` VALUES (20003667,'Z',16000,'4m','2005-01-01','2006-12-31',50,230);
INSERT INTO `t1` VALUES (20003501,'Z',16000,'4m','2005-01-01','2007-05-31',50,480);
INSERT INTO `t1` VALUES (20002352,'Z',25000,'2m','2007-01-01','2007-05-31',50,400);
INSERT INTO `t1` VALUES (20002351,'Z',25000,'2m','2005-01-01','2006-12-31',50,400);
INSERT INTO `t1` VALUES (20002350,'Z',25000,'2m','2005-01-01','2006-12-31',50,400);
INSERT INTO `t1` VALUES (20002349,'Z',25000,'3m','2007-01-01','2007-05-31',50,230);
INSERT INTO `t1` VALUES (20002348,'Z',25000,'3m','2007-01-01','2007-05-31',50,230);
INSERT INTO `t1` VALUES (20002347,'Z',25000,'3m','2005-01-01','2006-12-31',50,230);
INSERT INTO `t1` VALUES (20002346,'Z',25000,'3m','2005-01-01','2006-12-31',50,230);
INSERT INTO `t1` VALUES (20002345,'Z',25000,'3m','2007-01-01','2007-05-31',50,230);
INSERT INTO `t1` VALUES (20002344,'Z',25000,'3m','2007-01-01','2007-05-31',50,230);
INSERT INTO `t1` VALUES (20002331,'Z',25000,'2m','2005-01-01','2006-12-31',50,230);
INSERT INTO `t1` VALUES (20002330,'Z',25000,'2m','2005-01-01','2006-12-31',50,230);
INSERT INTO `t1` VALUES (20002296,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20002294,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20002292,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20002290,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20002289,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20002288,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20002287,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20002286,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20002285,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (20002284,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (20002283,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (20002282,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (20002280,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20002278,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20002276,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20002274,'Z',40000,'1Am','2007-07-01','2008-06-18',50,400);
INSERT INTO `t1` VALUES (20002273,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20002272,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20002271,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20002270,'Z',40000,'1Am','2007-06-15','2007-06-30',50,400);
INSERT INTO `t1` VALUES (20002269,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (20002268,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (20002267,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (20002266,'Z',40000,'1Am','2007-06-01','2007-06-14',50,400);
INSERT INTO `t1` VALUES (22000018,'ZB',25000,'4m','2008-06-06','2050-12-31',60,460);

SELECT DISTINCT maintab.BArt FROM t1 AS maintab
WHERE maintab.HubLst = 16000
AND maintab.TrbwGr = '4m'
AND maintab.BArt IN ('D', 'DA', 'DA4', 'DB', 'DQA', 'Z', 'ZA', 'ZB');

SELECT DISTINCT maintab.BArt FROM t1 AS maintab
WHERE maintab.HubLst = 16000
AND maintab.TrbwGr = '4m'
AND maintab.BArt IN ('D', 'DA', 'DA4', 'DB', 'DQA', 'Z', 'ZA', 'ZB')
AND maintab.BArt IN ('ZB','Z');

DROP TABLE t1;

1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. End of test case

The problem was introduced in MariaDB 5.5.29 by a merge from MySQL, specifically by this revision:

revno: 3624 [merge]
revision-id: sergii@pisem.net-20130115181332-1h95xur953vgkb22
parent: sergii@pisem.net-20130115180849-nlrobwol6wcca9q3
parent: dmitry.lenev@oracle.com-20121210060637-fxax09ib1bmqnyu3
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: 5.5
timestamp: Tue 2013-01-15 19:13:32 +0100
message:
mysql-5.5.29 merge
------------------------------------------------------------

About the same time it appeared in MySQL 5.6, too. It is not reproducible on MySQL 5.5, apparently due to the absence of index_merge, but still reproducible on MySQL 5.6.

In such cases we normally report the bug at MySQL bug base to inform upstream about the issue. Are you willing to do it, or should I do it on your behalf?

Meanwhile, as a workaround, you can turn off index_merge in your optimizer switch:
optimizer_switch = 'index_merge=off,index_merge_intersection=off'

ANALYZE TABLE also seems to help in some cases, but I think it's unreliable.

Hello Elena,

please report the bug at MySQL bug base on my behalf?

Mit freundlichen Grüßen/Best regards/Sincères salutations

Artur Pritzkau

Filed as http://bugs.mysql.com/bug.php?id=71184

The upstream bugs are said to have been fixed in 5.5.39, although the problem disappeared from 5.5 tree with revno 4070 (merge of MySQL 5.5.36).
Anyway, closing as fixed.