Description:
after changing SUM(distinct x) to - SUM(distinct -x),the value unexpectedly change.Even though floating-precision loss exists,only change the sign bit shouldn’t affect the value
mysql> SELECT ST_GEOHASH(subq.sum_distinct_578, subq.sum_distinct_578, 72) AS col_1_2 FROM (SELECT SUM(DISTINCT s645.c14) AS sum_distinct_578, s645.c4 AS c4 FROM t2 AS s645 GROUP BY s645.c4) AS subq;
+------------------------+
| col_1_2 |
+------------------------+
| szwygsdzm6dtm6dtm6dtm7 |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT ST_GEOHASH(subq.sum_distinct_578, subq.sum_distinct_578, 72) AS col_1_2 FROM (SELECT -SUM(DISTINCT -s645.c14) AS sum_distinct_578, s645.c4 AS c4 FROM t2 AS s645 GROUP BY s645.c4) AS subq;
+--------------------------------------------------------------------------+
| col_1_2 |
+--------------------------------------------------------------------------+
| szwygsdzm63s0zmsdth1w1h0000000000000000000000000000000000000000000000000 |
+--------------------------------------------------------------------------+
1 row 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 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 (6076, 6081, 50.91, 'sample_psZ4h8h', '2025-09-22', 'sample_W', 'sample_wgkkJJqkEjSZJIQw0m7gxLXfoTMewJ6hEykoKR2MlhXbaoa8sZnfcxKJ2D4TObwOT4OyRKl0C7fNvNjX0IpyEjoIeWBWjRo5J9Bt1kAl524AsyXaasRT4uomHQVSX33fCif0W2cNZ2BhhoPKVa1qIInMVWjv45osL9gSxu7Ly9aujF1Rq1xZLZZKKfhdCD8To3mzmGoMoXgh43r43ghV8al7j0zMi3KjlkGtvjo4MYUcULRTY16M6r1TaBAK4meb2KI59LnjcxcW3Ba24AJS8KlukFoIWU21chfe1WfMFeiQIM7ETVk02bPkfuMevBsnsEeLlJF3Eafn6BLLiXx1fN4oJAIKwWXPywSBEB7wOkxDWoSAqVLmopZ8nUsn8sfOhKP4U8V8qq3srjxjwq4EqBwabWG0O2dAowBcP9i8Scbe6d7Q3jPDrzaBs0v1nrDAga6HmR4nXAoeoxCNmirmmrdnrpRl9w5a2jCPD1VbunD6BYzNkqKXeRabX7Su7NcEJxddpdVmRVcaoBUyhyeuo0NXwNB5LdWnNfDJ4fJrVZeLjzgMGqL0yTtZ1S5av58a0tw3bjo1HIRb5q9OD1B64giiX1zxPpMOyzoq7UIrb7sJXxr2E13eYhAigruiDU5AfBoxaAMXkCtkjDbBkwcGciP0gROtMcF0yhPWyg9d69Gp4WUjpYdCeSWjE47xXEWviJtjJ9syajQqPnBPOF3i9UqBosRLcOZDYdonzvLbPGr4WDxV2Ecbo9kulrKrPmTCidihHz85TjOfHQcVKkNnZ3g7H7Mtc8SOm9oZQZgsyNImi29TITMh3SAap5AEYdQ52OOGZSAbQpUYlLcmOaLXG5xtvsUNB5hFpQZ9CNIsb5ClVXa4LiytU1mFqKFPBsUfbSML9a5t8nlKxnIxlxzXV85Qu9iYKEvrnCFEik8kr5K5tLWLOnDyTUo5Iyyo21a9hyebZbIRDZJePNFaWnE5FQPOir5eCJtsMn6FaJ4l9wHb1VyB8bV6jIM4CsmvOfGXwUVQZ5V5bFQgXJAjWR0nOoetGFEPJHUMUoSsaMLeXJ5IRt5yS87uJqMYzNzL0WgBvVNyu6rjGF8jhsWC9WJ4kxYuiE2YdtjX7UGBijkpqzB5dyL0Tay90Con1BDt49wfcHdBiRUeSaFY2DcUTiETSaM0kTvUkW0xK5pJrtHFU2inodRY2mURD0EYlIw81D0vUWF0rCoPsE0LXEanw5dphqEv7laDdjAIfzpDLQrdtqiWPianD25gZMsY4EqDJXYvKA1yNIR6V3kuc6KMkV1ATG6t1lvjoF6j8SHOSxCOwAHHYFw4vpSIU1CZamzBQBxV4Y0ytKkhTUltEESZuyoEE0nyIAlP4rEicm9QJqN3rnwknIbO4phO5yphMTBR9973I8MY1BzS1srxWuD4opaYMOPNfsLnJ8e8uttEug5Q6YiqzpWr0if71Hoo8vVnRU8htWXCXNNePm4StjG5CPiFWOR056ZFArPUkWQelxXo5DOZiQz5dtUeQAVpyNfx', X'DB8141C395DBB50FCFAD26EC959AD98CCB9DC0A3D8AE4820', X'C58BE78BAEE4808B16CD8334E89195DD94EF9288D784E6AB8AD388', 'value2', 'b,a,c,d', b'10101001', '2025-09-19 23:35:00', 43.40, 47.13, '{"k1": 682, "k2": "sample_19", "k3": 925.8191499213518, "k4": true}');
SELECT ST_GEOHASH(subq.sum_distinct_578, subq.sum_distinct_578, 72) AS col_1_2 FROM (SELECT SUM(DISTINCT s645.c14) AS sum_distinct_578, s645.c4 AS c4 FROM t2 AS s645 GROUP BY s645.c4) AS subq;
SELECT ST_GEOHASH(subq.sum_distinct_578, subq.sum_distinct_578, 72) AS col_1_2 FROM (SELECT -SUM(DISTINCT -s645.c14) AS sum_distinct_578, s645.c4 AS c4 FROM t2 AS s645 GROUP BY s645.c4) AS subq;
Description: after changing SUM(distinct x) to - SUM(distinct -x),the value unexpectedly change.Even though floating-precision loss exists,only change the sign bit shouldn’t affect the value mysql> SELECT ST_GEOHASH(subq.sum_distinct_578, subq.sum_distinct_578, 72) AS col_1_2 FROM (SELECT SUM(DISTINCT s645.c14) AS sum_distinct_578, s645.c4 AS c4 FROM t2 AS s645 GROUP BY s645.c4) AS subq; +------------------------+ | col_1_2 | +------------------------+ | szwygsdzm6dtm6dtm6dtm7 | +------------------------+ 1 row in set (0.00 sec) mysql> SELECT ST_GEOHASH(subq.sum_distinct_578, subq.sum_distinct_578, 72) AS col_1_2 FROM (SELECT -SUM(DISTINCT -s645.c14) AS sum_distinct_578, s645.c4 AS c4 FROM t2 AS s645 GROUP BY s645.c4) AS subq; +--------------------------------------------------------------------------+ | col_1_2 | +--------------------------------------------------------------------------+ | szwygsdzm63s0zmsdth1w1h0000000000000000000000000000000000000000000000000 | +--------------------------------------------------------------------------+ 1 row 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 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 (6076, 6081, 50.91, 'sample_psZ4h8h', '2025-09-22', 'sample_W', 'sample_wgkkJJqkEjSZJIQw0m7gxLXfoTMewJ6hEykoKR2MlhXbaoa8sZnfcxKJ2D4TObwOT4OyRKl0C7fNvNjX0IpyEjoIeWBWjRo5J9Bt1kAl524AsyXaasRT4uomHQVSX33fCif0W2cNZ2BhhoPKVa1qIInMVWjv45osL9gSxu7Ly9aujF1Rq1xZLZZKKfhdCD8To3mzmGoMoXgh43r43ghV8al7j0zMi3KjlkGtvjo4MYUcULRTY16M6r1TaBAK4meb2KI59LnjcxcW3Ba24AJS8KlukFoIWU21chfe1WfMFeiQIM7ETVk02bPkfuMevBsnsEeLlJF3Eafn6BLLiXx1fN4oJAIKwWXPywSBEB7wOkxDWoSAqVLmopZ8nUsn8sfOhKP4U8V8qq3srjxjwq4EqBwabWG0O2dAowBcP9i8Scbe6d7Q3jPDrzaBs0v1nrDAga6HmR4nXAoeoxCNmirmmrdnrpRl9w5a2jCPD1VbunD6BYzNkqKXeRabX7Su7NcEJxddpdVmRVcaoBUyhyeuo0NXwNB5LdWnNfDJ4fJrVZeLjzgMGqL0yTtZ1S5av58a0tw3bjo1HIRb5q9OD1B64giiX1zxPpMOyzoq7UIrb7sJXxr2E13eYhAigruiDU5AfBoxaAMXkCtkjDbBkwcGciP0gROtMcF0yhPWyg9d69Gp4WUjpYdCeSWjE47xXEWviJtjJ9syajQqPnBPOF3i9UqBosRLcOZDYdonzvLbPGr4WDxV2Ecbo9kulrKrPmTCidihHz85TjOfHQcVKkNnZ3g7H7Mtc8SOm9oZQZgsyNImi29TITMh3SAap5AEYdQ52OOGZSAbQpUYlLcmOaLXG5xtvsUNB5hFpQZ9CNIsb5ClVXa4LiytU1mFqKFPBsUfbSML9a5t8nlKxnIxlxzXV85Qu9iYKEvrnCFEik8kr5K5tLWLOnDyTUo5Iyyo21a9hyebZbIRDZJePNFaWnE5FQPOir5eCJtsMn6FaJ4l9wHb1VyB8bV6jIM4CsmvOfGXwUVQZ5V5bFQgXJAjWR0nOoetGFEPJHUMUoSsaMLeXJ5IRt5yS87uJqMYzNzL0WgBvVNyu6rjGF8jhsWC9WJ4kxYuiE2YdtjX7UGBijkpqzB5dyL0Tay90Con1BDt49wfcHdBiRUeSaFY2DcUTiETSaM0kTvUkW0xK5pJrtHFU2inodRY2mURD0EYlIw81D0vUWF0rCoPsE0LXEanw5dphqEv7laDdjAIfzpDLQrdtqiWPianD25gZMsY4EqDJXYvKA1yNIR6V3kuc6KMkV1ATG6t1lvjoF6j8SHOSxCOwAHHYFw4vpSIU1CZamzBQBxV4Y0ytKkhTUltEESZuyoEE0nyIAlP4rEicm9QJqN3rnwknIbO4phO5yphMTBR9973I8MY1BzS1srxWuD4opaYMOPNfsLnJ8e8uttEug5Q6YiqzpWr0if71Hoo8vVnRU8htWXCXNNePm4StjG5CPiFWOR056ZFArPUkWQelxXo5DOZiQz5dtUeQAVpyNfx', X'DB8141C395DBB50FCFAD26EC959AD98CCB9DC0A3D8AE4820', X'C58BE78BAEE4808B16CD8334E89195DD94EF9288D784E6AB8AD388', 'value2', 'b,a,c,d', b'10101001', '2025-09-19 23:35:00', 43.40, 47.13, '{"k1": 682, "k2": "sample_19", "k3": 925.8191499213518, "k4": true}'); SELECT ST_GEOHASH(subq.sum_distinct_578, subq.sum_distinct_578, 72) AS col_1_2 FROM (SELECT SUM(DISTINCT s645.c14) AS sum_distinct_578, s645.c4 AS c4 FROM t2 AS s645 GROUP BY s645.c4) AS subq; SELECT ST_GEOHASH(subq.sum_distinct_578, subq.sum_distinct_578, 72) AS col_1_2 FROM (SELECT -SUM(DISTINCT -s645.c14) AS sum_distinct_578, s645.c4 AS c4 FROM t2 AS s645 GROUP BY s645.c4) AS subq;