Bug #114743 result incorrect
Submitted: 23 Apr 2024 11:30 Modified: 23 Apr 2024 11:58
Reporter: haizhen xue Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[23 Apr 2024 11:30] haizhen xue 
Description:
change the position condition in where, the result change.

How to repeat:
CREATE TABLE `tt_sal_purchase_main` (
  `iD` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nDID` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nBID` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `nTaxRate` decimal(14,2) NOT NULL COMMENT '税率',
  `vRemark` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '备注',
  `nHanderID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '操作员ID',
  `nOprID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '操作员ID',
  `vBillNo` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '单据号',
  `nBizAreaID` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '业务域',
  `vBillState` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '单据状态',
  `vBilltype` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '单据类型',
  `nAdtPersonID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '审核人',
  `nApplicantID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '申请人',
  `nWhid` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '仓库',
  `vTraffic` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '运输方式',
  `cReturn` tinyint DEFAULT NULL COMMENT '退货标识',
  `nUnitID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '供应商',
  `vRejectSeason` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '拒绝原因',
  `cInOut` tinyint DEFAULT NULL COMMENT '入库标识',
  `dBookdate` datetime DEFAULT NULL COMMENT '单据日期',
  `nCorBillID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '对应ID',
  `createBy` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `createOn` datetime NOT NULL,
  `updateBy` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `updateOn` bigint NOT NULL,
  `record_version` int NOT NULL DEFAULT '0' COMMENT '版本',
  `vFinanceType` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '融资类型',
  `vFinanceBank` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '融资银行',
  `dFinanceB` datetime DEFAULT NULL COMMENT '融资起始日期',
  `dFinanceE` datetime DEFAULT NULL COMMENT '融资截止日期',
  PRIMARY KEY (`iD`) USING BTREE,
  UNIQUE KEY `idx_ndid_vbillno` (`nDID`,`vBillNo`) USING BTREE,
  KEY `idx_ncorbillid_vbilltype` (`nCorBillID`,`vBilltype`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='采购单主表';

CREATE TABLE `tt_sal_purchase_detail` (
  `iD` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nDID` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nMainID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主表id',
  `vVin` varchar(17) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '底盘号',
  `nProdID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '产品id',
  `dCheck` datetime DEFAULT NULL COMMENT '审核日期',
  `nTaxamt` decimal(14,2) DEFAULT NULL COMMENT '税额',
  `nPriceNoTax` decimal(14,2) DEFAULT NULL COMMENT '不含税单价',
  `dProd` datetime DEFAULT NULL COMMENT '生产日期',
  `nPrice` decimal(14,2) DEFAULT NULL COMMENT '价格',
  `nFreight` decimal(14,2) DEFAULT NULL COMMENT '运费',
  `vPropertyRight` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '产权属性',
  `cReturn` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '退货标志',
  `nPriceRefit` decimal(14,2) DEFAULT NULL COMMENT '改装价',
  `vRefitMsg` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '改装信息',
  `cPurchase` tinyint DEFAULT NULL COMMENT '自购标志',
  `nPriceRefitTax` decimal(14,2) DEFAULT NULL COMMENT '改装价税额',
  `nSVCAdviserID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '服务顾问',
  `vAReason` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '条查原因',
  `vColorname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '颜色',
  `vPDICheck` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'pdi检查说明',
  `cFactory` tinyint DEFAULT NULL COMMENT '厂家标识',
  `vehiclesStoragePlace` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '车辆存放地',
  `createBy` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `createOn` datetime NOT NULL,
  `updateBy` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `updateOn` bigint NOT NULL,
  `record_version` int NOT NULL DEFAULT '0' COMMENT '版本',
  `nOldMainId` varchar(36) DEFAULT NULL COMMENT '调差对应原单主表id',
  `dMonitorDays` int DEFAULT NULL,
  PRIMARY KEY (`iD`) USING BTREE,
  KEY `idx_purchase_detail02` (`nMainID`,`vVin`) USING BTREE,
  KEY `index_2` (`nMainID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='采购单子表';

CREATE TABLE `tt_sal_purchase_invoice_main` (
  `iD` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nDID` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nBID` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nCorID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `vInvoiceType` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nTaxAmt` decimal(14,2) NOT NULL,
  `nAmt` decimal(14,2) NOT NULL,
  `vInvoiceNO` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `dInvoice` datetime NOT NULL,
  `nSupplierID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nAmtNoTax` decimal(14,2) NOT NULL,
  `vBillNO` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `vBillType` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '发票状态(00已认证，90取消认证)',
  `createBy` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `createOn` datetime NOT NULL,
  `updateBy` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `updateOn` bigint NOT NULL,
  `record_version` int NOT NULL DEFAULT '0' COMMENT '版本',
  PRIMARY KEY (`iD`) USING BTREE,
  KEY `index_11` (`nDID`,`vInvoiceNO`) USING BTREE,
  KEY `index_Invoicemain_ndid` (`nDID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='采购发票主表';

CREATE TABLE `tt_sal_purchase_invoice_detail` (
  `iD` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `nCorID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `cCancel` tinyint NOT NULL,
  `nPrice` decimal(14,2) NOT NULL,
  `nTaxAmt` decimal(14,2) NOT NULL,
  `nMainID` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `vVin` varchar(17) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `createBy` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `createOn` datetime NOT NULL,
  `updateBy` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `updateOn` bigint NOT NULL,
  `record_version` int NOT NULL DEFAULT '0' COMMENT '版本',
  PRIMARY KEY (`iD`) USING BTREE,
  KEY `index_12` (`nMainID`) USING BTREE,
  KEY `index_invoicedetail_13` (`cCancel`,`vVin`,`nCorID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='采购发票认证明细';

insert into tt_sal_purchase_main values('1659824c-9758-eb11-8147-d093dba0c44e','F72929D0-80AC-4854-88CE-9D33C9CA8D70','5DA97E63-B262-4B53-9CA6-4B2C3F6385E4','13.00', NULL,'7eeee081-80d7-4a0a-89ea-7fcc4b5609cb',NULL,'RK20210117010',NULL,'采购退库','ITK',NULL,NULL,'ae0a5158-fc2e-4003-ba2e-7d3a05a4dd1b',NULL,0,'fcb02ed1-4647-eb11-8147-d093dba0c44e',NULL,1,'2021-01-17 15:41:13','0a29a5ed-9258-eb11-8147-d093dba0c44e','FTTLGF','2021-01-17 15:41:13','FTTLGF',1610869276141,1,NULL,NULL,NULL,NULL);

insert into tt_sal_purchase_detail values('1759824c-9758-eb11-8147-d093dba0c44e','F72929D0-80AC-4854-88CE-9D33C9CA8D70','1659824c-9758-eb11-8147-d093dba0c44e','LFMKN5BF8M3138640','8bb0f5a3-f444-eb11-8147-d093dba0c44e',NULL,19103.50,146950.00,NULL,166053.50,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'FTTLGF','2021-01-17 15:41:13','FTTLGF',1610869273577,1,NULL,NULL);

set global log_bin_trust_function_creators=on;

DELIMITER //;
CREATE FUNCTION `f_emptyid`() RETURNS varchar(36) CHARSET utf8mb3
BEGIN
DECLARE v_emptyid VARCHAR(36);
set v_emptyid = '00000000-0000-0000-0000-000000000000';
return v_emptyid;
END //;
DELIMITER ;

SELECT * FROM tt_sal_purchase_main a INNER JOIN tt_sal_purchase_detail b ON a.iD = b.nMainID WHERE ( a.nUnitID = '00000000-0000-0000-0000-000000000000' OR '00000000-0000-0000-0000-000000000000' = '00000000-0000-0000-0000-000000000000' ) AND b.vVin= 'LFMKN5BF8M3138640' AND NOT EXISTS (SELECT * FROM (SELECT a.id, a.dinvoice, b.ncorid  FROM  tt_sal_purchase_invoice_main a, tt_sal_purchase_invoice_detail b  WHERE a.id = b.nmainid AND a.dinvoice BETWEEN '1900/01/01' AND '2024-04-01 23:59:59'  AND ifnull(a.NCorID, f_emptyid()) = '00000000-0000-0000-0000-000000000000' AND a.ndid = 'F72929D0-80AC-4854-88CE-9D33C9CA8D70' AND ifnull(b.CCancel, '0') = '0' ) a6  WHERE a6.ncorid = b.iD ) ;
SELECT * FROM tt_sal_purchase_main a INNER JOIN tt_sal_purchase_detail b ON a.iD = b.nMainID WHERE b.vVin= 'LFMKN5BF8M3138640' AND NOT EXISTS (SELECT * FROM (SELECT a.id, a.dinvoice, b.ncorid  FROM  tt_sal_purchase_invoice_main a, tt_sal_purchase_invoice_detail b  WHERE a.id = b.nmainid AND a.dinvoice BETWEEN '1900/01/01' AND '2024-04-01 23:59:59'  AND ifnull(a.NCorID, f_emptyid()) = '00000000-0000-0000-0000-000000000000' AND a.ndid = 'F72929D0-80AC-4854-88CE-9D33C9CA8D70' AND ifnull(b.CCancel, '0') = '0' ) a6  WHERE a6.ncorid = b.iD ) AND ( a.nUnitID = '00000000-0000-0000-0000-000000000000' OR '00000000-0000-0000-0000-000000000000' = '00000000-0000-0000-0000-000000000000' );
[23 Apr 2024 11:58] MySQL Verification Team 
Hi Mr. xue,

Thank you for your bug report.

However, your report is totally unclear.

First, you are reporting a problem with partitioning, but your CREATE TABLE statements do not have a single partition.

Second, you are talking about result being incorrect due to the change in the SELECT conditions, but you have only provided us with a single SELECT.

We can not repeat a problem that is not well defined.