Description:
after changing FLOOR(ASIN(hpg31.c1)) to FLOOR(FLOOR(ASIN(hpg31.c1))),col_1’s value unexpectedly change
mysql> (SELECT DISTINCT MIN(hpg31.c5) AS col_1, GROUP_CONCAT(hpg31.c4 - INTERVAL hpg31.c1 DAY ORDER BY hpg31.c4 - INTERVAL hpg31.c1 DAY) AS col_2, FLOOR(ASIN(hpg31.c1)) AS col_3 FROM t3 AS hpg31 GROUP BY FLOOR(ASIN(hpg31.c1))) UNION ALL SELECT nhp53.c5 AS col_1, nhp53.c6 AS col_2, nhp53.c1 AS col_3 FROM t1 AS nhp53;
+---------------------+----------+-------+
| col_1 | col_2 | col_3 |
+---------------------+----------+-------+
| 0000-00-00 00:00:00 | NULL | NULL |
| 2025-12-02 00:00:00 | sample_h | 9505 |
+---------------------+----------+-------+
2 rows in set, 3 warnings (0.00 sec)
mysql> (SELECT DISTINCT MIN(hpg31.c5) AS col_1, GROUP_CONCAT(hpg31.c4 - INTERVAL hpg31.c1 DAY ORDER BY hpg31.c4 - INTERVAL hpg31.c1 DAY) AS col_2, FLOOR(FLOOR(ASIN(hpg31.c1))) AS col_3 FROM t3 AS hpg31 GROUP BY FLOOR(ASIN(hpg31.c1)), FLOOR(FLOOR(ASIN(hpg31.c1)))) UNION ALL SELECT nhp53.c5 AS col_1, nhp53.c6 AS col_2, nhp53.c1 AS col_3 FROM t1 AS nhp53;
+---------------------+----------+-------+
| col_1 | col_2 | col_3 |
+---------------------+----------+-------+
| 2026-01-16 16:25:52 | NULL | NULL |
| 2025-12-02 00:00:00 | sample_h | 9505 |
+---------------------+----------+-------+
2 rows in set, 2 warnings (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);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (7303, 139, 4133, 2003, '2023-01-01 16:25:52', 20, 9, 19, 88, 'sample_ViPulNdH6FUzfJ8JRDVqFzxBVTZH175FZgnVlTCohFNarLZfxqRES7H8cqkBRcL81NUQnUCrjLdnkw2g9L0Mi1Ju4rRph1Z4600t60vIM4gWiaI7pxQGBei1Ouwe6o8B1Gwx24yJhVf7Yotg84eaJ5GRoB1xNpGPwHTNdEpT3Oz4LzntAspK6CmZX0Pfx23RBmwdywJNC7', ST_GeomFromText('POINT(-29.541262 3.85335)'), 'sample_h5vmQRTsjphZItuTsm9OZuBpPd5lWJwNmdDzgmElXldwrgKEVwwPyTmQi8oowAoSavjmPVTDlGl', X'E782B6EA838BC285EEA2B2E0AFA8C8816CC9BB43E9A8A516EC9EBDDE916F0BCCAAD6A9C686ED93B57DE89DA2E5B8937DCCA8', 'x,z', NULL);
(SELECT DISTINCT MIN(hpg31.c5) AS col_1, GROUP_CONCAT(hpg31.c4 - INTERVAL hpg31.c1 DAY ORDER BY hpg31.c4 - INTERVAL hpg31.c1 DAY) AS col_2, FLOOR(ASIN(hpg31.c1)) AS col_3 FROM t3 AS hpg31 GROUP BY FLOOR(ASIN(hpg31.c1))) UNION ALL SELECT nhp53.c5 AS col_1, nhp53.c6 AS col_2, nhp53.c1 AS col_3 FROM t1 AS nhp53;
(SELECT DISTINCT MIN(hpg31.c5) AS col_1, GROUP_CONCAT(hpg31.c4 - INTERVAL hpg31.c1 DAY ORDER BY hpg31.c4 - INTERVAL hpg31.c1 DAY) AS col_2, FLOOR(FLOOR(ASIN(hpg31.c1))) AS col_3 FROM t3 AS hpg31 GROUP BY FLOOR(ASIN(hpg31.c1)), FLOOR(FLOOR(ASIN(hpg31.c1)))) UNION ALL SELECT nhp53.c5 AS col_1, nhp53.c6 AS col_2, nhp53.c1 AS col_3 FROM t1 AS nhp53;
Description: after changing FLOOR(ASIN(hpg31.c1)) to FLOOR(FLOOR(ASIN(hpg31.c1))),col_1’s value unexpectedly change mysql> (SELECT DISTINCT MIN(hpg31.c5) AS col_1, GROUP_CONCAT(hpg31.c4 - INTERVAL hpg31.c1 DAY ORDER BY hpg31.c4 - INTERVAL hpg31.c1 DAY) AS col_2, FLOOR(ASIN(hpg31.c1)) AS col_3 FROM t3 AS hpg31 GROUP BY FLOOR(ASIN(hpg31.c1))) UNION ALL SELECT nhp53.c5 AS col_1, nhp53.c6 AS col_2, nhp53.c1 AS col_3 FROM t1 AS nhp53; +---------------------+----------+-------+ | col_1 | col_2 | col_3 | +---------------------+----------+-------+ | 0000-00-00 00:00:00 | NULL | NULL | | 2025-12-02 00:00:00 | sample_h | 9505 | +---------------------+----------+-------+ 2 rows in set, 3 warnings (0.00 sec) mysql> (SELECT DISTINCT MIN(hpg31.c5) AS col_1, GROUP_CONCAT(hpg31.c4 - INTERVAL hpg31.c1 DAY ORDER BY hpg31.c4 - INTERVAL hpg31.c1 DAY) AS col_2, FLOOR(FLOOR(ASIN(hpg31.c1))) AS col_3 FROM t3 AS hpg31 GROUP BY FLOOR(ASIN(hpg31.c1)), FLOOR(FLOOR(ASIN(hpg31.c1)))) UNION ALL SELECT nhp53.c5 AS col_1, nhp53.c6 AS col_2, nhp53.c1 AS col_3 FROM t1 AS nhp53; +---------------------+----------+-------+ | col_1 | col_2 | col_3 | +---------------------+----------+-------+ | 2026-01-16 16:25:52 | NULL | NULL | | 2025-12-02 00:00:00 | sample_h | 9505 | +---------------------+----------+-------+ 2 rows in set, 2 warnings (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); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (7303, 139, 4133, 2003, '2023-01-01 16:25:52', 20, 9, 19, 88, 'sample_ViPulNdH6FUzfJ8JRDVqFzxBVTZH175FZgnVlTCohFNarLZfxqRES7H8cqkBRcL81NUQnUCrjLdnkw2g9L0Mi1Ju4rRph1Z4600t60vIM4gWiaI7pxQGBei1Ouwe6o8B1Gwx24yJhVf7Yotg84eaJ5GRoB1xNpGPwHTNdEpT3Oz4LzntAspK6CmZX0Pfx23RBmwdywJNC7', ST_GeomFromText('POINT(-29.541262 3.85335)'), 'sample_h5vmQRTsjphZItuTsm9OZuBpPd5lWJwNmdDzgmElXldwrgKEVwwPyTmQi8oowAoSavjmPVTDlGl', X'E782B6EA838BC285EEA2B2E0AFA8C8816CC9BB43E9A8A516EC9EBDDE916F0BCCAAD6A9C686ED93B57DE89DA2E5B8937DCCA8', 'x,z', NULL); (SELECT DISTINCT MIN(hpg31.c5) AS col_1, GROUP_CONCAT(hpg31.c4 - INTERVAL hpg31.c1 DAY ORDER BY hpg31.c4 - INTERVAL hpg31.c1 DAY) AS col_2, FLOOR(ASIN(hpg31.c1)) AS col_3 FROM t3 AS hpg31 GROUP BY FLOOR(ASIN(hpg31.c1))) UNION ALL SELECT nhp53.c5 AS col_1, nhp53.c6 AS col_2, nhp53.c1 AS col_3 FROM t1 AS nhp53; (SELECT DISTINCT MIN(hpg31.c5) AS col_1, GROUP_CONCAT(hpg31.c4 - INTERVAL hpg31.c1 DAY ORDER BY hpg31.c4 - INTERVAL hpg31.c1 DAY) AS col_2, FLOOR(FLOOR(ASIN(hpg31.c1))) AS col_3 FROM t3 AS hpg31 GROUP BY FLOOR(ASIN(hpg31.c1)), FLOOR(FLOOR(ASIN(hpg31.c1)))) UNION ALL SELECT nhp53.c5 AS col_1, nhp53.c6 AS col_2, nhp53.c1 AS col_3 FROM t1 AS nhp53;