after changing YEAR function ,col_2's value unexpectedly become NULL

[chen8908917]

after changing YEAR function ,col_2's value unexpectedly become NULL

mysql> WITH cte_128 AS (SELECT (SELECT GROUP_CONCAT(s872.c3 ORDER BY s872.c3 SEPARATOR ',') AS subq_col FROM t1 AS s872) AS col_1, lhl17.c6 AS col_2 FROM t1 AS lhl17) SELECT (SELECT AVG(63) AS subq_col FROM t1 AS s865) AS col_1, YEAR(rkr44.c5) AS col_2 FROM t3 AS rkr44 CROSS JOIN cte_128 AS wne59 ON 1 = 1 GROUP BY YEAR(rkr44.c5), YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1;
+---------+-------+
| col_1   | col_2 |
+---------+-------+
| 63.0000 |  2026 |
+---------+-------+
1 row in set (0.03 sec)

mysql> WITH cte_128 AS (SELECT (SELECT GROUP_CONCAT(s872.c3 ORDER BY s872.c3 SEPARATOR ',') AS subq_col FROM t1 AS s872) AS col_1, lhl17.c6 AS col_2 FROM t1 AS lhl17) SELECT (SELECT AVG(63) AS subq_col FROM t1 AS s865) AS col_1, YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1 AS col_2 FROM t3 AS rkr44 CROSS JOIN cte_128 AS wne59 ON 1 = 1 GROUP BY YEAR(rkr44.c5), YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1;
+---------+-------+
| col_1   | col_2 |
+---------+-------+
| 63.0000 |  NULL |
+---------+-------+
1 row in set (0.03 sec)

Additionally in nysql:9.5.0,this bug has been solved

mysql> WITH cte_128 AS (SELECT (SELECT GROUP_CONCAT(s872.c3 ORDER BY s872.c3 SEPARATOR ',') AS subq_col FROM t1 AS s872) AS col_1, lhl17.c6 AS col_2 FROM t1 AS lhl17) SELECT (SELECT AVG(63) AS subq_col FROM t1 AS s865) AS col_1, YEAR(rkr44.c5) AS col_2 FROM t3 AS rkr44 CROSS JOIN cte_128 AS wne59 ON 1 = 1 GROUP BY YEAR(rkr44.c5), YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1;
+---------+-------+
| col_1   | col_2 |
+---------+-------+
| 63.0000 |  2026 |
+---------+-------+
1 row in set (0.00 sec)

mysql> WITH cte_128 AS (SELECT (SELECT GROUP_CONCAT(s872.c3 ORDER BY s872.c3 SEPARATOR ',') AS subq_col FROM t1 AS s872) AS col_1, lhl17.c6 AS col_2 FROM t1 AS lhl17) SELECT (SELECT AVG(63) AS subq_col FROM t1 AS s865) AS col_1, YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1 AS col_2 FROM t3 AS rkr44 CROSS JOIN cte_128 AS wne59 ON 1 = 1 GROUP BY YEAR(rkr44.c5), YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1;
+---------+-------+
| col_1   | col_2 |
+---------+-------+
| 63.0000 |  2026 |
+---------+-------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.5.0     |
+-----------+
1 row in set (0.00 sec)

How to repeat

DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE t1 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 VARCHAR(255) NOT NULL,
    c3 VARCHAR(255) NULL,
    c4 INT NULL,
    c5 DATE NOT NULL,
    c6 VARCHAR(10) NOT NULL,
    PRIMARY KEY (c1)
);

CREATE TABLE t3 (
    c1 INT NOT NULL AUTO_INCREMENT,
    c2 INT NOT NULL,
    c3 INT NOT NULL,
    c4 YEAR NOT NULL,
    c5 TIME NULL,
    c6 TINYINT NULL,
    c7 SMALLINT NULL,
    c8 MEDIUMINT NULL,
    c9 BIGINT NULL,
    c10 LONGTEXT NULL,
    c11 GEOMETRY NULL,
    c12 TINYTEXT NULL,
    c13 TINYBLOB NULL,
    c14 SET('x','y','z') NULL,
    c15 TINYINT(1) NULL,
    PRIMARY KEY (c1)
);

INSERT INTO t1 (c1, c2, c3, c4, c5, c6) VALUES (9505, 'sample_j6tRCMl2ntyXKOJEbYA1cM0rvcwpdKAcFn0YWKrCKnu3WgFFCrLj3UNBc3r0OF7q8uip4bOEuLRYII19C2S5qEaJjRJTU8TKZLWWoGUsHSVRxz1dRztOnTDtYBY4DRndJEl80NQgnZRcyNjwqJWoje0BloyBO6pONgdOaYlL4LVYA4YBB97fuaiNZHuqN3cbb0aI6BdVxhvNwuVXqLZEI4NTUQBbg2QSoJ9IL49VpgrAVaahv9DPz', 'sample_FslXMXkrstdX6KR63rp7SuzwgkzaVAWVG7pPEULjq3STRGzDQ82473KqhecREn4XoFg63sUdHz6awadu3vYDH2tEXOy0zP4Ybr4epzn8AniupuoryIsLYB2BvUcqxi2V3X1DxNA8bf4Co0emlCYz', 61, '2025-12-02', 'sample_h');

INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (4992, 2579, 9871, 2020, '2023-01-01 22:44:35', 46, 25, 79, 9, 'sample_AEtk3qwMxslH0XcoF3Ort1thTgJYkZUiwpxtTmxhWIgxq52IV6TU1YcGOwgH8ldWWyvLDqO7YEEgwONICRY92VcLmnijCrTWzOC5R0WrbXNpwg9ya9bAisamL0fbNK0b92wUXq6oxdO9BuGPlWyaS9ve6M7IRoQQkFBrDpsmDuQug7b8jOSxnBk6ztIrDvaLYM4aeQhSZMuJPSf3RkPeoIh5QanRUBtm3O5VXocvNbAJyTQgt3AhB2llo5tnO1bmh9gFLalw0NfvIsxlKUpQ4G6ZHsGpnQaXH6e4lPDDVX07bfgBSZNm0uolja55cRN9BAdSFKgaZmSxZywU0nR2iwTbewfhEq2IK2tVCQ0t9tOR4dtVjhYNtLoYYms3DWpah9BW9DpqKD4bBtCwiu9JgCaQys69sn53gW6jO5P734nR266nV4D1NG09DR0v4FhN4BSxwm6yQIQgXJUPpXIuJgM0Jzr0ZNNrbEHw6TUT2OjjXow92FMiXruAGixuDuZMkOf0LKqES6vOtMOKozH8Ldr4Sp4LSFDPc1qQFKD6sPtP9ngVqfI0WTuDWQTGS65hVh0p5KvlFKK3tkblFuonjfEyKG5QNDinXOVK3tgM0SRHnW8RQJ1rXgJHoQZOEopxKJFE7tHfLpqswhlT0ziNwLqZ6sxQ8eSIlA0jAFO8WjJGwXXOfs4xgmEI4eRav0lK4PuEOjQAnb2WHltjMA5rqHlGrcTcGpklPpdCJtBg6WEIYNvXihQqRvUg1Jw5ctg8rVgXJSb72yhESghMl7INYchsjZnuIkAI48xe0VSgGZgjg3rdhi02urw1zJDcPeupTXtMYmMQN1w9a1KXTYXu7Ysd6UDQLFjJyEKjcfQaHmyGvsZncCFWkLRjWPKyja8PQmWdLEOcuGplBdLcCoK8tQnX70sYeHLYqR9UnenaN5XXKahe0qpAdDBgjQYZvDadkvdqU591bfCHkgUBH70Z1Hfl51ukCHOty2JzcOvT6SaSVKNgGvTch6h7WRBMpBgJCL2ago2YZYPreIw3GF17xw7mjwk6QfnLAY23TgLTUV9TrCxQpcZD90YW8SCqZbXKYgfAsgt4OKrf2LZbQr0ziFfcF68MTm34rJmnPm1w0mIGCwMmU', ST_GeomFromText('POINT(89.285446 -33.318821)'), 'sample_YFFtbMWTmNenOjXxHy0YfyUoPQSfJAVvKICpHFIIMenooB36lYoAS3hPjCLJlPqpVho', X'D4A7E898B817C88330CBBBE9B0B6C2A736', 'z', NULL);

WITH cte_128 AS (SELECT (SELECT GROUP_CONCAT(s872.c3 ORDER BY s872.c3 SEPARATOR ',') AS subq_col FROM t1 AS s872) AS col_1, lhl17.c6 AS col_2 FROM t1 AS lhl17) SELECT (SELECT AVG(63) AS subq_col FROM t1 AS s865) AS col_1, YEAR(rkr44.c5) AS col_2 FROM t3 AS rkr44 CROSS JOIN cte_128 AS wne59 ON 1 = 1 GROUP BY YEAR(rkr44.c5), YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1;
WITH cte_128 AS (SELECT (SELECT GROUP_CONCAT(s872.c3 ORDER BY s872.c3 SEPARATOR ',') AS subq_col FROM t1 AS s872) AS col_1, lhl17.c6 AS col_2 FROM t1 AS lhl17) SELECT (SELECT AVG(63) AS subq_col FROM t1 AS s865) AS col_1, YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1 AS col_2 FROM t3 AS rkr44 CROSS JOIN cte_128 AS wne59 ON 1 = 1 GROUP BY YEAR(rkr44.c5), YEAR(rkr44.c5 + INTERVAL '1' YEAR) - 1;

Version

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

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.00 sec)

[hustfxj]

This bug has been fixed in the latest internal branch, and we will release it promptly later. Thanks again @chen8908917