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 13:26] R van der Wal
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:

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

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

[7 Jul 2017 7:18] Umesh Shastry
test results

Attachment: 86973.results (application/octet-stream, text), 7.38 KiB.