Description:
after changing asin function in group clause,col_3's value unexpectedly change
mysql> SELECT DISTINCT COUNT(DISTINCT jnk24.c14) AS col_2, MIN(jnk24.c12) AS col_3, ASIN(jnk24.c3) AS col_4 FROM t2 AS jnk24 WHERE NOT jnk24.c13 IS NULL GROUP BY ASIN(SIN(ASIN(jnk24.c3))), ASIN(jnk24.c3) UNION SELECT qdz10.c1 AS col_2, qdz10.c9 AS col_3, qdz10.c3 AS col_4 FROM t2 AS qdz10 WHERE (SHA(qdz10.c11) = 'sample_20');
+-------+--------------+-------+
| col_2 | col_3 | col_4 |
+-------+--------------+-------+
| 1 | 0xFA | NULL |
+-------+--------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT DISTINCT COUNT(DISTINCT jnk24.c14) AS col_2, MIN(jnk24.c12) AS col_3, ASIN(jnk24.c3) AS col_4 FROM t2 AS jnk24 WHERE NOT jnk24.c13 IS NULL GROUP BY ASIN(jnk24.c3) UNION SELECT qdz10.c1 AS col_2, qdz10.c9 AS col_3, qdz10.c3 AS
col_4 FROM t2 AS qdz10 WHERE (SHA(qdz10.c11) = 'sample_20');
+-------+--------------+-------+
| col_2 | col_3 | col_4 |
+-------+--------------+-------+
| 1 | 0x323530 | NULL |
+-------+--------------+-------+
1 row in set, 1 warning (0.01 sec)
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,
c16 JSON NULL,
PRIMARY KEY (c1)
);
INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (8704, 2839, 146.86, 'sample_0n6nNhYhRlc7Lc4vgMqBwXUG8aZIr31iRCJm9s', '2025-10-23', 'sample_JvKtXVz44cZQD2Kxur8DKBk59l2bwR93QwtioGuxhGSAiy2D7Z3kWNSrRT6eP6bU4aPRQuKWSlQcebu3pqLy3DxMLtdyDy4mUVyC2GF894OccU1NlAQBQIRtzjufKhF4uuLLtFuPSeiN1ApaB1BcHgXiBndWMb2CDJdJp9HQ2gqNoLxQNYMyRTvu6OPixNi0fAEktI9Dpm37YCoSf8N1U3U66XSFn604CQWuUHj9UN1mbdHsZIlsMTOTSbiJi8GniNYDwOcqiI6c25OAQGQkBjEXXMJyNlIweZ8HoJw0VP0nrLBBd3iCpPHGyVcFQFF1iJTIbIwrdcrAvVrm2ao7L6VSZ5wVgjjTyeVtGfzQCaCqMKwOlEbg2OtirwxxsVBslolQS4W9e0yj7G1Mjn2GKiULE42chXA8UHdaKSXJshyzqx40rLRRsMjxditaLFDxcNxrv3ou8ZWk1CdJ0vhCWQLJjRlK7DyE5oQTHXfP36sNcktShqTcyMBus6N0Kae35a9YlkKifRy0uJLjOfo9ItXv2ZhFpGEkSKrUDynzQ3C1n1aMLqATdSevrT4vo7HrYQciBEVMN9fYjjepuARo4TStCNy17Uj9s8jQH6Fa1igBogmE5snqwq5A75I8NSOkoTn2LxcdxQ2qdG9cJJj7szX68MtIFaIUqWqhCX1KHD0BMqmwPjkk2mvybXeaKpGOxZ0bdojyYJn2Qs5YYN7RgWfbUA7P652vbjBCfMrx1PExSGDQZ0wwEZxdvAeJxCuWNNY8ubPPXPdbHvA1cAURRl0bgZAy0Lnw4YnIz7NfQ07H40H9YFHotAxc5CpimCHCastvlMhoZUImkTYSfuueYrqk9FOF4BfG', 'sample_9SIHfozd8PNto9En1JkJHOaIdYxGcNVDx7dxABNxERrS3ZsUC6PTSxA2Z7ppRJ77XCLtGHtEdiMZ4dZNjslAbMAcz3LzCIUHJEw8Gf', X'1A72C28EE79B9D3AC7ABDF8134E9B5B528C783DFA91C5C7E7DE6A282EEB5B8E4A183EBABAEE0B9A313E1AD84', X'E285A36157C9952D', 'value1', 'b,d,c', b'11111010', '2025-10-16 23:45:33', 49.37, 59.04, '{"k1": 410, "k2": "sample_1", "k3": 571.4004337388058, "k4": true}');
SELECT DISTINCT COUNT(DISTINCT jnk24.c14) AS col_2, MIN(jnk24.c12) AS col_3, ASIN(jnk24.c3) AS col_4 FROM t2 AS jnk24 WHERE NOT jnk24.c13 IS NULL GROUP BY ASIN(jnk24.c3) UNION SELECT qdz10.c1 AS col_2, qdz10.c9 AS col_3, qdz10.c3 AS col_4 FROM t2 AS qdz10 WHERE (SHA(qdz10.c11) = 'sample_20');
SELECT DISTINCT COUNT(DISTINCT jnk24.c14) AS col_2, MIN(jnk24.c12) AS col_3, ASIN(jnk24.c3) AS col_4 FROM t2 AS jnk24 WHERE NOT jnk24.c13 IS NULL GROUP BY ASIN(SIN(ASIN(jnk24.c3))), ASIN(jnk24.c3) UNION SELECT qdz10.c1 AS col_2, qdz10.c9 AS col_3, qdz10.c3 AS col_4 FROM t2 AS qdz10 WHERE (SHA(qdz10.c11) = 'sample_20');
Description: after changing asin function in group clause,col_3's value unexpectedly change mysql> SELECT DISTINCT COUNT(DISTINCT jnk24.c14) AS col_2, MIN(jnk24.c12) AS col_3, ASIN(jnk24.c3) AS col_4 FROM t2 AS jnk24 WHERE NOT jnk24.c13 IS NULL GROUP BY ASIN(SIN(ASIN(jnk24.c3))), ASIN(jnk24.c3) UNION SELECT qdz10.c1 AS col_2, qdz10.c9 AS col_3, qdz10.c3 AS col_4 FROM t2 AS qdz10 WHERE (SHA(qdz10.c11) = 'sample_20'); +-------+--------------+-------+ | col_2 | col_3 | col_4 | +-------+--------------+-------+ | 1 | 0xFA | NULL | +-------+--------------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT DISTINCT COUNT(DISTINCT jnk24.c14) AS col_2, MIN(jnk24.c12) AS col_3, ASIN(jnk24.c3) AS col_4 FROM t2 AS jnk24 WHERE NOT jnk24.c13 IS NULL GROUP BY ASIN(jnk24.c3) UNION SELECT qdz10.c1 AS col_2, qdz10.c9 AS col_3, qdz10.c3 AS col_4 FROM t2 AS qdz10 WHERE (SHA(qdz10.c11) = 'sample_20'); +-------+--------------+-------+ | col_2 | col_3 | col_4 | +-------+--------------+-------+ | 1 | 0x323530 | NULL | +-------+--------------+-------+ 1 row in set, 1 warning (0.01 sec) 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, c16 JSON NULL, PRIMARY KEY (c1) ); INSERT INTO t2 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16) VALUES (8704, 2839, 146.86, 'sample_0n6nNhYhRlc7Lc4vgMqBwXUG8aZIr31iRCJm9s', '2025-10-23', 'sample_JvKtXVz44cZQD2Kxur8DKBk59l2bwR93QwtioGuxhGSAiy2D7Z3kWNSrRT6eP6bU4aPRQuKWSlQcebu3pqLy3DxMLtdyDy4mUVyC2GF894OccU1NlAQBQIRtzjufKhF4uuLLtFuPSeiN1ApaB1BcHgXiBndWMb2CDJdJp9HQ2gqNoLxQNYMyRTvu6OPixNi0fAEktI9Dpm37YCoSf8N1U3U66XSFn604CQWuUHj9UN1mbdHsZIlsMTOTSbiJi8GniNYDwOcqiI6c25OAQGQkBjEXXMJyNlIweZ8HoJw0VP0nrLBBd3iCpPHGyVcFQFF1iJTIbIwrdcrAvVrm2ao7L6VSZ5wVgjjTyeVtGfzQCaCqMKwOlEbg2OtirwxxsVBslolQS4W9e0yj7G1Mjn2GKiULE42chXA8UHdaKSXJshyzqx40rLRRsMjxditaLFDxcNxrv3ou8ZWk1CdJ0vhCWQLJjRlK7DyE5oQTHXfP36sNcktShqTcyMBus6N0Kae35a9YlkKifRy0uJLjOfo9ItXv2ZhFpGEkSKrUDynzQ3C1n1aMLqATdSevrT4vo7HrYQciBEVMN9fYjjepuARo4TStCNy17Uj9s8jQH6Fa1igBogmE5snqwq5A75I8NSOkoTn2LxcdxQ2qdG9cJJj7szX68MtIFaIUqWqhCX1KHD0BMqmwPjkk2mvybXeaKpGOxZ0bdojyYJn2Qs5YYN7RgWfbUA7P652vbjBCfMrx1PExSGDQZ0wwEZxdvAeJxCuWNNY8ubPPXPdbHvA1cAURRl0bgZAy0Lnw4YnIz7NfQ07H40H9YFHotAxc5CpimCHCastvlMhoZUImkTYSfuueYrqk9FOF4BfG', 'sample_9SIHfozd8PNto9En1JkJHOaIdYxGcNVDx7dxABNxERrS3ZsUC6PTSxA2Z7ppRJ77XCLtGHtEdiMZ4dZNjslAbMAcz3LzCIUHJEw8Gf', X'1A72C28EE79B9D3AC7ABDF8134E9B5B528C783DFA91C5C7E7DE6A282EEB5B8E4A183EBABAEE0B9A313E1AD84', X'E285A36157C9952D', 'value1', 'b,d,c', b'11111010', '2025-10-16 23:45:33', 49.37, 59.04, '{"k1": 410, "k2": "sample_1", "k3": 571.4004337388058, "k4": true}'); SELECT DISTINCT COUNT(DISTINCT jnk24.c14) AS col_2, MIN(jnk24.c12) AS col_3, ASIN(jnk24.c3) AS col_4 FROM t2 AS jnk24 WHERE NOT jnk24.c13 IS NULL GROUP BY ASIN(jnk24.c3) UNION SELECT qdz10.c1 AS col_2, qdz10.c9 AS col_3, qdz10.c3 AS col_4 FROM t2 AS qdz10 WHERE (SHA(qdz10.c11) = 'sample_20'); SELECT DISTINCT COUNT(DISTINCT jnk24.c14) AS col_2, MIN(jnk24.c12) AS col_3, ASIN(jnk24.c3) AS col_4 FROM t2 AS jnk24 WHERE NOT jnk24.c13 IS NULL GROUP BY ASIN(SIN(ASIN(jnk24.c3))), ASIN(jnk24.c3) UNION SELECT qdz10.c1 AS col_2, qdz10.c9 AS col_3, qdz10.c3 AS col_4 FROM t2 AS qdz10 WHERE (SHA(qdz10.c11) = 'sample_20');