Bug #86973 Submitted: BIN() function error 6 Jul 2017 13:26 7 Jul 2017 7:17 R van der Wal Verified None MySQL Server: Optimizer S1 (Critical) 5.6, 5.5.56, 5.6.36, 5.7.18 Any Any BINARY, conversion, value

[6 Jul 2017 13:26] R van der Wal
```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)```
[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] Umesh Shastry
```Hello!

Thank you for the report and test case.
Truncation happens even without sort etc

Thanks,
Umesh```
[7 Jul 2017 7:18] Umesh Shastry
`test resultsAttachment: 86973.results (application/octet-stream, text), 7.38 KiB.`