Bug #88896 Different query result, depending from data load pattern
Submitted: 13 Dec 2017 12:12 Modified: 14 Dec 2017 5:32
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6, 5.7.20, 8.0.3, 5.6.38 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[13 Dec 2017 12:12] Sveta Smirnova
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.
[13 Dec 2017 12:12] Sveta Smirnova
test case for MTR

Attachment: bug1721753.test (application/octet-stream, text), 43.23 KiB.

[13 Dec 2017 12:13] Sveta Smirnova
Version 5.5 returns correct results in all cases
[14 Dec 2017 5:32] Umesh Shastry
Hello Sveta,

Thank you for the report and test case.
Verified as described with 5.6.38 and 5.7.20 build.

Thanks,
Umesh