Description:
after changing MINUTE(subq.c1) to MINUTE(subq.c1 + INTERVAL '60' MINUTE),the result become empty.
Additionally,when the type of MINUTE's argument is INT,it defaultly try to transform the argument.
For example,when I insert an INT value 2187.the result is 21
mysql> select MINUTE(t3.c1) from t3;
+---------------+
| MINUTE(t3.c1) |
+---------------+
| 21 |
| 59 |
| NULL |
+---------------+
3 rows in set, 1 warning (0.00 sec)
mysql> SELECT CAST(subq.c8 AS SIGNED) AS col_1, ST_GEOMETRYFROMTEXT('POINT(47.70515 -12.15125)') AS col_1_2 FROM (SELECT s480.c1 AS c1, s480.c8 AS c8, JSON_ARRAYAGG(s480.c10) AS json_arrayagg_540 FROM t3 AS s480 GROUP BY s480.c1, s480.c8 ORDER BY s480.c1 ASC) AS subq WHERE (MINUTE(subq.c1) <> 64);
+-------+------------------------------------------------------+
| col_1 | col_1_2 |
+-------+------------------------------------------------------+
| 24 | 0x0000000001010000002063EE5A42DA47403D0AD7A3704D28C0 |
| 100 | 0x0000000001010000002063EE5A42DA47403D0AD7A3704D28C0 |
+-------+------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> SELECT CAST(subq.c8 AS SIGNED) AS col_1, ST_GEOMETRYFROMTEXT('POINT(47.70515 -12.15125)') AS col_1_2 FROM (SELECT s480.c1 AS c1, s480.c8 AS c8, JSON_ARRAYAGG(s480.c10) AS json_arrayagg_540 FROM t3 AS s480 GROUP BY s480.c1, s480.c8 ORDER BY s480.c1 ASC) AS subq WHERE (MINUTE(subq.c1 + INTERVAL '60' MINUTE) <> 64);
Empty set, 3 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1292 | Incorrect datetime value: '2126' |
| Warning | 1292 | Incorrect datetime value: '5937' |
| Warning | 1292 | Incorrect datetime value: '9327' |
+---------+------+----------------------------------+
3 rows in set (0.00 sec)
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
SET GLOBAL sort_buffer_size = 64 * 1024 * 1024;
SET GLOBAL read_rnd_buffer_size = 8 * 1024 * 1024;
USE test;
CREATE TABLE t3 (
c1 INT NOT NULL AUTO_INCREMENT,
c2 INT NOT NULL,
c3 INT NOT NULL,
c4 YEAR NOT NULL,
c5 DATETIME 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 t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (2126, 7213, 186, 2011, '2025-10-12 20:18:22', 10, 16, 24, 11, 'sample_PVU0Y1jprz34r2WioawLEOjunuaAt4kEjCRAanbkE25PCkwlq4jaZbPHhAbuM6nAuOtu7ehgJOTz5tGBkR6naUNQEfgMByGduYC82Xdgb1hrLUhczgd18MgSZEBodCajWt1V8ODjO6ukO7zfERsJDDHRKAqFazfmBtOVVdlATnJg61pkhIzPzBtDx8fEAh25Hl7Nzy1OXWGBZoaliUu9QlpcxkZrHFBERHyi27TYqG0u27wpoQ2Ude1nE50knuulp5s1kVGFH590umlLcQ7MaEbk7Xd6uGjmzVWH0EhAJgqnFM6F1ugz09FH5M2B0lTlJ3JzPKF8hyBWZMLI6IZ0nazIEY5oszKVJtxhMs85WQJI0jworVTa6yR8RpzfD6cYqEVtJIXWSnm4meBO0KlBFz2NuU7bx5ufiOxAUVrvXCb17eWEj33aXBukH8Wo0jcquR8dTpYWJMhstxxvgKzM05zeM4ekhzusTTHVnYrHlKkT1kSvicdVeIMCKl3FUPbhvEsTsAbFTWzIw8Q6pg60KRYxcJHXuNnsCjtFcKxGISYfMgkRJnE9iK5ufac3R7T7n4jKslO0WsdmFqbStJGnf2LeLpYcOi7FcBx3LiCE3QByUeqz0ssc8ALo3Kr9EOZp3fJngE146ua3b2gh7ohct9E4HmlJ3M86T5cvK7fo3PitEerOLy3U2MGqlswmNaf0NmedQCzAfk3YZCyreRzDuSX4Vq5XNt5KBnRRgDbkKybzKA6o3vaR4mFGBDtn9f6003vQSUn5vfEPNGpWjm4uMLSvs5FC0kzlq1LdGUSDhZ7RQaXm5zwC6pfAAP8fzvkTZ4VGkvmTS7A7N9bfAXePEA7qbqRVKvzwoFdVyrlfNzeQoQnz4Dy5QeGDXPqRx30FGCjZE5kslxyrkHK7KF8saD7QdFAB8tJFJvh57zOympeIy7N2yhtY9venQVo7rKPl0tqfBKRbb0X1Uq2Ra7ZltneLTxkAhb7NOyZbtwsQsVVOAawVkhkzkYvPuw1rlNbivt5QiDf2moSQ9ebVNCGJKoZyiTOE5ZheHZoMezt8beeahULiApsSYZrobLH0AQHaNdGRYbalc73RXCBcqvARVI6E4FmrliVjZQ4jhxdhvKacjCV0efD6RGGyd320ylEyTaEoBwHnV79CgAQydhSu7P6BADR2tnHApyhcDFQiIan1Bf0KDFL6TOZ0f8IT5pX8Fd7qmVM2SQtEYbWYO94CQTLe8QXnF4cpUN3o2vl0Vh3rhb76V4ubWfdoWgIHEPw7s8YSYJOwL68Qoje1jwA7sqJ8sPcJXDyb5bDMsVdXESdkWyogQMGX9PQZUdAqJF59q5fPGLGeR5ltBSF9I6TVT05YdqYRVcKtkH550uX9B0qUQjFyzVDIH6ltv7xUOlsGy5wd9MHdxaQr7g4xPcbjiHtv4DOGuLKRcCXR45H3N2FyXPfqqnMyztDuuYiL6bbSsQX18toHU4x46Qf8LLgKuQrQ9W8ogsJetZsLCLXm1ysDuN53j2eGtFSg0v6gvyRwOLRlLVaRHDQIQwRUSnWFbFFrn4QfgTqdqFOzKNmPV5tvq2FVXnkVU9eeIcKvPB1TfTMlOUQ4PNQq6UzQsKANKBhPmZx19SW04wJALda2wTLrZrJXTOGG5xB54Gcq5Qrq1oVe3pOsoXtxSwKmRcC1SSLG1nwmqXUyXIihimL5hs2l82kBdL5SA9Cj9wfGrDfPq81zjsOO6YekpJz4FZWFwuNRDYUMXL9lwXIZg64gf1sMqmNsS1wQ00cXoOIN5bxP41ogNmEdKG9ByRuRZPUBOX8daExRpSOrcEZZyH0S5S9E3luuJjpO59yOK27VL', ST_GeomFromText('POINT(-6.356185 22.126352)'), 'sample_VTfoS7AOg57xffGnqfBe0kzcEfx77H7fqTKtM', X'E2BC933BCD88D9B0C2AB68D4A54100', 'z,x,y', NULL);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (9327, 9425, 6076, 2018, '2025-01-23 18:01:36', 39, 55, 73, 75, 'sample_18Aen9B9xFd1e83fFbmjfmSvruKGkMRUBycPLOMtsHFhul7Wx6xak4MqkLNgS5yJQhMSb8oqgp5Iw90JaqGj5hkmsUfYBh9mz2jclSdAFqVGolEmPBVeuI9aFf6jxmehJG1oHMzfpwNhk21kPAhMobFoCSPZTtynDzhONZ2zMYTNKRv9WsAhLZjiddSqsBvBX9aVXqB1RnZPZ3OdXwIBp2PWIWr1mFnzyfg9WrBx4GAK8L9KybT7TOIt4RsAE0i6Oazvv9dSVMUixJ9k1mbPjG5rxVxN16YSavq9S2km7135tQuXTGvkMR8JXovLzTb7wtCvRbXP8uhQTBoHg8QDr6BQYCnCwmdOdYHgOhWPB5O57HYAw133Nif5dilQAP9HEVpRE00uWNRn95UiDOllT4yBhLBo1bRGKeyKCLDM5nv51DC3D8X7pIId7K7Kt8Koh29sUijADY9EX9fBT2WXwvWOL2zZI9NLApr7BnJTXBw5ZqP8yDlVA', ST_GeomFromText('POINT(23.22481 152.837058)'), 'sample_Ldkt61BL2ACkdkXGKDgrYwcc4aCm04i40XgZQh', X'0DD8986D484640', 'x,z', NULL);
INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (5937, 8695, 1658, 2005, '2025-11-16 01:44:09', 84, 41, 100, 50, 'sample_4fBSJY6vp7H0MLMicXlFElAy8Sw1CjYJNcH7dA89y2ndY1dwsYDqsgJB2b1ol9MOVeWOzCeAwzDqxh9JrQcPVNjxybyvToIj06tD2WDsXqBCVnlDS1yYfpc5bLajIegL6u4ojjV7YFnVfRz1SqiypKQVZzxrqEMyoyxW0Zf8QFRt9xmLzu7s4r7iST9PyOlvj4eLj7fmXYnUZ6ngq8uVZldnnfiGP7OHhxavYVNOgNcVUhDfMJICk2ZXU9ah0anlrzjFkoFgA2muvMBG0h2KhtvQFnhJ7LPRIXVXSkhOXIuIUSLndcWyhKNLQvBm6y9etqyfZMcbhuaVYy8eVIiEWXyhW5yRS2JotVIAuqFaZlaqxzIwtEfAbdF18gYK27cuF7WmiPjxAllEmXzz5DSpWi11dgU1PsYqMnfykWPUcB1fFOHHcuh1YvIuXV99U7tadyJ4oxI4X9esG', ST_GeomFromText('POINT(29.908758 31.295671)'), 'sample_4fzOvkGV2w7hF4JV24w6tq1W5uKmDuvmFYUh1CTZqXV4Nvnk85f9D0bWB85uS2Pnl6Cinxl7LTcFWIcUmCLlst', X'D89FEDBF95E4A382C8B7D6927DE0A6AFE99D84D187471ECC8EC2B0EE92AEC399', 'x,y,z', NULL);
SELECT CAST(subq.c8 AS SIGNED) AS col_1, ST_GEOMETRYFROMTEXT('POINT(47.70515 -12.15125)') AS col_1_2 FROM (SELECT s480.c1 AS c1, s480.c8 AS c8, JSON_ARRAYAGG(s480.c10) AS json_arrayagg_540 FROM t3 AS s480 GROUP BY s480.c1, s480.c8 ORDER BY s480.c1 ASC) AS subq WHERE (MINUTE(subq.c1) <> 64);
SELECT CAST(subq.c8 AS SIGNED) AS col_1, ST_GEOMETRYFROMTEXT('POINT(47.70515 -12.15125)') AS col_1_2 FROM (SELECT s480.c1 AS c1, s480.c8 AS c8, JSON_ARRAYAGG(s480.c10) AS json_arrayagg_540 FROM t3 AS s480 GROUP BY s480.c1, s480.c8 ORDER BY s480.c1 ASC) AS subq WHERE (MINUTE(subq.c1 + INTERVAL '60' MINUTE) <> 64);
Description: after changing MINUTE(subq.c1) to MINUTE(subq.c1 + INTERVAL '60' MINUTE),the result become empty. Additionally,when the type of MINUTE's argument is INT,it defaultly try to transform the argument. For example,when I insert an INT value 2187.the result is 21 mysql> select MINUTE(t3.c1) from t3; +---------------+ | MINUTE(t3.c1) | +---------------+ | 21 | | 59 | | NULL | +---------------+ 3 rows in set, 1 warning (0.00 sec) mysql> SELECT CAST(subq.c8 AS SIGNED) AS col_1, ST_GEOMETRYFROMTEXT('POINT(47.70515 -12.15125)') AS col_1_2 FROM (SELECT s480.c1 AS c1, s480.c8 AS c8, JSON_ARRAYAGG(s480.c10) AS json_arrayagg_540 FROM t3 AS s480 GROUP BY s480.c1, s480.c8 ORDER BY s480.c1 ASC) AS subq WHERE (MINUTE(subq.c1) <> 64); +-------+------------------------------------------------------+ | col_1 | col_1_2 | +-------+------------------------------------------------------+ | 24 | 0x0000000001010000002063EE5A42DA47403D0AD7A3704D28C0 | | 100 | 0x0000000001010000002063EE5A42DA47403D0AD7A3704D28C0 | +-------+------------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> SELECT CAST(subq.c8 AS SIGNED) AS col_1, ST_GEOMETRYFROMTEXT('POINT(47.70515 -12.15125)') AS col_1_2 FROM (SELECT s480.c1 AS c1, s480.c8 AS c8, JSON_ARRAYAGG(s480.c10) AS json_arrayagg_540 FROM t3 AS s480 GROUP BY s480.c1, s480.c8 ORDER BY s480.c1 ASC) AS subq WHERE (MINUTE(subq.c1 + INTERVAL '60' MINUTE) <> 64); Empty set, 3 warnings (0.00 sec) mysql> show warnings; +---------+------+----------------------------------+ | Level | Code | Message | +---------+------+----------------------------------+ | Warning | 1292 | Incorrect datetime value: '2126' | | Warning | 1292 | Incorrect datetime value: '5937' | | Warning | 1292 | Incorrect datetime value: '9327' | +---------+------+----------------------------------+ 3 rows in set (0.00 sec) How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE IF NOT EXISTS test; SET GLOBAL sort_buffer_size = 64 * 1024 * 1024; SET GLOBAL read_rnd_buffer_size = 8 * 1024 * 1024; USE test; CREATE TABLE t3 ( c1 INT NOT NULL AUTO_INCREMENT, c2 INT NOT NULL, c3 INT NOT NULL, c4 YEAR NOT NULL, c5 DATETIME 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 t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (2126, 7213, 186, 2011, '2025-10-12 20:18:22', 10, 16, 24, 11, 'sample_PVU0Y1jprz34r2WioawLEOjunuaAt4kEjCRAanbkE25PCkwlq4jaZbPHhAbuM6nAuOtu7ehgJOTz5tGBkR6naUNQEfgMByGduYC82Xdgb1hrLUhczgd18MgSZEBodCajWt1V8ODjO6ukO7zfERsJDDHRKAqFazfmBtOVVdlATnJg61pkhIzPzBtDx8fEAh25Hl7Nzy1OXWGBZoaliUu9QlpcxkZrHFBERHyi27TYqG0u27wpoQ2Ude1nE50knuulp5s1kVGFH590umlLcQ7MaEbk7Xd6uGjmzVWH0EhAJgqnFM6F1ugz09FH5M2B0lTlJ3JzPKF8hyBWZMLI6IZ0nazIEY5oszKVJtxhMs85WQJI0jworVTa6yR8RpzfD6cYqEVtJIXWSnm4meBO0KlBFz2NuU7bx5ufiOxAUVrvXCb17eWEj33aXBukH8Wo0jcquR8dTpYWJMhstxxvgKzM05zeM4ekhzusTTHVnYrHlKkT1kSvicdVeIMCKl3FUPbhvEsTsAbFTWzIw8Q6pg60KRYxcJHXuNnsCjtFcKxGISYfMgkRJnE9iK5ufac3R7T7n4jKslO0WsdmFqbStJGnf2LeLpYcOi7FcBx3LiCE3QByUeqz0ssc8ALo3Kr9EOZp3fJngE146ua3b2gh7ohct9E4HmlJ3M86T5cvK7fo3PitEerOLy3U2MGqlswmNaf0NmedQCzAfk3YZCyreRzDuSX4Vq5XNt5KBnRRgDbkKybzKA6o3vaR4mFGBDtn9f6003vQSUn5vfEPNGpWjm4uMLSvs5FC0kzlq1LdGUSDhZ7RQaXm5zwC6pfAAP8fzvkTZ4VGkvmTS7A7N9bfAXePEA7qbqRVKvzwoFdVyrlfNzeQoQnz4Dy5QeGDXPqRx30FGCjZE5kslxyrkHK7KF8saD7QdFAB8tJFJvh57zOympeIy7N2yhtY9venQVo7rKPl0tqfBKRbb0X1Uq2Ra7ZltneLTxkAhb7NOyZbtwsQsVVOAawVkhkzkYvPuw1rlNbivt5QiDf2moSQ9ebVNCGJKoZyiTOE5ZheHZoMezt8beeahULiApsSYZrobLH0AQHaNdGRYbalc73RXCBcqvARVI6E4FmrliVjZQ4jhxdhvKacjCV0efD6RGGyd320ylEyTaEoBwHnV79CgAQydhSu7P6BADR2tnHApyhcDFQiIan1Bf0KDFL6TOZ0f8IT5pX8Fd7qmVM2SQtEYbWYO94CQTLe8QXnF4cpUN3o2vl0Vh3rhb76V4ubWfdoWgIHEPw7s8YSYJOwL68Qoje1jwA7sqJ8sPcJXDyb5bDMsVdXESdkWyogQMGX9PQZUdAqJF59q5fPGLGeR5ltBSF9I6TVT05YdqYRVcKtkH550uX9B0qUQjFyzVDIH6ltv7xUOlsGy5wd9MHdxaQr7g4xPcbjiHtv4DOGuLKRcCXR45H3N2FyXPfqqnMyztDuuYiL6bbSsQX18toHU4x46Qf8LLgKuQrQ9W8ogsJetZsLCLXm1ysDuN53j2eGtFSg0v6gvyRwOLRlLVaRHDQIQwRUSnWFbFFrn4QfgTqdqFOzKNmPV5tvq2FVXnkVU9eeIcKvPB1TfTMlOUQ4PNQq6UzQsKANKBhPmZx19SW04wJALda2wTLrZrJXTOGG5xB54Gcq5Qrq1oVe3pOsoXtxSwKmRcC1SSLG1nwmqXUyXIihimL5hs2l82kBdL5SA9Cj9wfGrDfPq81zjsOO6YekpJz4FZWFwuNRDYUMXL9lwXIZg64gf1sMqmNsS1wQ00cXoOIN5bxP41ogNmEdKG9ByRuRZPUBOX8daExRpSOrcEZZyH0S5S9E3luuJjpO59yOK27VL', ST_GeomFromText('POINT(-6.356185 22.126352)'), 'sample_VTfoS7AOg57xffGnqfBe0kzcEfx77H7fqTKtM', X'E2BC933BCD88D9B0C2AB68D4A54100', 'z,x,y', NULL); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (9327, 9425, 6076, 2018, '2025-01-23 18:01:36', 39, 55, 73, 75, 'sample_18Aen9B9xFd1e83fFbmjfmSvruKGkMRUBycPLOMtsHFhul7Wx6xak4MqkLNgS5yJQhMSb8oqgp5Iw90JaqGj5hkmsUfYBh9mz2jclSdAFqVGolEmPBVeuI9aFf6jxmehJG1oHMzfpwNhk21kPAhMobFoCSPZTtynDzhONZ2zMYTNKRv9WsAhLZjiddSqsBvBX9aVXqB1RnZPZ3OdXwIBp2PWIWr1mFnzyfg9WrBx4GAK8L9KybT7TOIt4RsAE0i6Oazvv9dSVMUixJ9k1mbPjG5rxVxN16YSavq9S2km7135tQuXTGvkMR8JXovLzTb7wtCvRbXP8uhQTBoHg8QDr6BQYCnCwmdOdYHgOhWPB5O57HYAw133Nif5dilQAP9HEVpRE00uWNRn95UiDOllT4yBhLBo1bRGKeyKCLDM5nv51DC3D8X7pIId7K7Kt8Koh29sUijADY9EX9fBT2WXwvWOL2zZI9NLApr7BnJTXBw5ZqP8yDlVA', ST_GeomFromText('POINT(23.22481 152.837058)'), 'sample_Ldkt61BL2ACkdkXGKDgrYwcc4aCm04i40XgZQh', X'0DD8986D484640', 'x,z', NULL); INSERT INTO t3 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15) VALUES (5937, 8695, 1658, 2005, '2025-11-16 01:44:09', 84, 41, 100, 50, 'sample_4fBSJY6vp7H0MLMicXlFElAy8Sw1CjYJNcH7dA89y2ndY1dwsYDqsgJB2b1ol9MOVeWOzCeAwzDqxh9JrQcPVNjxybyvToIj06tD2WDsXqBCVnlDS1yYfpc5bLajIegL6u4ojjV7YFnVfRz1SqiypKQVZzxrqEMyoyxW0Zf8QFRt9xmLzu7s4r7iST9PyOlvj4eLj7fmXYnUZ6ngq8uVZldnnfiGP7OHhxavYVNOgNcVUhDfMJICk2ZXU9ah0anlrzjFkoFgA2muvMBG0h2KhtvQFnhJ7LPRIXVXSkhOXIuIUSLndcWyhKNLQvBm6y9etqyfZMcbhuaVYy8eVIiEWXyhW5yRS2JotVIAuqFaZlaqxzIwtEfAbdF18gYK27cuF7WmiPjxAllEmXzz5DSpWi11dgU1PsYqMnfykWPUcB1fFOHHcuh1YvIuXV99U7tadyJ4oxI4X9esG', ST_GeomFromText('POINT(29.908758 31.295671)'), 'sample_4fzOvkGV2w7hF4JV24w6tq1W5uKmDuvmFYUh1CTZqXV4Nvnk85f9D0bWB85uS2Pnl6Cinxl7LTcFWIcUmCLlst', X'D89FEDBF95E4A382C8B7D6927DE0A6AFE99D84D187471ECC8EC2B0EE92AEC399', 'x,y,z', NULL); SELECT CAST(subq.c8 AS SIGNED) AS col_1, ST_GEOMETRYFROMTEXT('POINT(47.70515 -12.15125)') AS col_1_2 FROM (SELECT s480.c1 AS c1, s480.c8 AS c8, JSON_ARRAYAGG(s480.c10) AS json_arrayagg_540 FROM t3 AS s480 GROUP BY s480.c1, s480.c8 ORDER BY s480.c1 ASC) AS subq WHERE (MINUTE(subq.c1) <> 64); SELECT CAST(subq.c8 AS SIGNED) AS col_1, ST_GEOMETRYFROMTEXT('POINT(47.70515 -12.15125)') AS col_1_2 FROM (SELECT s480.c1 AS c1, s480.c8 AS c8, JSON_ARRAYAGG(s480.c10) AS json_arrayagg_540 FROM t3 AS s480 GROUP BY s480.c1, s480.c8 ORDER BY s480.c1 ASC) AS subq WHERE (MINUTE(subq.c1 + INTERVAL '60' MINUTE) <> 64);