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???

Do you have null values in your tables? Also, provide us with sample data. — lolbas, Feb 27 '18 at 07:28

score 2 · Accepted Answer · answered Feb 27 '18 at 07:30

2

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.

answered Feb 27 '18 at 07:30

Tim Biegeleisen

502,043
27
286
360

I told my mate to use Standard SQL, but I still didn't understand, why mysql support: SELECT field1, field2, sum(num) FROM tb GROUP BY field1 the group by has no field2 – youbl Feb 27 '18 at 07:39
The query you wrote is possibly invalid, if `field1` is not a primary key column. – Tim Biegeleisen Feb 27 '18 at 07:41
no no no, this sql is valid, and can execute in mysql, but invalid in MSSQL. and thanks for your answer,my question was modified, COUNT + SUM, the quesion still exists – youbl Feb 27 '18 at 07:45
@youbl no no no it is NOT valid SQL and though it may execute on certain configurations of MySQL, the results will be non deterministic. I have answered your question, please don't change or it could draw downvotes. – Tim Biegeleisen Feb 27 '18 at 07:54
yes, the result will be non deterministic. Thanks a lot. – youbl Feb 27 '18 at 08:32

MySql ifnull and count bug?

1 Answers1