| Bug #111808 | histroy result instead of null when the select result is empty | ||
|---|---|---|---|
| Submitted: | 19 Jul 2023 7:53 | Modified: | 19 Jul 2023 12:49 |
| Reporter: | 文强 杨 | Email Updates: | |
| Status: | Unsupported | Impact on me: | |
| Category: | MySQL Server: User-defined functions ( UDF ) | Severity: | S3 (Non-critical) |
| Version: | 8.0.22 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Jul 2023 12:49]
MySQL Verification Team
Hi Mr. 文强 杨, Thank you for your bug report. However, you are using too old release of 8.0. Please, try to repeat the result with 8.0.34. If you manage to repeat with 8.0.34, please explain the following statement: " the company orderflowFlag for 广西吉仁堂药业有限公司, 广西九州通医药有限公司 are 1, others are 0, so when the function's select result is empty(orderflowFlag=0) MySQL use the previous cached value instead of real result 0 " We nowhere see the result of 0 nor of 1. Hence, make your report clear. If you repeat the behaviour with 8.0.34, then please send us a fully repeatable test case. Unsupported.
Description: when I continuously call a function as follow: CREATE DEFINER=`root`@`%` FUNCTION `getCompanySoldTotalAmount`( _companyCode VARCHAR(80) ) RETURNS decimal(16,2) BEGIN DECLARE _direct DECIMAL(16,2) DEFAULT 0.0; SELECT SUM(IFNULL(i.totalAmount,0)) INTO _direct FROM crm.t_dispatch_items i, crm.t_dispatch_bills b, crm.t_business_company c WHERE c.companyCode = b.customerCode AND c.orderflowFlag = 0 AND b.dispatchId = i.dispatchId AND c.companyCode = _companyCode; RETURN _direct; END The printed result is: ... 559/3250: 广西华兴药械有限公司 = 0.00 560/3250: 广西华源冠峰药业有限公司 = 0.00 561/3250: 广西吉仁堂药业有限公司 = 17,388.00 562/3250: 广西极致药业有限责任公司 = 17,388.00 563/3250: 广西嘉进医药批发有限公司 = 17,388.00 564/3250: 广西健一药业有限责任公司 = 17,388.00 565/3250: 广西健之佳勤康医药销售有限公司 = 17,388.00 566/3250: 广西健之佳药店连锁有限公司 = 17,388.00 567/3250: 广西金贝医药有限责任公司 = 17,388.00 568/3250: 广西金恩药业有限公司 = 17,388.00 569/3250: 广西金夫康医药有限公司 = 17,388.00 570/3250: 广西金银花药业有限公司 = 17,388.00 571/3250: 广西九州通医药有限公司 = 22,844,927.40 572/3250: 广西康乐医药有限公司 = 22,844,927.40 573/3250: 广西康全药业连锁有限公司 = 22,844,927.40 574/3250: 广西康全医药有限责任公司 = 22,844,927.40 575/3250: 广西灵山县医药有限责任公司 = 22,844,927.40 the company orderflowFlag for 广西吉仁堂药业有限公司, 广西九州通医药有限公司 are 1, others are 0, so when the function's select result is empty(orderflowFlag=0) MySQL use the previous cached value instead of real result 0 How to repeat: descript in Description Suggested fix: disable the cache value although we can count the result to avoid this bug(the function code will not clear, and the function performance will be reduce)