MySQL Bugs: #114972: I got a bug with function about ifnull()

Bug #114972	I got a bug with function about ifnull()
Submitted:	13 May 2024 6:48	Modified:	15 May 2024 7:39
Reporter:	chao zhu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.37	OS:	CentOS ( 7.9.2009 )
Assigned to:		CPU Architecture:	x86
Tags:	8.0.37 ifnull

Description:
I made a upgrade  on  mysql-server from 8.0.22 to   8.0.37,and  I got a bug about function ifnull .
In  version 8.0.22 ,it goes well,but in version 8.0.37,it ignores default value,output null.
Here is the  SQL text

select /*+ MAX_EXECUTION_TIME(3600000) */
    inv.id                       as id,
    inv.upc                      as upc,
    sum(inv.qty)                 as qty,
    (select ifnull(sum(qty), 0)
     from t_inventory_snapshot_9 t
     where t.batch_no = inv.batch_no
       and t.upc = inv.upc
       and t.inv_status != '10') as defectiveQty
from t_inventory_snapshot_9 inv
where inv.batch_no = '1789686978875998208' and upc = '656047952'
group by inv.upc;

In  version 8.0.22  ,the resoult is 
2545002	656047952	12	0
But in version 8.0.37  ,the resoult is 
2545002	656047952	12	null

I also test version 8.0.36,it goes well.
So  I want to report this bug about it.

How to repeat:
FYI
select /*+ MAX_EXECUTION_TIME(3600000) */
    inv.id                       as id,
    inv.upc                      as upc,
    sum(inv.qty)                 as qty,
    (select ifnull(sum(qty), 0)
     from t_inventory_snapshot_9 t
     where t.batch_no = inv.batch_no
       and t.upc = inv.upc
       and t.inv_status != '10') as defectiveQty
from t_inventory_snapshot_9 inv
where inv.batch_no = '1789686978875998208' and upc = '656047952'
group by inv.upc;

Hi Mr. zhu,

Thank you for your bug report.

However, let us inform you that this is a forum for the reports with fully repeatable test cases. A test case should consist of the set of SQL statements that would show unequivocally where the error pops up.

We cannot run your SELECT statements, since we do not have your tables.

Also, we do not see any proof that ifnull() does not work. We tried with our tables and ifnull() works just fine  in all cases.

Can't repeat.

CREATE TABLE `t_inventory_snapshot_9` (
	`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
	`version` int DEFAULT NULL COMMENT '版本',
	`im_organization` varchar(50) NOT NULL COMMENT '库管',
	`sku_code` varchar(150) DEFAULT NULL COMMENT 'sku编码',
	`supplier_sku_code` varchar(255) DEFAULT NULL COMMENT '货号',
	`sku_size` varchar(50) DEFAULT NULL COMMENT '尺码',
	`upc` varchar(150) DEFAULT NULL COMMENT 'upc',
	`platform_code` varchar(150) DEFAULT NULL COMMENT '平台对接码',
	`bar_code` varchar(150) DEFAULT NULL COMMENT '条码',
	`owner_code` varchar(150) DEFAULT NULL COMMENT '库存主体',
	`available_qty` int DEFAULT '0' COMMENT '可用',
	`occupy_qty` int DEFAULT '0' COMMENT '占用',
	`qty` int DEFAULT '0' COMMENT '存货',
	`batch_no` varchar(50) DEFAULT NULL COMMENT '批次号',
	`task_code` varchar(50) DEFAULT NULL COMMENT '任务标号',
	`inv_status` varchar(10) DEFAULT NULL COMMENT '库存状态',
	`bin_code` varchar(50) DEFAULT NULL COMMENT '仓库编码',
	`gift` tinyint(1) DEFAULT NULL COMMENT '赠品',
	`status` smallint DEFAULT NULL COMMENT '状态',
	`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
	`saas_tenant_code` varchar(100) DEFAULT 'baozun' COMMENT '租户编码',
	`product_batch_no` varchar(50) DEFAULT 'DEFAULT_NULL' COMMENT '批次效期',
	`product_date` date DEFAULT NULL COMMENT '生产日期',
	`expire_date` date DEFAULT NULL COMMENT '过期日期',
	`subpackage_date` date DEFAULT NULL COMMENT '分包日期',
	`brand_code` varchar(150) DEFAULT NULL COMMENT '品牌编码',
	PRIMARY KEY (`id`),
	KEY `idx_inv_common_snapshot_batch_no` (`batch_no`),
	KEY `idx_inv_common_snapshot_status` USING BTREE (`status`),
	KEY `idx_create_time` (`create_time`),
	KEY `idx_last_modify_time` (`last_modify_time`)
) ENGINE = InnoDB AUTO_INCREMENT = 2575959 CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT '通用库存快照'

INSERT INTO t_inventory_snapshot_9  (`id`,`version`,`im_organization`,`sku_code`,`supplier_sku_code`,`sku_size`,`upc`,`platform_code`,`bar_code`,`owner_code`,`available_qty`,`occupy_qty`,`qty`,`batch_no`,`task_code`,`inv_status`,`bin_code`,`gift`,`status`,`create_time`,`last_modify_time`,`saas_tenant_code`,`product_batch_no`,`product_date`,`expire_date`,`subpackage_date`,`brand_code`) VALUES(2545002,2,'ANFKG','AFUPC656047952','656047952',NULL,'656047952','654422018','656047952','ANF天猫旗舰店',12,0,12,'1789686978875998208','im2_snapshot_task_ANFKG_0','10','BS13',0,2,'2024-05-13 00:00:01','2024-05-13 13:17:44','baozun','DEFAULT_NULL','2999-01-01','2999-01-01','2999-01-01','PP4104');

I am Chao Zhu's colleague. We discovered this bug together. Please help us reproduce it once more.

Hi Mr. zhu,

Thank you for your test case.

Your test case definitely shows that this is not a bug.

Simply, you have several items in the SELECT list and only one is present in the GROUP BY. Hence, those other values can be anything, according to the SQL Standard.

Please, use ONLY_FULL_GROUP_BY for your future queries.

This is all explained in our Reference Manual.

Not a bug.

hello，we found that sql_mode of two server version of 8.0.22 and 8.0.37  are same
.The data and table structure are same，too.
 
how does mysql get two different result  in two different  version？
Any update about function ifnull occurs after version 8.0.22?

HI Mr. zhu,

There were many bug fixes since 8.0.22 and current release of the version 8.0.

But, your major problem is not in that function.

It is the one that we described above. MySQL follows SQL standard which clearly states that in the aggregated queries, fields in the SELECT list that are not included in the GROUP BY list, can be values from any tuple in the relation.

This is also described in our Reference Manual.

That is why this is not a bug.