an error unexpectedly appear after changing avg(x) to avg(x)+5

[chen8908917]

after changing AVG(alb59.c14) to AVG(alb59.c14) + 5,error 4501 appear unexpectedly

mysql> WITH cte_993 AS (SELECT RANK() OVER (PARTITION BY alb59.c11 ORDER BY alb59.c5 DESC) AS col_1, alb59.c14 AS col_2, alb59.c14 AS col_3, AVG(alb59.c14) AS col_4 FROM t2 AS alb59 USE INDEX (idx_t2_c10) GROUP BY alb59.c11, alb59.c5, alb59.c14) SELECT cte.col_3 AS col_3, 'sample_76' AS col_1, cte.col_1 AS col_1_1 FROM cte_993 AS cte;
+-------+-----------+---------+
| col_3 | col_1     | col_1_1 |
+-------+-----------+---------+
| 90.91 | sample_76 |       1 |
+-------+-----------+---------+
1 row in set (0.02 sec)

mysql> WITH cte_993 AS (SELECT RANK() OVER (PARTITION BY alb59.c11 ORDER BY alb59.c5 DESC) AS col_1, alb59.c14 AS col_2, alb59.c14 AS col_3, AVG(alb59.c14) + 5 AS col_4 FROM t2 AS alb59 USE INDEX (idx_t2_c10) GROUP BY alb59.c11, alb59.c5, alb59.c14) SELECT cte.col_3 AS col_3, 'sample_76' AS col_1, cte.col_1 AS col_1_1 FROM cte_993 AS cte;
ERROR 4501 (HY000): [1ac921099a400000][172.17.0.7:8527][test]ERR-CODE: [PXC-4501][ERR_OPTIMIZER] optimize error by java.lang.IllegalArgumentException

How to repeat

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE t2 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 INT NOT NULL,
    c3 DECIMAL(10,2) NOT NULL,
    c4 VARCHAR(50) NOT NULL,
    c5 DATE NOT NULL,
    c6 MEDIUMTEXT NULL,
    c7 LONGTEXT NULL,
    c8 MEDIUMBLOB NULL,
    c9 LONGBLOB NULL,
    c10 ENUM('value1','value2','value3') NULL,
    c11 SET('a','b','c','d') NULL,
    c12 BIT(8) NULL,
    c13 DATETIME NULL,
    c14 FLOAT(8,2) NULL,
    c15 DOUBLE(12,4) NULL,
    PRIMARY KEY (c1)
);

INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (994, 3423, 181.05, 'sample_wfxp8dzD7XrFnPLzr', '2024-12-16', 'sample_pVtuVRHgHyCTAdMjTX5uDmgDFTTcRtYK4MA3VzWGhxyRAthAan095ltHnWC2wZqFzmNIz2BhrrY2h0fxo8UzijtqT3Qzyc7ydCZ760AbwzyWxKRhxPNIiXBNmglw9ylwoKoF0Q5QMAuWKKHLT07TyALEjZEq9SXDPPHZkG637npBmt2TKOxONpcXFVVrsW3jgaSAVhmwZMYON8YAyhzkhs0fsDZAohNdcrXeb43HI5de5EWnnY2lj8xyjRyfvWyVnBrhfeRiVnwUHuk0bcwoN3ueyVTRnXwQSF56ZbxUsw4hUmzUQ4GGRNJqlHOzrdMFLZHnsp5SamGZ0WPFJC1Vx1RuAnB5RdThXSIL8o2Fgebw5VIlETIWBvsuSQqrlbUxl0DbKa4xwcIiP23T8cVxdUgU4xKNvx6nab1kO47gdMcGoSGyXyvr23yKaVpjtw6XNe0uwE6zNt4nqZ1IMHxo', 'sample_oIxWhZ5PNoCnBUDNNG1t4uGQ80Jm3dOMyFRe0xsG8bqvWYFDuMqCy715ukwGqx4YD0tNg95vNkY49I1JPAbh5BBrcbTCYpkH1R37hDwaEo0Lzl91rc0tCeXUzOy258wzk7DX3Z11LNDgWU4awzLAAqwgg6l6QUVNdWbDUN3MQYXgivI8FcfivR8MVM40OIkt9fO8BNRyM2wTSSdov5M69ahQHUactc9lQyhXQfKFssG6OK2qKe2LIxK7FT1nHxPTNh7d41fB2bHcrjQ51tWpKJcLUFVVansOZC4fsvYKAamSAEMg3CsdHRNxw9dNCn5504tI75YCiAZT9DJEv7eTX7R07OJUaKE95P4VCs4XzftyMlVR4sPXkCsh7k7MYYBRd5d9iJdQLLOKXV3FUX5qjMRwF0oltoDkNGhVzUwlRuOptO8ROwzJLwCo5Zc7ce9l3bVoWQ3LsFymuRfVPQNPDkec3GZtAddEZBmIvzojWCpi0vhTzdYojxEdQwMHomRvrrYI7XWkrLrOOYwgMnPfHTeyO9oBFhEksiqIvbJHAEP2xCD2J0NvGcap1lZc2ueOUe6pJbZ95HmiFyRwwxMOxfLY4VCD5vgXNZTzeP0q8G0caUPPmLnaPcrw0vcnvR1gJnPdl8YyggslnNMOFmhPU2TK8EO5Xn6QVpvb6zPaM3Bw6sK3u10cw3i3jPuAYGSdMFNIdrU6L0zMUiI1LO4T7us2MUaXM', X'4DD6A6D687C6A9C08CDA93E9B48429DAA8ECBF93E891B67DDEBCDEBCE79C8A20', X'CF8CCBA2C880ED9DA9CF8644ECA5BD53E6B0AAED898E10C398D0AD', 'value1', 'a,c,b,d', b'01101010', '2025-04-25 18:28:30', 90.91, 53.96);

CREATE  INDEX idx_t2_c10 ON t2 (c10);
WITH cte_993 AS (SELECT RANK() OVER (PARTITION BY alb59.c11 ORDER BY alb59.c5 DESC) AS col_1, alb59.c14 AS col_2, alb59.c14 AS col_3, AVG(alb59.c14) AS col_4 FROM t2 AS alb59 USE INDEX (idx_t2_c10) GROUP BY alb59.c11, alb59.c5, alb59.c14) SELECT cte.col_3 AS col_3, 'sample_76' AS col_1, cte.col_1 AS col_1_1 FROM cte_993 AS cte; 
WITH cte_993 AS (SELECT RANK() OVER (PARTITION BY alb59.c11 ORDER BY alb59.c5 DESC) AS col_1, alb59.c14 AS col_2, alb59.c14 AS col_3, AVG(alb59.c14) + 5 AS col_4 FROM t2 AS alb59 USE INDEX (idx_t2_c10) GROUP BY alb59.c11, alb59.c5, alb59.c14) SELECT cte.col_3 AS col_3, 'sample_76' AS col_1, cte.col_1 AS col_1_1 FROM cte_993 AS cte; 

Version

mysql> select polardb_version();
+---------+--------------+---------------------+
| TYPE    | VERSION      | RELEASE_DATE        |
+---------+--------------+---------------------+
| Product | PolarDB V2.0 | Distributed Edition |
| CN      | 2.4.0.5.4.19 | SNAPSHOT            |
| DN      | 2.4.0.8.4.19 | 20240430            |
| GMS     | 2.4.0.8.4.19 | 20240430            |
+---------+--------------+---------------------+
4 rows in set (0.12 sec)

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 8.0.32-X-Cluster-8.4.19 |
+-------------------------+
1 row in set (0.01 sec)