MySql ifnull and count bug?

Question

MySql version: 5.7.18-15-log,

My classmate asked a question:

SELECT 
  COUNT(1) Counts, 
  IFNULL((SELECT SUM(c.RealMoney) FROM PayRecord c WHERE a.id=c.orderid), 0) money
FROM `Order` a
WHERE a.UserId ='not exists user id';

he got a result :

Counts:0 money 8000

, and he asked why??? no record, why have money???

I tryed and found it's like a bug, For example:

SELECT
  COUNT(1) Counts, 
  IFNULL((SELECT c.RealMoney FROM PayRecord c WHERE a.id=c.orderid), 0) money
FROM `Order` a
WHERE  a.id='bc7ba7ed-44f2-4686-b5ae-df65e8cc566f';

return

Counts:1 money:8622

SELECT 
  COUNT(1) Counts, 
  IFNULL((SELECT SUM(c.RealMoney) FROM PayRecord c WHERE a.id=c.orderid), 0) money
FROM `Order` a
WHERE a.UserId ='not exists user id';

return

Counts:0 money:8622

It seems the first sql will cached,
and second sql will use this cache...

DDL like :

CREATE TABLE `Order` (
  `Id` char(36) NOT NULL ,
  `UserId` char(36) NOT NULL ,
  ......,
  PRIMARY KEY (`Id`,`CreateTime`),
  KEY `idx_RestId` (`RestId`),
  KEY `idx_CreateTime` (`CreateTime`),
  KEY `idx_UserId` (`UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50500 PARTITION BY RANGE  COLUMNS(CreateTime)
(
 PARTITION p201712 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION p201801 VALUES LESS THAN ('2018-02-01') ENGINE = InnoDB) */


CREATE TABLE `PayRecord` (
  `Id` char(36) NOT NULL ,
  `OrderId` char(36) NOT NULL ,
  `RealMoney` int(11) NOT NULL ,
  .......
  PRIMARY KEY (`Id`,`CreateTime`),
  KEY `idx_OrderId` (`OrderId`),
  KEY `idx_PayId` (`PayId`),
  KEY `idx_CreateTime` (`CreateTime`),
  KEY `idx_BrandId_RestId` (`BrandId`,`RestId`),
  KEY `idx_RestId` (`RestId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(CreateTime)
(
 PARTITION p201712 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION p201801 VALUES LESS THAN ('2018-02-01') ENGINE = InnoDB) */

and my question was: why Count returns no record, but IFNULL has value???

Answer 1

The logic of why you are seeing what you observe is that COUNT is an aggregate function. When used without GROUP BY, it will return a count across the entire table. Also important, it will always return a single record, even if no records are in the actual table. Using COUNT in the select clause also means that only other aggregate functions or scalar constants may appear. The reason you see a sum of money is that it comes from a subquery which generates a single scalar value.

While this may be hard to tease apart, it certainly is not a bug in MySQL.