Bug #114744 result incorrect when change the position condition in where
Submitted: 23 Apr 2024 11:31 Modified: 23 Apr 2024 12:00
Reporter: haizhen xue Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[23 Apr 2024 11:31] 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 12:00] MySQL Verification Team 
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

You have already filed your bug report here:

https://bugs.mysql.com/bug.php?id=114743

Thank you for your interest in MySQL.