MySQL Bugs: #107363: Unexpected results if the query with subquery, FLOOR and GROUP BY.

Bug #107363	Unexpected results if the query with subquery, FLOOR and GROUP BY.
Submitted:	22 May 2022 14:01	Modified:	23 May 2022 7:18
Reporter:	Zhang JiYang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the following use case, an unexpected result is obtained.

How to repeat:
CREATE TABLE `t2` (
  `c1` bigint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `t2` VALUES (5),(6),(8);

SELECT
  a.*
FROM
  (
    SELECT
      a_c1,
      floor(a_c1 / 1) AS c1_1
    FROM
      (
        SELECT
          t.c1 as a_c1
        FROM
          t2 t
        GROUP BY
          t.c1
      ) b
  ) a
where c1_1 > 0;

+------+------+
| a_c1 | c1_1 |
+------+------+
|    5 |    8 |
|    6 |    8 |
|    8 |    8 |
+------+------+
3 rows in set (0.00 sec)

But if it is the following query：
SELECT
  a.*
FROM
  (
    SELECT
      a_c1,
      floor(a_c1 / 1) AS c1_1
    FROM
      (
        SELECT
          t.c1 as a_c1
        FROM
          t2 t
        GROUP BY
          t.c1
      ) b
  ) a;

+------+------+
| a_c1 | c1_1 |
+------+------+
|    5 |    5 |
|    6 |    6 |
|    8 |    8 |
+------+------+

Hello Zhang JiYang,

Thank you for the report and feedback.
Confirmed that 8.0.25 build is affected but issue is not seen since 8.0.26 build.

8.0.26+
--
mysql> SELECT
    ->   a.*
    -> FROM
    ->   (
    ->     SELECT
    ->       a_c1,
    ->       floor(a_c1 / 1) AS c1_1
    ->     FROM
    ->       (
    ->         SELECT
    ->           t.c1 as a_c1
    ->         FROM
    ->           t2 t
    ->         GROUP BY
    ->           t.c1
    ->       ) b
    ->   ) a
    -> where c1_1 > 0;
+------+------+
| a_c1 | c1_1 |
+------+------+
|    5 |    5 |
|    6 |    6 |
|    8 |    8 |
+------+------+
3 rows in set (0.01 sec)

mysql> SELECT
    ->   a.*
    -> FROM
    ->   (
    ->     SELECT
    ->       a_c1,
    ->       floor(a_c1 / 1) AS c1_1
    ->     FROM
    ->       (
    ->         SELECT
    ->           t.c1 as a_c1
    ->         FROM
    ->           t2 t
    ->         GROUP BY
    ->           t.c1
    ->       ) b
    ->   ) a;
+------+------+
| a_c1 | c1_1 |
+------+------+
|    5 |    5 |
|    6 |    6 |
|    8 |    8 |
+------+------+
3 rows in set (0.00 sec)

- 8.0.25 - affected

mysql> SELECT
    ->   a.*
    -> FROM
    ->   (
    ->     SELECT
    ->       a_c1,
    ->       floor(a_c1 / 1) AS c1_1
    ->     FROM
    ->       (
    ->         SELECT
    ->           t.c1 as a_c1
    ->         FROM
    ->           t2 t
    ->         GROUP BY
    ->           t.c1
    ->       ) b
    ->   ) a
    -> where c1_1 > 0;
+------+------+
| a_c1 | c1_1 |
+------+------+
|    5 |    8 |
|    6 |    8 |
|    8 |    8 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   a.*
    -> FROM
    ->   (
    ->     SELECT
    ->       a_c1,
    ->       floor(a_c1 / 1) AS c1_1
    ->     FROM
    ->       (
    ->         SELECT
    ->           t.c1 as a_c1
    ->         FROM
    ->           t2 t
    ->         GROUP BY
    ->           t.c1
    ->       ) b
    ->   ) a;
+------+------+
| a_c1 | c1_1 |
+------+------+
|    5 |    5 |
|    6 |    6 |
|    8 |    8 |
+------+------+
3 rows in set (0.00 sec)

Please note that we don't fix bugs in old versions, don't back-port bug fixes, so you need to check with latest MySQL Server version. So, please, upgrade MySQL Server to 8.0.29 and inform us if problem still exists. Thank you!

regards,
Umesh

Thank you very much!