MySQL Bugs: #106063: Condition on aggregared expr in HAVING clause produces bad result in some cases

Bug #106063	Condition on aggregared expr in HAVING clause produces bad result in some cases
Submitted:	5 Jan 2022 12:42	Modified:	6 Jan 2022 14:12
Reporter:	Michal Vorisek	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0, 8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	aggregate, having, regression, wrong result

Description:
```
sum(`amount`) > 3.9 and sum(`amount`) < 4.1
```

condition in having clause does not match any record on MySQL Server 8.0

demo v5.7 (working as expected) https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=31af0af14c59492f76534ba53828dda5

demo v8.0 (no result) https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=31af0af14c59492f76534ba53828dda5

Given the fact the query works on MySQL Server 5.7, and on the latest Maria DB, MSSQL and Sqlite, I am highly confident this is a serious issue in MySQL Server that needs to be fixed in the next release.

How to repeat:
CREATE TABLE `client` (
  `id` INT UNSIGNED AUTO_INCREMENT NOT NULL, 
  `name` VARCHAR(255) DEFAULT NULL, 
  `surname` VARCHAR(255) DEFAULT NULL, 
  `order` INT DEFAULT NULL, 
  PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

insert into `client` (`name`, `surname`, `order`) 
values 
  ('Vinny', 'Rad', 2);

insert into `client` (`name`, `surname`, `order`) 
values 
  ('Zoe', 'Hansen', 1);

CREATE TABLE `invoice` (
  `id` INT UNSIGNED AUTO_INCREMENT NOT NULL, 
  `client_id` INT DEFAULT NULL, 
  `name` VARCHAR(255) DEFAULT NULL, 
  `amount` DOUBLE PRECISION DEFAULT NULL, 
  PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

insert into `invoice` (`client_id`, `name`, `amount`) 
values 
  (1, 'chair purchase', 4);

insert into `invoice` (`client_id`, `name`, `amount`) 
values 
  (1, 'table purchase', 15);

insert into `invoice` (`client_id`, `name`, `amount`) 
values 
  (2, 'chair purchase', 4);

select 
  (
    select 
      `name` 
    from 
      (
        select 
          `id`, 
          `name`, 
          `surname`, 
          `order` 
        from 
          `client`
      ) `_c_2bfe9d72a4aa` 
    where 
      `id` = `__inner__`.`client_id`
  ) `client`, 
  `client_id`, 
  sum(`amount`) `amount` 
from 
  (
    select 
      `id`, 
      `name`, 
      `client_id`, 
      `amount` 
    from 
      `invoice`
  ) `__inner__` 
group by 
  `client_id` 
having 
  (
    `client_id` = 2
    and sum(`amount`) > 3.9 and sum(`amount`) < 4.1
  );

Suggested fix:
the query must return one row as on MySQL 5.7 and other major DB vendors

Hello Michal Vorisek,

Thank you for the report and feedback.

regards,
Umesh

- 8.0.27 affected

rm -rf 106063/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/106063 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/106063 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3306 --log-error=$PWD/106063/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ 2>&1 &

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `client` (
    ->   `id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ->   `name` VARCHAR(255) DEFAULT NULL,
    ->   `surname` VARCHAR(255) DEFAULT NULL,
    ->   `order` INT DEFAULT NULL,
    ->   PRIMARY KEY(`id`)
    -> ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> insert into `client` (`name`, `surname`, `order`)
    -> values
    ->   ('Vinny', 'Rad', 2);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> insert into `client` (`name`, `surname`, `order`)
    -> values
    ->   ('Zoe', 'Hansen', 1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> CREATE TABLE `invoice` (
    ->   `id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ->   `client_id` INT DEFAULT NULL,
    ->   `name` VARCHAR(255) DEFAULT NULL,
    ->   `amount` DOUBLE PRECISION DEFAULT NULL,
    ->   PRIMARY KEY(`id`)
    -> ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> insert into `invoice` (`client_id`, `name`, `amount`)
    -> values
    ->   (1, 'chair purchase', 4);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> insert into `invoice` (`client_id`, `name`, `amount`)
    -> values
    ->   (1, 'table purchase', 15);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> insert into `invoice` (`client_id`, `name`, `amount`)
    -> values
    ->   (2, 'chair purchase', 4);
Query OK, 1 row affected (0.00 sec)

mysql> select
    ->   (
    ->     select
    ->       `name`
    ->     from
    ->       (
    ->         select
    ->           `id`,
    ->           `name`,
    ->           `surname`,
    ->           `order`
    ->         from
    ->           `client`
    ->       ) `_c_2bfe9d72a4aa`
    ->     where
    ->       `id` = `__inner__`.`client_id`
    ->   ) `client`,
    ->   `client_id`,
    ->   sum(`amount`) `amount`
    -> from
    ->   (
    ->     select
    ->       `id`,
    ->       `name`,
    ->       `client_id`,
    ->       `amount`
    ->     from
    ->       `invoice`
    ->   ) `__inner__`
    -> group by
    ->   `client_id`
    -> having
    ->   (
    ->     `client_id` = 2
    ->     and sum(`amount`) > 3.9
    ->     and sum(`amount`) < 4.1
    ->   );
Empty set (0.00 sec)

-- Looks like regression
-- Lowest checked version 8.0.11 - not affected

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `client` (
    ->   `id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ->   `name` VARCHAR(255) DEFAULT NULL,
    ->   `surname` VARCHAR(255) DEFAULT NULL,
    ->   `order` INT DEFAULT NULL,
    ->   PRIMARY KEY(`id`)
    -> ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql>
mysql> insert into `client` (`name`, `surname`, `order`)
    -> values
    ->   ('Vinny', 'Rad', 2);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> insert into `client` (`name`, `surname`, `order`)
    -> values
    ->   ('Zoe', 'Hansen', 1);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> CREATE TABLE `invoice` (
    ->   `id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ->   `client_id` INT DEFAULT NULL,
    ->   `name` VARCHAR(255) DEFAULT NULL,
    ->   `amount` DOUBLE PRECISION DEFAULT NULL,
    ->   PRIMARY KEY(`id`)
    -> ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> insert into `invoice` (`client_id`, `name`, `amount`)
    -> values
    ->   (1, 'chair purchase', 4);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> insert into `invoice` (`client_id`, `name`, `amount`)
    -> values
    ->   (1, 'table purchase', 15);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql>
mysql> insert into `invoice` (`client_id`, `name`, `amount`)
    -> values
    ->   (2, 'chair purchase', 4);
Query OK, 1 row affected (0.00 sec)

mysql> select
    ->   (
    ->     select
    ->       `name`
    ->     from
    ->       (
    ->         select
    ->           `id`,
    ->           `name`,
    ->           `surname`,
    ->           `order`
    ->         from
    ->           `client`
    ->       ) `_c_2bfe9d72a4aa`
    ->     where
    ->       `id` = `__inner__`.`client_id`
    ->   ) `client`,
    ->   `client_id`,
    ->   sum(`amount`) `amount`
    -> from
    ->   (
    ->     select
    ->       `id`,
    ->       `name`,
    ->       `client_id`,
    ->       `amount`
    ->     from
    ->       `invoice`
    ->   ) `__inner__`
    -> group by
    ->   `client_id`
    -> having
    ->   (
    ->     `client_id` = 2
    ->     and sum(`amount`) > 3.9
    ->     and sum(`amount`) < 4.1
    ->   );
+--------+-----------+--------+
| client | client_id | amount |
+--------+-----------+--------+
| Zoe    |         2 |      4 |
+--------+-----------+--------+
1 row in set (0.01 sec)

Duplicate of

Bug#105692 Incorrect result with aggregation by temporary table

which has been fixed in the upcoming 8.0.28.