Bug #107363 Unexpected results if the query with subquery, FLOOR and GROUP BY.
Submitted: 22 May 14:01 Modified: 23 May 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

[22 May 14:01] Zhang JiYang
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 |
+------+------+
[23 May 7:15] MySQL Verification Team
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
[23 May 7:18] Zhang JiYang
Thank you very much!