Description:
Originally reported at https://bugs.launchpad.net/percona-server/+bug/1721753
Query result changing depending from how data loaded and if one of indexes used.
How to repeat:
Use test case attached or:
CREATE TABLE `mad_partner` (
`OID` varchar(40) COLLATE utf8mb4_bin NOT NULL,
`changeTime` datetime NOT NULL,
`changer` varchar(128) COLLATE utf8mb4_bin NOT NULL,
`creationTime` datetime NOT NULL,
`creator` varchar(128) COLLATE utf8mb4_bin NOT NULL,
`code` varchar(128) COLLATE utf8mb4_bin NOT NULL,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`deactivated` bit(1) NOT NULL,
`labels` mediumtext COLLATE utf8mb4_bin,
`urn` varchar(128) COLLATE utf8mb4_bin NOT NULL,
`organizationOID` varchar(40) COLLATE utf8mb4_bin NOT NULL,
`phoneNumber` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
`faxNumber` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
`eMailAddress` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
`webAddress` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`taxIDNumber` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`taxIDType` varchar(32) COLLATE utf8mb4_bin NOT NULL,
`profileLabels` varchar(2000) COLLATE utf8mb4_bin DEFAULT NULL,
`linked_crm` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
`chamberOfCommerceNumber` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`dunsNumber` varchar(9) COLLATE utf8mb4_bin DEFAULT NULL,
`lwi_addresstool_id` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL,
`cinType` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
`CIN` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`locked` tinyint(1) NOT NULL DEFAULT '0',
`eoriNumber` varchar(17) COLLATE utf8mb4_bin DEFAULT NULL,
`dunsAddress` varchar(512) COLLATE utf8mb4_bin DEFAULT NULL,
`lastLookupUser` varchar(512) COLLATE utf8mb4_bin DEFAULT NULL,
`lastLookupDate` date DEFAULT NULL,
`lastQuickCheckUser` varchar(512) COLLATE utf8mb4_bin DEFAULT NULL,
`lastQuickCheckDate` date DEFAULT NULL,
`businessType` varchar(32) COLLATE utf8mb4_bin NOT NULL,
`vatGroup_OID` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`OID`),
UNIQUE KEY `organizationOID` (`organizationOID`,`code`),
UNIQUE KEY `idx_urn` (`urn`,`organizationOID`),
KEY `FK_PARTNER_VAT_GROUP` (`vatGroup_OID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
ALTER TABLE `mad_partner` DISABLE KEYS;
-- Load data
ALTER TABLE `mad_partner` ENABLE KEYS;
-- Queries return no result if run right after dump load
SELECT DISTINCT partner0_.organizationOID as col FROM mad_partner partner0_ WHERE partner0_.OID IN ( 'be067ca5-6cd3-42dc-8a68-a08e9800b195', 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab', '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b', '7a090a98-08ec-4924-ac1a-d4b45c749cac', '83031acb-43cd-4c97-963e-8692a007aba5');
col
SELECT DISTINCT partner0_.organizationOID as col1 FROM mad_partner partner0_ WHERE partner0_.OID IN (
'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
'7a090a98-08ec-4924-ac1a-d4b45c749cac',
'be067ca5-6cd3-42dc-8a68-a08e9800b195');
col1
SELECT DISTINCT partner0_.organizationOID as col2 FROM mad_partner partner0_ WHERE partner0_.OID IN (
'8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b',
'83031acb-43cd-4c97-963e-8692a007aba5');
col2
-- However IGNORE INDEX clause fixes the issue:
SELECT DISTINCT partner0_.organizationOID as col FROM mad_partner partner0_ ignore index(organizationOID) WHERE partner0_.OID IN ( 'be067ca5-6cd3-42dc-8a68-a08e9800b195', 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab', '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b', '7a090a98-08ec-4924-ac1a-d4b45c749cac', '83031acb-43cd-4c97-963e-8692a007aba5');
col
e0f52141-941b-437c-87a5-dff3f4f74999
-- After OPTIMIZE TABLE last query (2 queries depending on version) show correct result:
optimize table mad_partner;
Table Op Msg_type Msg_text
test.mad_partner optimize note Table does not support optimize, doing recreate + analyze instead
test.mad_partner optimize status OK
Warnings:
Warning 1478 InnoDB: ROW_FORMAT=DYNAMIC requires innodb_file_format > Antelope.
Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT.
SELECT DISTINCT partner0_.organizationOID as col FROM mad_partner partner0_ WHERE partner0_.OID IN ( 'be067ca5-6cd3-42dc-8a68-a08e9800b195', 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab', '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b', '7a090a98-08ec-4924-ac1a-d4b45c749cac', '83031acb-43cd-4c97-963e-8692a007aba5');
col
SELECT DISTINCT partner0_.organizationOID as col1 FROM mad_partner partner0_ WHERE partner0_.OID IN (
'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
'7a090a98-08ec-4924-ac1a-d4b45c749cac',
'be067ca5-6cd3-42dc-8a68-a08e9800b195');
col1
e0f52141-941b-437c-87a5-dff3f4f74999
SELECT DISTINCT partner0_.organizationOID as col2 FROM mad_partner partner0_ WHERE partner0_.OID IN (
'8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b',
'83031acb-43cd-4c97-963e-8692a007aba5');
col2
e0f52141-941b-437c-87a5-dff3f4f74999
-- If data loaded without disable/enable keys all queries return correct result
create table t1 like mad_partner;
insert into t1 select * from mad_partner;
drop table mad_partner;
create table mad_partner like t1;
insert into mad_partner select * from t1;
SELECT DISTINCT partner0_.organizationOID as col FROM mad_partner partner0_ WHERE partner0_.OID IN ( 'be067ca5-6cd3-42dc-8a68-a08e9800b195', 'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab', '8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b', '7a090a98-08ec-4924-ac1a-d4b45c749cac', '83031acb-43cd-4c97-963e-8692a007aba5');
col
e0f52141-941b-437c-87a5-dff3f4f74999
SELECT DISTINCT partner0_.organizationOID as col1 FROM mad_partner partner0_ WHERE partner0_.OID IN (
'dff1ffa7-58c2-46f3-9880-9a02e8e9c8ab',
'7a090a98-08ec-4924-ac1a-d4b45c749cac',
'be067ca5-6cd3-42dc-8a68-a08e9800b195');
col1
e0f52141-941b-437c-87a5-dff3f4f74999
SELECT DISTINCT partner0_.organizationOID as col2 FROM mad_partner partner0_ WHERE partner0_.OID IN (
'8c1fb1ae-25e2-49f8-bb6a-65b5716d0d2b',
'83031acb-43cd-4c97-963e-8692a007aba5');
col2
e0f52141-941b-437c-87a5-dff3f4f74999
Suggested fix:
Return correct values no matter which index used.
