| Bug #41814 | SELECT DISTINCT returns wrong results with fixed width division | ||
|---|---|---|---|
| Submitted: | 1 Jan 2009 1:18 | Modified: | 1 Jan 2009 11:51 |
| Reporter: | Justin Swanhart | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.1.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Jan 2009 1:19]
Justin Swanhart
mysqldump of the test.testdata table
Attachment: testdata.sql (application/octet-stream, text), 74.22 KiB.
[1 Jan 2009 11:51]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Additional Info:
I was able to repeat the behavior reported with version 5.1.29 but not with a server built from source, so I assume it was fixed somewhat:
mysql> select DISTINCT td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03
-> from test.testdata td1,
-> test.testdata td2
-> where td1.c1 = -99 and td2.c2 = 0.03;
+------+------+---------------+------------+
| c1 | c2 | td1.c1/td2.c2 | -99 / 0.03 |
+------+------+---------------+------------+
| -99 | 0.03 | -999.9999 | -3300.0000 |
+------+------+---------------+------------+
1 row in set (0.05 sec)
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| protocol_version | 10 |
| version | 5.1.29-rc-community |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | ia32 |
| version_compile_os | Win32 |
+-------------------------+------------------------------+
5 rows in set (0.05 sec)
mysql>
mysql 5.1 >select DISTINCT td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03
-> from testdata td1,
-> testdata td2
-> where td1.c1 = -99 and td2.c2 = 0.03;
+------+------+---------------+------------+
| c1 | c2 | td1.c1/td2.c2 | -99 / 0.03 |
+------+------+---------------+------------+
| -99 | 0.03 | -3300.0000 | -3300.0000 |
+------+------+---------------+------------+
1 row in set (0.02 sec)
mysql 5.1 >show variables like "%version%";
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.31-nt-log |
| version_comment | Source distribution |
| version_compile_machine | ia32 |
| version_compile_os | Win32 |
+-------------------------+---------------------+
5 rows in set (0.03 sec)
Description: -- SELECT without distinct returns the following CORRECT results select td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03 from testdata td1, testdata td2 where td1.c1 = -99 and td2.c2 = 0.03 limit 1; +------+------+---------------+------------+ | c1 | c2 | td1.c1/td2.c2 | -99 / 0.03 | +------+------+---------------+------------+ | -99 | 0.03 | -3300.0000 | -3300.0000 | +------+------+---------------+------------+ 1 row in set (0.00 sec) -- Adding DISTINCT to the query results in the wrong result for the -- division: -999.9999 instead of -3300.0000 select DISTINCT td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03 from testdata td1, testdata td2 where td1.c1 = -99 and td2.c2 = 0.03; +------+------+---------------+------------+ | c1 | c2 | td1.c1/td2.c2 | -99 / 0.03 | +------+------+---------------+------------+ | -99 | 0.03 | -999.9999 | -3300.0000 | +------+------+---------------+------------+ 1 row in set (0.00 sec) How to repeat: Import the attached table then execute the following query: select DISTINCT td1.c1, td2.c2, td1.c1/td2.c2, -99 / 0.03 from test.testdata td1, test.testdata td2 where td1.c1 = -99 and td2.c2 = 0.03;