MySQL查询的诡异问题

Question

今天发现公司考勤数据统计查询结果出现诡异的情况，在查询条件加个筛选后，竟然会改变查询列中的数据

数据库版本：8.0.23
数据表及数据：

CREATE TABLE tblattendance (
  ID int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  EMP_CODE varchar(32) DEFAULT NULL COMMENT '员工号',
  ATTENDANCE decimal(18,3) DEFAULT NULL COMMENT '出勤',
  HRS_PER_DAY decimal(18,2) DEFAULT NULL COMMENT '每天工作小时数',
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='考勤表';

CREATE TABLE tblworklog (
  ID int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  EMP_CODE varchar(32) DEFAULT NULL COMMENT '员工号',
  HRS decimal(18,3) DEFAULT NULL COMMENT '小时数',
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='工作日志表';

insert into tblAttendance(EMP_CODE,ATTENDANCE,HRS_PER_DAY)
values('10049', 21, 8), ('10032', 19, 8);

insert into tblWorklog(EMP_CODE,HRS)
values('10049', 11), ('10049', 9), ('10032', 9), ('10032', 9);

以下查询语句结果正确：

select EMP_CODE,ATTENDANCE,NEED_HRS,HRS
from (
    select a.EMP_CODE, a.ATTENDANCE ,a.ATTENDANCE*a.HRS_PER_DAY as NEED_HRS, HRS
    from (select t.EMP_CODE, t.ATTENDANCE, t.HRS_PER_DAY
        ,(select sum(t2.HRS) from tblWorklog t2 where t2.EMP_CODE=t.EMP_CODE) as HRS
    from tblAttendance t) a 
) mm

查询结果：

但在上面查询语句的基础上增加筛选条件 where NEED_HRS>0，

select EMP_CODE,ATTENDANCE,NEED_HRS,HRS
from (
    select a.EMP_CODE, a.ATTENDANCE ,a.ATTENDANCE*a.HRS_PER_DAY as NEED_HRS, HRS
    from (select t.EMP_CODE, t.ATTENDANCE, t.HRS_PER_DAY
        ,(select sum(t2.HRS) from tblWorklog t2 where t2.EMP_CODE=t.EMP_CODE) as HRS
    from tblAttendance t) a 
) mm where NEED_HRS>0

查询结果竟然变为：

而奇怪的是，我将最里层的汇总子查询注释掉后（此字段并没有参与计算与过滤），有没有筛选条件的查询结果都是正常的：

select EMP_CODE,ATTENDANCE,NEED_HRS -- ,HRS
from (
    select a.EMP_CODE, a.ATTENDANCE ,a.ATTENDANCE*a.HRS_PER_DAY as NEED_HRS -- , HRS
    from (select t.EMP_CODE, t.ATTENDANCE, t.HRS_PER_DAY
        -- ,(select sum(t2.HRS) from tblWorklog t2 where t2.EMP_CODE=t.EMP_CODE) as HRS
    from tblAttendance t) a 
) mm where NEED_HRS>0

或者将“a.ATTENDANCE*a.HRS_PER_DAY as NEED_HRS”的计算字段移到最里层，查询结果也都是正常的：

select EMP_CODE,ATTENDANCE,NEED_HRS ,HRS
from (
    select a.EMP_CODE, a.ATTENDANCE, NEED_HRS, HRS
    from (select t.EMP_CODE, t.ATTENDANCE, t.HRS_PER_DAY, t.ATTENDANCE*t.HRS_PER_DAY as NEED_HRS
         ,(select sum(t2.HRS) from tblWorklog t2 where t2.EMP_CODE=t.EMP_CODE) as HRS
    from tblAttendance t) a 
) mm where NEED_HRS>0

希望有人可以解答第二个加了where NEED_HRS>0筛选条件后查询结果不正常的原因，以及为什么将ATTENDANCE*HRS_PER_DAY移到最里层查询结果也会正常。

Answer 1

我按照你的代码，并没有复现你的问题：

感觉你的库有问题