| Bug #109363 | 0 changed to -0 caused by DISTINCT and UNION ALL | ||
|---|---|---|---|
| Submitted: | 13 Dec 2022 13:08 | Modified: | 16 Dec 2022 14:50 |
| Reporter: | ZongYin Hao | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.4, 8.0.31 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | distinct, regression, UNION ALL, zero | ||
[13 Dec 2022 13:26]
MySQL Verification Team
Hi Mr. Hao, Thank you for your bug report. However, this is not a bug. Simply, you are mixing incompatible types and domains in the expressions, which can lead to the undetermined results. Primarily, you are mixing integer with floating point and with date values. The problem is exhibited with UNION, only because UNION has to reconcile the unreconcilable domains. Not a bug.
[13 Dec 2022 13:26]
MySQL Verification Team
Hello ZongYin Hao, Thank you for the report and test case. regards, Umesh
[14 Dec 2022 8:09]
Roy Lyseng
Posted by developer: This is a minor problem since the float values 0 and -0 are two different representations of the number zero.
[16 Dec 2022 12:56]
ZongYin Hao
Sorry, I won't submit such minor bugs in the future.
[16 Dec 2022 13:45]
huahua xu
Hi all:
The minor problem is caused by the method `Field_double::store`, who calls `Field_real::truncate`.
```
Field_real::Truncate_result Field_real::truncate(double *nr, double max_value) {
...
/* Check for infinity so we don't get NaN in calculations */
if (!std::isinf(*nr)) {
double tmp = rint((*nr - floor(*nr)) * log_10[dec]) / log_10[dec];
*nr = floor(*nr) + tmp;
}
...
}
```
[16 Dec 2022 13:49]
MySQL Verification Team
Thank you for your contribution. It is our standpoint that all bugs should be fixed, whether major or minor.
[16 Dec 2022 14:50]
ZongYin Hao
Wow, thank you for your great work, too!
Description: In theory, the result of sql2(DISTINCT) ⊆ the result of sql1: (SELECT ~1) UNION ALL (SELECT c1 DIV 1.0*(-LAST_DAY('2011-08-03')) FROM t); -- sql1 (SELECT ~1) UNION ALL (SELECT DISTINCT c1 DIV 1.0*(-LAST_DAY('2011-08-03')) FROM t); -- sql2 However, the value 0 changed to -0 after adding DISTINCT, seems like a logical bug: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.00 sec) mysql> (SELECT ~1) UNION ALL (SELECT c1 DIV 1.0*(-LAST_DAY('2011-08-03')) FROM t); +----------------------+ | ~1 | +----------------------+ | 18446744073709552000 | | -20110831 | | 0 | | -723989916 | +----------------------+ 4 rows in set (0.00 sec) mysql> (SELECT ~1) UNION ALL (SELECT DISTINCT c1 DIV 1.0*(-LAST_DAY('2011-08-03')) FROM t); +----------------------+ | ~1 | +----------------------+ | 18446744073709552000 | | -20110831 | | -0 | | -723989916 | +----------------------+ 4 rows in set (0.00 sec) How to repeat: drop table if exists t; CREATE TABLE t (c1 FLOAT UNSIGNED); INSERT INTO t VALUES (1.009),(0.0001),(36.0002); (SELECT ~1) UNION ALL (SELECT c1 DIV 1.0*(-LAST_DAY('2011-08-03')) FROM t); (SELECT ~1) UNION ALL (SELECT DISTINCT c1 DIV 1.0*(-LAST_DAY('2011-08-03')) FROM t); Suggested fix: The following list may be helpful for your debugging: 1. The bug cannot be reproduced after removing (SELECT ~1) UNION ALL; 2. We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mysql/tags We found that the bug first occurred in mysql:8.0.4, it cannot be reproduced in mysql:8.0.3: mysql> select version(); +--------------+ | version() | +--------------+ | 8.0.3-rc-log | +--------------+ 1 row in set (0.00 sec) mysql> (SELECT ~1) UNION ALL (SELECT c1 DIV 1.0*(-LAST_DAY('2011-08-03')) FROM t); +-----------------------+ | ~1 | +-----------------------+ | 1.8446744073709552e19 | | 0 | | -0 | | 0 | +-----------------------+ 4 rows in set (0.00 sec) mysql> (SELECT ~1) UNION ALL (SELECT DISTINCT c1 DIV 1.0*(-LAST_DAY('2011-08-03')) FROM t); +-----------------------+ | ~1 | +-----------------------+ | 1.8446744073709552e19 | | 0 | | -0 | | 0 | +-----------------------+ 4 rows in set (0.00 sec)