MySQL Bugs: #101451: Wrong Results when using WHERE + HAVING + ORDER BY + LIMIT

Bug #101451	Wrong Results when using WHERE + HAVING + ORDER BY + LIMIT
Submitted:	4 Nov 2020 7:19	Modified:	4 Nov 2020 13:50
Reporter:	soheil rahsaz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.32	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
When querying a table and using where clause on indexed columns and having clause on a generated value in select and order by primary key and limit n, MySQL returns a lot fewer results than expected n.
Here I include an example. 
It does not actually reproduce the problem as I saw it, but this is exactly similar to the situation that I actually have.

Instead of returning 15 rows, it returns 6-7 rows.

How to repeat:
DROP TABLE testingBug;
CREATE TABLE `testingBug`
(
    `id`                INT(10) UNSIGNED                        NOT NULL AUTO_INCREMENT,
    `name`              VARCHAR(255) COLLATE utf8mb4_persian_ci NOT NULL,
    `beginDatetime`     CHAR(19) COLLATE utf8mb4_persian_ci     NOT NULL,
    `endDatetime`       CHAR(19) COLLATE utf8mb4_persian_ci     NOT NULL,
    `realBeginDatetime` CHAR(19) COLLATE utf8mb4_persian_ci          DEFAULT NULL,
    `realEndDatetime`   CHAR(19) COLLATE utf8mb4_persian_ci          DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `meetingHostname` (`name`),
    KEY `beginDatetime` (`beginDatetime`),
    KEY `realEndDatetime` (`realEndDatetime`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_persian_ci;
INSERT INTO testingBug (name, beginDatetime, endDatetime, realBeginDatetime, realEndDatetime)
VALUES ('test1', '2020/11/04 08:00:00', '2020/11/04 08:45:00', '2020/11/04 08:01:00', '2020/11/04 08:10:00')
     , ('test2', '2020/11/04 08:00:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test3', '2020/11/04 08:10:00', '2020/11/04 09:40:00', '2020/11/04 08:01:00', NULL)
     , ('test4', '2020/11/04 08:20:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test5', '2020/11/04 08:25:00', '2020/11/04 09:50:00', '2020/11/04 08:01:00', NULL)
     , ('test6', '2020/11/04 08:30:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test7', '2020/11/04 08:17:00', '2020/11/04 09:30:00', '2020/11/04 08:01:00', '2020/11/04 08:30:00')
     , ('test8', '2020/11/04 08:16:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test3', '2020/11/04 08:10:00', '2020/11/04 09:40:00', '2020/11/04 08:01:00', NULL)
     , ('test4', '2020/11/04 08:20:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test5', '2020/11/04 08:25:00', '2020/11/04 09:50:00', '2020/11/04 08:01:00', NULL)
     , ('test6', '2020/11/04 08:30:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test7', '2020/11/04 08:17:00', '2020/11/04 09:30:00', '2020/11/04 08:01:00', '2020/11/04 08:30:00')
     , ('test8', '2020/11/04 08:16:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test5', '2020/11/04 08:25:00', '2020/11/04 09:50:00', '2020/11/04 08:01:00', NULL)
     , ('test6', '2020/11/04 08:30:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test7', '2020/11/04 08:17:00', '2020/11/04 09:30:00', '2020/11/04 08:01:00', '2020/11/04 08:30:00')
     , ('test8', '2020/11/04 08:16:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test3', '2020/11/04 08:10:00', '2020/11/04 09:40:00', '2020/11/04 08:01:00', NULL)
     , ('test4', '2020/11/04 08:20:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test5', '2020/11/04 08:25:00', '2020/11/04 09:50:00', '2020/11/04 08:01:00', NULL)
     , ('test6', '2020/11/04 08:30:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test7', '2020/11/04 08:17:00', '2020/11/04 09:30:00', '2020/11/04 08:01:00', '2020/11/04 08:30:00')
     , ('test8', '2020/11/04 08:16:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('test9', '2020/11/04 08:29:00', '2020/11/04 09:00:00', '2020/11/04 08:01:00', NULL)
     , ('tes10', '2020/11/04 08:28:00', '2020/11/04 09:10:00', '2020/11/04 08:01:00', '2020/11/04 08:50:00');

SELECT *,
       IF(realEndDatetime IS NOT NULL, TT('ended'),
          IF(realBeginDatetime IS NULL,
             IF('2020/11/04 08:50:00' > endDatetime, TT('cancelled'),
                TT('waiting')),
             IF('2020/11/04 08:50:00' > endDatetime, TT('ended'),
                TT('running')))) AS status
FROM testingBug
WHERE beginDatetime LIKE '2020/11/04%'
  AND !(realBeginDatetime IS NULL AND realEndDatetime IS NOT NULL)
HAVING `status` LIKE N'%run%'
ORDER BY `id` DESC
LIMIT 0,15;

Hi Mr. rahsaz,

Thank you for your bug report.

However, this is not a bug.

HAVING clause is there to be used only with aggregated queries and your query is not an aggregated one.

Not a bug.

I may have failed to explain what is actually happening.
In my table, I have a lot of data which with the given query, it returns 300 rows (without limit clause). But when I apply the `LIMIT 15` clause, it returns 7 rows. 
Also, when I remove `ORDER BY` clause, it works fine with having and limit.
Is this normal to happen with having clause?
Thanks in advance.

I just found this:
https://dev.mysql.com/doc/refman/8.0/en/select.html#:~:text=The%20SQL%20standard%20require....

"The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well."

Sorry I didn't find it earlier.

Hi Mr. rahsaz,

Short answer to your question is that it is the expected result.