| Bug #86973 | BIN() function error | ||
|---|---|---|---|
| Submitted: | 6 Jul 2017 13:26 | Modified: | 7 Jul 2017 7:17 |
| Reporter: | R van der Wal | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.6, 5.5.56, 5.6.36, 5.7.18 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | BINARY, conversion, value | ||
[6 Jul 2017 14:22]
Peter Laursen
OM 5.7 I get this SELECT BIN(244207),BIN(42949697296), "test"; BIN(244207) BIN(42949697296) test ------------------ ------------------------------------ -------- 111011100111101111 101000000000000000000101111100010000 test SELECT * FROM (SELECT BIN(244207),BIN(4294967296), "test" ) a; BIN(244207) BIN(4294967296) test ------------------ --------------------- -------- 111011100111101111 100000000000000000000 test SELECT * FROM (SELECT BIN(244207),BIN(4294967296), "test" ) a ORDER BY a.test; BIN(244207) BIN(4294967296) test ------------------ --------------------- -------- 111011100111101111 100000000000000000000 test .. so here the use of a subquery causes truncation of the larger value. I cannot see any effect of ORDER BY with this example -- Peter -- not a MySQL/Oracle person
[7 Jul 2017 7:17]
MySQL Verification Team
Hello! Thank you for the report and test case. Truncation happens even without sort etc Thanks, Umesh
[7 Jul 2017 7:18]
MySQL Verification Team
test results
Attachment: 86973.results (application/octet-stream, text), 7.38 KiB.
Description: The BIN() function creates a wrong value when a "SORT" is used in the query. The outcome of the BIN() function returns an incorrect value when sort is used How to repeat: select bin(244207),bin(4294967296), "test" gives the output: 111011100111101111;100000000000000000000000000000000;test which is correct for demonstration i use the following query: select * from ( select bin(244207),bin(4294967296), "test") a, ( select bin(244207),bin(4294967296), "test") b order by a.test this gives the output: "1110111001111011" "1000000000000000" "test" "1110111001111011" "1000000000000000" "test" As you can see both values are completely wrongly converted. Same happens when you use conv(244207,10,2)